f



Linked table (excel spread sheet) on users desktop

I have a database that uses a daily import from an excel spread sheet to update a specific table. The spread sheet is emailed to the user and they copy the spread sheet to their desktop, then click the import button in the database.

This has worked fine for a long time but now there are multiple users and I have to remap the linked spread sheet on each users copy of the database front end. 

My question is, is there a way to link create this link with VBA when the front end database is opened instead of having to recreate the link on each user's machine?

Thank you! 
0
Del
12/13/2016 2:55:48 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

3 Replies
496 Views

Similar Articles

[PageSpeed] 36

On Tuesday, December 13, 2016 at 9:55:55 AM UTC-5, Del LaBo wrote:
> I have a database that uses a daily import from an excel spread sheet to =
update a specific table. The spread sheet is emailed to the user and they c=
opy the spread sheet to their desktop, then click the import button in the =
database.
>=20
> This has worked fine for a long time but now there are multiple users and=
 I have to remap the linked spread sheet on each users copy of the database=
 front end.=20
>=20
> My question is, is there a way to link create this link with VBA when the=
 front end database is opened instead of having to recreate the link on eac=
h user's machine?
>=20
> Thank you!

Most likely I have the issue you have wrong.  Anyway, do your users save to=
 the same folder name on their computer?  Is the file name they save the sa=
me?  If so, in your database (I assume it is 2007/2010/20113) select Extern=
al Data then Excel and when the Get External Data window pops up, select Li=
nk Table and browse to the correct Excel filename.  Then provide your users=
 with your app copy for update.
0
Patrick
12/13/2016 5:19:58 PM
On Tuesday, December 13, 2016 at 12:20:04 PM UTC-5, Patrick Finucane wrote:
> On Tuesday, December 13, 2016 at 9:55:55 AM UTC-5, Del LaBo wrote:
> > I have a database that uses a daily import from an excel spread sheet t=
o update a specific table. The spread sheet is emailed to the user and they=
 copy the spread sheet to their desktop, then click the import button in th=
e database.
> >=20
> > This has worked fine for a long time but now there are multiple users a=
nd I have to remap the linked spread sheet on each users copy of the databa=
se front end.=20
> >=20
> > My question is, is there a way to link create this link with VBA when t=
he front end database is opened instead of having to recreate the link on e=
ach user's machine?
> >=20
> > Thank you!
>=20
> Most likely I have the issue you have wrong.  Anyway, do your users save =
to the same folder name on their computer?  Is the file name they save the =
same?  If so, in your database (I assume it is 2007/2010/20113) select Exte=
rnal Data then Excel and when the Get External Data window pops up, select =
Link Table and browse to the correct Excel filename.  Then provide your use=
rs with your app copy for update.

Yes the file name is always the same, the issue is the users would be savin=
g the excel spread sheet to their desktop so the path is the file would be =
different for each user name. I am trying to void having to setup a link fo=
r the file each time a new user is added. I believe I have found the answer=
 by using DoCmd.TransferSpreadsheet acLink.=20
Thank you for your input.
0
Del
12/13/2016 5:43:59 PM
On Tuesday, December 13, 2016 at 12:44:05 PM UTC-5, Del LaBo wrote:
> On Tuesday, December 13, 2016 at 12:20:04 PM UTC-5, Patrick Finucane wrot=
e:
> > On Tuesday, December 13, 2016 at 9:55:55 AM UTC-5, Del LaBo wrote:
> > > I have a database that uses a daily import from an excel spread sheet=
 to update a specific table. The spread sheet is emailed to the user and th=
ey copy the spread sheet to their desktop, then click the import button in =
the database.
> > >=20
> > > This has worked fine for a long time but now there are multiple users=
 and I have to remap the linked spread sheet on each users copy of the data=
base front end.=20
> > >=20
> > > My question is, is there a way to link create this link with VBA when=
 the front end database is opened instead of having to recreate the link on=
 each user's machine?
> > >=20
> > > Thank you!
> >=20
> > Most likely I have the issue you have wrong.  Anyway, do your users sav=
e to the same folder name on their computer?  Is the file name they save th=
e same?  If so, in your database (I assume it is 2007/2010/20113) select Ex=
ternal Data then Excel and when the Get External Data window pops up, selec=
t Link Table and browse to the correct Excel filename.  Then provide your u=
sers with your app copy for update.
>=20
> Yes the file name is always the same, the issue is the users would be sav=
ing the excel spread sheet to their desktop so the path is the file would b=
e different for each user name. I am trying to void having to setup a link =
for the file each time a new user is added. I believe I have found the answ=
er by using DoCmd.TransferSpreadsheet acLink.=20
> Thank you for your input.

Like I said, I don't understand the issue.  If I had an Excel file linked t=
o Access, and I copied an Excel file to the existing file, the link hasn't =
changed if it has the same file name.  If each Excel file has a different n=
ame for each day, then you'd have your problem.

Here is a sample program to get a connection string.  Put in a code module =
and enter something like GetConnection "Customer" to get the table link.

Public Sub GetConnection(strTable As String)
    Dim tdf As TableDef
    Dim d As Database
   =20
    Set d =3D CurrentDb
    Set tdf =3D d.TableDefs(strTable)
   =20
    Debug.Print tdf.Connect
End Sub

Me, I'd probably save my Excel file to C:\ExcelFile for simplicity.  To eac=
h his own.

You can update the connection string if you'd like with a slight modificati=
on.
0
Patrick
12/13/2016 10:18:19 PM
Reply: