Change path of linked table

[Abby N]

I've got an Access 2k database with a linked table connected to an Excel spreadsheet. Unfortunately the name of the Excel file changes daily (the name includes the date). Is there a programmatic way to update the path of a linked table? I'd like to avoid forcing my users to run the Linked Table Manager every day.

I’ve tried (without success):
CurrentDb.TableDefs("ImportData").Properties("connect").Value = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & strFileName

and

CurrentDb.TableDefs("ImportData").Connect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & strFileName

Any help would be appreciated.

 

[namliam]

have you "Cheated" of an existing link or did you think this up yourself?

Regards

By the By, CurrentDb.TableDefs("ImportData").Connect will do the same as your properties().value

 

[Abby N]

Yes, I "cheated." The string to which I'm attempting to set the connect property was derived by reading the existing property. The variable strFileName represents the new path. So, for example, the connect property originally read something like:

"Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:\10-01-03 Report.xls"

And I'd like to change it to:

"Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:\10-02-03 Report.xls"

 

[Pat Hartman]

The problem is probably that the file name is not surrounded with quotes.

I know that this type of file name has been "supported" for some years but they really are a pain to work with in code because of the embedded spaces and special characters.

 

[Abby N]

Eureka! It seems the TableDef object must be set to a variable before the connect property becomes writable. Here’s the code that worked for me:

Set db = CurrentDb
Set tdf = db.TableDefs("ImportData")
With tdf
.Connect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & strFileName
.RefreshLink
End With

 

[namliam]

Hmz strange....

You learn something everyday...

Regards

 

[Sergeant]

I could not get Abby N's "eureka" method to work. Do I need to set any references?
Anyone got any smarts on programatically changing path to linked tables?

I don't want to re-establish broken links. I have a db that is used by two different squadrons (each with their own BE on their own shared drive), and as I make changes to the FE and re-distribute, I want the links to be set to the squadrons's path to their BE's.

Sarge.
(I realize the post is a year old, but a search didn't reveal much)

 

[namliam]

to loop all tables something like:

For each T in currentdb.tabledefs
T.connect =
T.refreshlink
next T

Is that what you were looking for?

 

[Sergeant]

Yeah, I tried that, and it didn't work. (or it didn't seem to work) I realized later that even if I refreshed the tables collection in code, I still needed to go to a different tab in the db window and back to tables to see the changes. So, it may have worked after all.

In the mean time, I made a table of table names and a table of paths and a form and some buttons to test this out. I ended up deleting all the linked tables and adding them back by looping through the table of table names and referring to the paths table for the correct path, depending upon who the current user is in the user table. It works like a champ.
Now I need to do one of two things:
1. A test that indicates that the links are faulty, where I can run the code. (I could just delete the tables before distribution, because it would be easy to test if a table exists.)
or
2. I will choose which squadron the db is going to, and set the links (absolute paths) before distributing.

 

[namliam]

Here is an idea i have done in the past.

I relinked the tables everytime the database openes, to a relative path. REMEMBER tho that access stores the absolute path!!!!
I had simular paths on different sites (with small diffs offcourse) and used this functions to get the current db path. Then added Tables\DB_BE.mdb and there they are.

Function myCurrDir()
    myCurrDir = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
End Function
Function myTables()
    myTables = myCurrDir & "Tables\This_BE.MDB"
End Function

Hope it helps.

Greetz

 

[Sergeant]

Somehow, I thought it would be a bad idea to relink on open if the links are already good.
So, I decided to use a form that opens before the main form. It checks the table links by calling a function for each table name in my [TBLS] table. If all of the linked tables are there, the form closes and opens the main form. Otherwise, the re-linking form stays open and allows you to select which environment you are operating in and then it re-links the tables accordingly.

Sarge.