Solved Wich is the fast mehod to relink table to db server?

[amorosik]

I have a procedure created with Access that uses data from a Sql Server db
The Access procedure is the same and can be used with different databases via odbc interface, suppose company1, company2, company N, which are physically different databases on the Sql Server, but with the exact same structure and each one contains the data of the homonymous company
When the operator has to use the program with the data taken from company2 he must perform a relink to the tables as taken from Sql Server db company2
The relink operation, being several hundreds of tables, is quite slow, in the order of 20-30 seconds
The code used to relink tables is similar to the one indicated in this post
The question is: is there a different method for linking tables to different db, in order to switch from Company1 to company2 more quickly?

 

[The_Doc_Man]

I have a question in the abstract. Are the multiple companies related? If the DBs have identical structure such that the FE can just be redirected, then is there any reason to not combine the DBs into one bigger DB where the company name, symbol, or ID because a qualifying key field in any queries? Then no relinking at all is involved, just change the queries to include the correct company number. THAT switch would take milliseconds over a network or microseconds if done purely locally in the FE file.

If those companies CANNOT share the DB for legal reasons, then obviously this doesn't work. But let me just say that having multiple identical databases that can use the same FE sounds very seriously non-normalized.

 

[amorosik]

The individual databases are from companies that have nothing in common
The hypothesis of keeping a single db containing the data of all the companies, and then selecting from time to time only those relating to the current company, has already been evaluated, but the hypothesis was rejected for various reasons

 

[Josef P.]

There are 2 variants for linking:
1. RefreshLink
2. delete and create TableDef

Just an idea, I have never needed this myself and have not tried it out: Would it work to link the tables via an ODBC data source (DSN) and change the database in the DSN?

 

[The_Doc_Man]

The individual databases are from companies that have nothing in common
The hypothesis of keeping a single db containing the data of all the companies, and then selecting from time to time only those relating to the current company, has already been evaluated, but the hypothesis was rejected for various reasons

OK, it was worth asking anyway.

The problem becomes dependent on the speed of your network because when you drop a link, that takes very little - but to establish a new link dynamically over a network becomes slower. You have to relink the tables one at a time and Access will have to validate the structure of each table. Each table individually won't be terribly slow, but it all adds up when you have a number of tables. I don't know how to speed that up other than to speed up the physical network, and that is probably not going to happen.

 

[isladogs]

I have always used DSN-less connections which, in my opinion, makes it easier to swop between different instances of SQL Server. However, I doubt whether it is any faster. If that approach is new to you and you want to investigate it, try to attend the Access Europe user group online session this week. See

Next Access Europe Meeting is on Wed 3 May 2023

The next Access Europe meeting will be on Wednesday 3 May 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central Europe and 10AM in Seattle / PST Please note that the UK is now on Summer Time (UTC+1). For local...

www.access-programmers.co.uk

I suppose you could always just relink the tables as required for a particular task, but I think that could get very confusing.
If you need to switch regularly, it may be better to rethink your decision and have several versions of the database each linked to a specific company

 

[Josef P.]

Idea from #4 tested ... it works.

1. create file DSN with default db
2. link table with connection string: "ODBC;FILEDSN=Y:\our\OdbcDsnToSqlServ.dsn"=>

=> Changing the database in the DSN also changes the contents of the linked tables without re-linking. (The tables must of course be closed beforehand.)

 

[amorosik]

Idea from #4 tested ... it works.

1. create file DSN with default db
2. link table with connection string: "ODBC;FILEDSN=Y:\our\OdbcDsnToSqlServ.dsn"=>

=> Changing the database in the DSN also changes the contents of the linked tables without re-linking. (The tables must of course be closed beforehand.)

Are you sure?
Because on the properties of linket table, there is also stored a db name like
"ODBC;DSN=COMP1; ...... ;DATABASE=COMPANY1;TABLE=dbo.Customer;..."
Where COMPANY1 is one of db name on Sql Server
If you change properties on Dsn Comp1, without 'read' information from Dsn to Access table, change nothing

 

[Josef P.]

Are you sure?

I tested it.

Because on the properties of linket table, there is also stored a db name like
"ODBC;DSN=COMP1; ...... ;DATABASE=COMPANY1;TABLE=dbo.Customer;..."

So I wrote down the connection string to use.

 

[amorosik]

