Linked Table Manager: can this be automated?

[intern42]

I have a split database with the back end (tables) and front end (forms/reports/queries) residing in the same folder.

The problem is, every time I move the folder I have to go into Linked Table Manager and re-link the back end to the front end.

Is there a way to automate this so that the front end always looks in the same folder as itself to find the back end?

This isn't a big deal for me, but the end-user might get confused by this.

 

[Cosmos75]

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=41086

See Oldsoftboss's attachment.

 

[llkhoutx]

You question assumes that you know the file and folder containing the MDB which you want to link and that you know the table names. If you have tables in multiple MDBs which must be relinked the solution is a little more complicated.

You can use the common dialog to allow the user to navigate to the requisite mdb.

Cycle through your table names executing the following for each:

dim tbldef as dao.tabeldef

Set tbldef = db.TableDefs(stblName)
tbldef.Connect = ";DATABASE=" & sYourMDBFileandFolder
tbldef.RefreshLink

 

[intern42]

That would work, but I don't want the user to EVER have to navigate to anything.

I am going to give the user a folder containing 2 .mde files:
the front end and the back end.

I'll probably make the back end a hidden file so that he doesn't ever click on it accidentally.

However, if he decides to move the folder, he'll no longer be able to access the tables.

I know the name of the file(s) and the folder they'll be in, but have no way of knowing the full file path if he moves the folder.

I know in other programming languages it is possible to reference a file in the same folder as the source code without knowing the full file path so that moving the entire folder won't affect anything.

I was hoping there was a way of doing this in Access without the user being aware.

Maybe this is not the case.

Thanks for all the help.

 

[Cosmos75]

I know in other programming languages it is possible to reference a file in the same folder as the source code without knowing the full file path so that moving the entire folder won't affect anything.

If you want the path and the name of the current database, then use:
CurrentDb.Name

If you want just the name of the current database, then use:
Dir(CurrentDb.Name)

Or if you want the path to the current database, then use:
Left(CurrentDb.Name,Len(CurrentDb.Name)-Len(Dir(CurrentDb.Name)))

You need to add

Dim db as Database

for the three lines above to work.

Additionally you can use
CurrentProject.Path (Access 2000)
Application.Path (Access 97)

But if you call this from the Front end it will return the file path of the front end! So you need to keep the beack in in the same folder OR a subfolder

Front End = C:\DBFile
Back End = C:\DBFIle\BackEnd

and refer to the BackEnd as Application.Path &" BackEnd\" (or &" \BackEnd\" - can't remember if it'll return the filepath and a \ at the end)

 

[llkhoutx]

I've used the some Access code to find a file without knowing the path. I'll try to locate it. However, with this method, you have to guarantee that the file name is unique.