Separating tables and code doesn't really do the trick

[Guest]

Hi,

I have modified quite heavily an Access application, and, because it's going
to be used by 2 different people in the same company, and also because I
wanted to be able to make software changes independantly from table content,
I duplicated the original .mdb file, then left only tables in one (the "DB"
mdb), and all the code in the other one (the "code" mdb).

However, the code is not really "portable" at all, because I have to link,
in the "code" mdb, the tables of the "DB" mdb, and Access has no concept of
relative links, but stores the whole path to the "DB" mdb, which is different
for me in my test environment than for the final users. So everytime I do
mods (in my env) and bring them to the final users, I have to delete all
links in the "code" mdb, and link again to the users directory structure,
which is a pain somewhere, even more so when you consider that the 2 mdb
files will actually be in the same directory on a server at the users !

Is there anyway I can have relative links in Access ? Or am I damned to
recreate in my dev env the same dir structure as at the users ? Any other
alternatives to split tables and code cleanly, short of moving to SQLServer
and VB ?

Thanks for help
Balex

 

[Allen Browne]

Access does not support relative links, but there are a couple of ways to
handle this.

One approach is to use a batch file so you can SUBST the drive letter where
your users actually have the database. For example, if your users have the
data in:
R:\
and you have them in:
C:\Data
the batch file would be:
Subst R: C:\Data
and you can execute that from inside Access with:
Shell CurrentProject.Path & "\AssignRDrive.bat"

Another approach is to set up your initializion code to OpenRecordset on
some table to verify the data is present where expected. If not, you pop up
the FileOpen dialog and ask the user to locate it. When they do, you set the
Connect property of each TableDef (remembering to RefreshLink), and it
remembers that location for the future. This not only copes with them
getting updates from you, but it is good initialization practice, and it
works correctly for the day they change their server. There is an example of
this approach in this sample database:
http://msdn.microsoft.com/library/officedev/bapp2000/mdbdownload.htm

 

[aaron.kempf]

mdb is a piece of shit

upgrade to sql server and dont listen to the mdb wimps at this channel

ps - dont do vb; do access data projects
they are 10,000 times more powerful than silly old mdb files; and
they're reliable also.