Split Database Tables (1 Viewer)

[estelleh]

I split my database (Name1), and everything was working fine. I then saved Name1_be.accdb to Name2_be.accdb and Name1.accdb to Name2.accdb.

Now my queries are not picking up any data in Name2.accdb. I have relinked the tables in Name2.accdb, but it's like the accdb is not seeing the tables in the _be.accdb, even though all the names are the same as they were before the save as?

Have I royally messed this up or is there something simple I am missing?

 

[arnelgp]

did you relink it to name2_be?

 

[The_Doc_Man]

As arnelgp suggests, you have to go through the relink operation for every table if you have renamed the BE file. Using the External Data manager, there is a way for you to relink all of your tables at once. Just check all the tables and let it ask you where the tables are kept. That should be all you need. It should work immediately.

However, ... do you have ANY CODE in the FE file that tries to dynamically connect to the BE file with an explicit Open Database operation or with some code to modify the tables' .Connect string? Using the External Data manager allows you to statically relink the tables but would have no effect on dynamic relinking.

 

[estelleh]

did you relink it to name2_be?

I did :-(

 

[estelleh]

As arnelgp suggests, you have to go through the relink operation for every table if you have renamed the BE file. Using the External Data manager, there is a way for you to relink all of your tables at once. Just check all the tables and let it ask you where the tables are kept. That should be all you need. It should work immediately.

However, ... do you have ANY CODE in the FE file that tries to dynamically connect to the BE file with an explicit Open Database operation or with some code to modify the tables' .Connect string? Using the External Data manager allows you to statically relink the tables but would have no effect on dynamic relinking.

I relinked the files by right clicking one of the tables, selecting "Linked Table Manager", selecting all the tables and forcing it to ask for the location of the be. It says all tables were successfully relinked, but it seems none of my queries are working - although I'm not getting an error, the reports are just blank.

Also, the one DLookup statement I looked at while running - showed it matching one value to atotally different value and saying the two are the same....

No dynamic code that I can think of....

 

[The_Doc_Man]

Also, the one DLookup statement I looked at while running - showed it matching one value to atotally different value and saying the two are the same....

Go into your VBA code screen. From the menu, follow Tools >> References and scroll through the checked items to verify that none of them show up as "Missing." It is unlikely, but since it is a "cheap" diagnostic test, I don't feel bad in asking you to do this.

 

[estelleh]

Go into your VBA code screen. From the menu, follow Tools >> References and scroll through the checked items to verify that none of them show up as "Missing." It is unlikely, but since it is a "cheap" diagnostic test, I don't feel bad in asking you to do this.

Only 4 checked items, none of them marked missing.

 

[Gasman]

Well if you hover over a linked table in the FE, it will show you which DB you are linked to?

 

[estelleh]

Well if you hover over a linked table in the FE, it will show you which DB you are linked to?

It shows the tables as correctly linked.... It's as if the queries don't recognise the tables now... I tried creating a new query which looks exactly like the old one - and that works.....

I've restored the system to the point where it was before I split the database. Last time, I split it, worked on it and then imported the client's data into the renamed database.

This time I'm going to try appending the client's data to my test data, deleting the test data and then splitting it.

 

[The_Doc_Man]

It's as if the queries don't recognise the tables now

Re-open each query that doesn't work, in design mode. Change the underlying tables. (I.e refresh the query's record source.) Save. Try them again.

 

[Pat Hartman]

rename the original be. Make sure there is no BE with the original name. Then test again. Do you get an error?

Can you post the SQL of one of the queries that doesn't work. If you have hard-coded the path to the db and table in the query, the query would need to be modified. This is not a recommended technique since the path won't automatically update if you relink the tables.

There is no condition where you would have to import client data. Once you split the db, simply relink to point to the clientdata be.