Multiple Backends

[The_Doc_Man]

Don't know what Dave did, but for my biggest project I had a BE version table that I used to look up the version numbers acceptable to the FE file. I had an opening form that was a dispatcher and in its Form_Open routine I opened a recordset to read the BE version table. It was enough to have it as a read-only connection. Since the dispatcher never closed until you actually exited the DB, I just left the recordset open. That was how I created a persistent BE connection.

 

[NauticalGent]

Don't know what Dave did, but for my biggest project I had a BE version table that I used to look up the version numbers acceptable to the FE file. I had an opening form that was a dispatcher and in its Form_Open routine I opened a recordset to read the BE version table. It was enough to have it as a read-only connection. Since the dispatcher never closed until you actually exited the DB, I just left the recordset open. That was how I created a persistent BE connection.

That is my normal process too, but I'm always interested in alternative ways to do the same thing...

 

[gemma-the-husky]

How do you go about that Dave?

I only reconnect tables if the backend has changed - but if it has, then I think the existing permanent connection will not be useful. What I actually do is this.

I have a table called "LinkedTables" which includes a list of the tables I expect to connect in each front end. At startup I check that all of these tables are connected correctly. If I have added new backend tables, or want to connect to a different backend, this test fails.

So then I first drop all the connected tables, and start over. I just found it easier to recreate all the tables, as a belt and braces approach. Sometimes I am changing from Jet tables to SQL server tables, and vice versa. I don't know if a refreshlink would work in that case, but the deletetable/createtable always works, because the code just builds new tabledefs.

So having deleted all the tables, I then iterate my table of linkedtables, and relink all the tables.
If I don't find them all, at the end I report the error and stop.

After the first table is connected, I have a little sub that creates a recordset to the first table (I mean it only needs to create the recordset once)
if tempRST is nothing then set tempRST = currentdb.openrecordset(tablename)

Then I re-create the links to all the other tables.
Now all the tables are connected, I can do whatever I really want to do to maintain the permanent connection (open a query, open a form, open a different recordset, etc), and then close the tempRST object I just opened.

does that make sense?

 

[NauticalGent]

does that make sense?

It does. I had a similar re-linker setup for the SQL Server BE I maintained while I was abroad...without the recordset bit. I had a hidden startup form that was bound to a user table for that purpose.

Appreciate your response!

 

[Pat Hartman]

I have an app that is sold to the public which can switch from ACE to SQL Server. I keep a table to enable the swap. The code deletes all the tables from the table list table and then links them also from the table list table.