Linked Tables

[bbulla]

I have a frontend and a backend to my database. Actually, I have two backends....one with real data and one with sample data.

Currently when I am switching between databases, I am using the Linked Table Manager, which I am finding to be a bit of a pain. If I am already linked to one of the two backends, in order to switch to the other I have to rename the one currently in use so that it will prompt me for the new source.

I think MS Access looks for the current link before asking you for a new one. If the current link is still active it won't ask you for the new one. Renaming the current link effectively makes the link 'inactive' and allows me to link to the other database.

Does anyone know a better way to do this?? Can I change the link in code?? That way I could just make myself a form that I could use to switch between the two different backends.

Any other ways to resolve this would also be appreciated.

Thanks

Brian

 

[bvan]

I wouldn't recommend switching back and forth between 'real' and 'sample' data. It could lead to confusion, and corruption of your 'real' data. Why don't you make a copy of the front end, call it 'Sample.mdb', and link it to your 'sample' back end?

 

[bbulla]

Well, I figure for development purposes it makes more sense to just use one front end and flip it between the two different back ends. That way I can make any new queries, forms, reports while attached to the 'Sample' backend and not have to worry about corrupting data on the backend with the real data in it.

Once I make all my changes to the front end, I just change the backend back to the the one with real data, test it, and then copy the front end onto the network so everyone can use it.

Anyways, what I did was made a form with a button on it to change to the sample database. In debug mode it seems to be working, but when it gets to the 5th table it crashes with an 'Invalid Command' error. Here is my code:

<the click event of a button>

Dim tdf As TableDef
Dim strNewDB As String

strNewDB = "C:\Documents and Settings\bbulla\My Documents\SPP\PTTW\Databases\Summer 05\SAMPLE DATA.mdb"

For Each tdf In CurrentDb.TableDefs
tdf.Connect = ";DATABASE=" & strNewDB 'this is where it crashes
tdf.RefreshLink
Next tdf


In theory, I think this will work, I'm just not sure why it is crapping out on me. All the tables in both the main and the sample DB are the same name, structure, etc..

Brian

 

[bbulla]

The exact error is:

Runtime error 3219. Invalid Operation.

 

[bbulla]

OK, I figured out my code error. Just needed to add a line to check to see if the table is actually linked already or not. Here is the final version:

Dim dbs As Database
Dim tdf As TableDef
Dim strNewDB As String

Set dbs = CurrentDb
strNewDB = "<path to database>"
For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & strNewDB
tdf.RefreshLink
End If
Next tdf


So I just made a for that incorporates that code with a few options to let me pick what database I want to use; my sample or the real thing.

Thanks for your help!