Sorry but I do not understand
- you have created a system odbc dsn named db1, which points to the data contained in db Company1
- you connected the tables of an Access procedure to the dsn db1
- you have verified that the data in a table are those actually contained in the corresponding table in the Company1 db
- then you changed dsn db1 pointing to db Company2
- and opening the Access procedure (without relink table), are the table data now those contained in Company2 ?

I ask because I too have performed the same operations but I can't see the data on Company2

 

[Josef P.]

1. I created a file dsn "OdbcDSN.dsn"

[ODBC]
DRIVER=SQL Server Native Client 11.0
DATABASE=TestDb1
Trusted_Connection=Yes
SERVER=SQLServer\Instance

2. Linked a Table via VBA with connection string: ODBC;FILEDSN=C:\Test\OdbcDSN.dsn

3. Changed Database in OdbcDSN.dsn

[ODBC]
DRIVER=SQL Server Native Client 11.0
DATABASE=TestDb2
Trusted_Connection=Yes
SERVER=SQLServer\Instance

 

[gemma-the-husky]

One solution would be to gave multiple front ends, each connected to the appropriate back end.

Otherwise, an occasional relink process, even if takes 20 to 30 seconds is not so much of a problem, surely.

 

[amorosik]

1. I created a file dsn "OdbcDSN.dsn"

[ODBC]
DRIVER=SQL Server Native Client 11.0
DATABASE=TestDb1
Trusted_Connection=Yes
SERVER=SQLServer\Instance

2. Linked a Table via VBA with connection string: ODBC;FILEDSN=C:\Test\OdbcDSN.dsn

3. Changed Database in OdbcDSN.dsn

[ODBC]
DRIVER=SQL Server Native Client 11.0
DATABASE=TestDb2
Trusted_Connection=Yes
SERVER=SQLServer\Instance

I tried as you suggest, but the connection information with the first dsn file remains stored in the linked table in the Access procedure
And this is confirmed by the fact that, after having performed point 2, even if the OdbcDsn.dsn file is physically deleted from the computer, the Access procedure starts correctly and the information from the linked tables is correctly accessible
Maybe I did something wrong during the tests?

 

[amorosik]

One solution would be to gave multiple front ends, each connected to the appropriate back end.

Otherwise, an occasional relink process, even if takes 20 to 30 seconds is not so much of a problem, surely.

Yes I understand, and this is definitely a possibility
But my intention was to understand if it is possible to use a single Access procedure alternately with different db's, moving from one to another quickly

 

[Josef P.]

What is the reason for the failure to implement the variant of #11?

 

[amorosik]

I don't know why following the indications of post #11 doesn't work
I think it's because Access stores inside the information read from the file dsn
And therefore it is not sufficient to change the dsn file containing the information to change the visibility of the data inside the linked tables on the Access program
The procedure I followed is this:
- opened Access and created a new test.accdb file
- created file dsn1.dsn containing the connection information to db1
- created dsn2.dsn file containing db2 connection information
- from within Access executed table connection via odbc indicating dsn1 as file
- selected only one table, Customers, among those available
- the linked table has appeared inside the development environment and can be consulted correctly
- closed development environment
- I replaced the information contained in dsn2 inside ds, then dsn1.dsn contain information to connect to db2
- reopened the test.accdb file and clicked on the linked Customers table

At this point the data that appeared are those of db1
Same thing if I delete the dsn1.dsn files from the disk, the data in the linked Customers table are perfectly visible and modifiable
This test means that it is necessary to relink the tables
It is not enough to change the information contained in the dsn1.dsn file

 

[Josef P.]

Do you use the built-in function in Access to link the tables?
Then you have to set the Connect property once again using VBA.

example:

Const DsnFilePath As String = "y:\our\path\to\OdbcDSN.dsn"
Const DsnConnectString As String = "ODBC;FILEDSN=" & DsnFilePath

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb

For Each tdf In db.TableDefs
   If tdf.Connect Like "ODBC;*" Then
      tdf.Connect = DsnConnectString
      tdf.RefreshLink
   End If
Next

 

[amorosik]

Do you use the built-in function in Access to link the tables?
Then you have to set the Connect property once again using VBA.

This is equivalent to reconnecting the db tables
If Access has to redo the connection procedure, every time it starts, (and you have 500 tables below for example) I would say that the start is not instantaneous

 

[Josef P.]

You only have to do this once so that the connection string only contains the DSN info.

 

[amorosik]

No
Once every time db backend is switched from dsn1 to dsn2
Is a classic table re-link