create dynamic file path for linking to text files in Access 2003

[Daniel]

I have an MDB that I support for a group of users that need to
periodically link about 15 dbf files into. These files vary month to
month. And their location varies each time. The user has to copy an
empty version of my MDB into a new folder along with that month's DBFs
and then manually link to all of the DBFs and rename the links.

Pain in the butt, obviously, so I would like to automate this somehow.
I know how to write the code to link to external files, but is there a
way to create a connection string so that the path to the files changes
dynamically based on where the mdb is located when the code is run?

In other words, how do you create a variable path to the files without
hard coding the name of the parent folder? Like the way windows refers
to the system folder as %SYSTEMROOT% or whatever.

Any help is appreciated.

Thanks!
Daniel

 

[Douglas J. Steele]

You can't.

The best you can do is put in code that determines where the tables should
be, and relink them in code.

http://www.mvps.org/access/tables/tbl0009.htm and
http://www.mvps.org/access/tables/tbl0010.htm at "The Access Web" show how
to do this for tables in MDB files and using DSN respectively. See whether
that's enough to give you an idea of how to do it using dbf files.

 

[Klatuu]

Download the code from this site. It is an API that presents the Windows
Common Open/Save file dialog and allows you to select a file name with the
path.

http://www.mvps.org/access/api/api0001.htm