Link to Dbase File

[Guest]

I have created a link to a Dbase file in an Access 2000 database.
Periodically, the Dbase file data will change, but the structure will remain
the same. The path and/or filename could change, however. I would like to
be able to adjust the link to point to the new Dbase file when necessary.

I have used VB to update links to tables in other Access databases. Using
that as a basis, I created the following statements:

CurrentDb.TableDefs("DBLink").Connect = _
"dBase IV;HDR=NO;IMEX=2;DATABASE=D:\work\"

CurrentDb.TableDefs("DBLink").SourceTableName = "test.dbf"

CurrentDb.TableDefs("DBLink").RefreshLink

The Connect statement appears to execute, but the SourceTableName statement
generates an errorabout not being able to set the property once the object is
part of a collection. Because of that, I am not certain that the Connect
statment is actually working, even though it executes.

Is it possible to change this DBase link? I would prefer not to have to
remove the link and recreate it each time the DBase file changes.

Thanks.

 

[John Nurick]

Hi Martin,

See http://www.mvps.org/access/tables/tbl0012.htm

 

[Guest]

John
I added the code you suggested. I tried stepping thru the code and it
simply sets "fRefreshlinks" to True and exits. I am not a VB expert, but it
does not seem to do what I would like to.

There is already a link to the DBase file. I would like to be able to
change the path and/or filename the link points to without having to delete
the link and recreate it when the DBase file changes.

Thanks.

 

[John Nurick]

Martin,

I haven't used the "Relink tables from different datasources" code
myself yet, but as I understand it fRefreshLinks does this:

- for each linked table in the database, test the link using
Set rstTry = dbs.OpenRecordset(rst![Name].Value)

- if the link works, move to the next linked table
- if not, ask the user to select the correct file

- if after that all is well, return True, otherwise false.

If you want to change the "target" of a linked table where the link does
work, you'll need to modify the code. Alternatively, if you search
http://groups.google.com for

access dao "relink tables" group:microsoft.public.access.*

you should find something useful.