Linked table

[fireball]

I have 2 files one is a excel document and the other is an access document, i have a linked table between the two.

the problem i have is that when i move the files to a different location i have to update the link to the file manualy, is there a way for access to automaticly update the link??
can it automaticly look in the folder where the database is for the file as this is where it will allways be located?

 

[DJkarl]

short answer yes you can, you would need to create an Autoexec macro and have it run some VB code to update the link for the table(s). The Currentproject.Path command will give you the directory where the database resides, if you're using an older version of Access that doesn't have this method you may need Currentdb.name and then you'd have to strip out the database file name. You'll need to update the Connect property of the linked table(s) then refresh the link.

 

[fireball]

ok cool, dont supose some one could give me the vba code for it as i wouldnt know where to even start.

 

[WayneRyan]

FireBall,

RuralGuy posted this link a while ago ...

http://allenbrowne.com/ser-13.html

Wayne

 

[fireball]

ok, cheers for that info will check it out 2moro

 

[fireball]

ok well this doesnt work, i want to automaticly like a access and excel file, and the link i ws given dont allow u to do that.

does any one els know how i can do this???

the code would be helpfull seeing as i dont know how to do it.

thanks

 

[boomtap]

I also need the solution to this problem. It's hard to believe that Access can't just look at the same folder as itself for linked tables. If there isn't a setting for that - there REALLY should be. Anybody have any help?

 

[Dairy Farmer]

'Delete existing linked table
DoCmd.DeleteObject acTable, "Table1b"
'Link Table1a from BE and call it Table1b in FE
DoCmd.TransferDatabase acLink, "Microsoft Access", CurrentProject.Path & "\Data\be.accdb", acTable, "Table1a", "Table1b", False

First line of code deletes the linked table in the FE
CurrentProject.Path = The folder that the FE resides in
\Data\be.accdb = if you have your BE in a sub folder
Table1a = name of table in source
Table1b = name of table in destination if different name to source