Solved Wich is the fast mehod to relink table to db server? (1 Viewer)

[Josef P.]

see: OdbcDsn.mp4

Maybe it only works with the version I use.

 

[amorosik]

I believe the difference is not due to the different Access version as much as the db server difference
I'm using Access+Firebird while probably Access+SqlServer 'understand' each other better
(Access 2013 32bit + Firebird 3.0 + odbc driver 2.0.3.154)

 

[amorosik]

No it's not a matter of db server but probably of Access version or a particular configuration settings
I also tried with a Sql Server and two different db's, and with the same method you showed, in my case the db doesn't change
So there are two possibilities, either it's a different feature of the Microsoft 365 development environment, or it's a development environment setting that evidently tests the dsn on file every time the program starts running
In my case, an Access 2013 32bit + Sql Server 2014 + Native Client 11.0 this system doesn't work

 

[gemma-the-husky]

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

Well, I don't understand why you have hundreds of tables. Maybe your database design could reduce the number of tables. My biggest database has about 100 linked tables. Relinking those, on rare occasions takes about 10 seconds or so from memory. That's not too long a delay. I imagine if a user was regularly changing the target database, and there were more tables, then a 20 or 30 second delay each time might be irritating for users, hence the suggestion you have permanently linked front ends.

I think it takes slightly longer to link to SQL server databases. Linking to jet/ace back ends seems to be rather faster, maybe a couple of seconds for 50 plus tables.

 

[Josef P.]

I'm using Access+Firebird while probably Access+SqlServer 'understand' each other better

Tested with firebird 4 and ODBC 2.05.156 => After changing the database in the DSN file, a restart of the Access application was necessary to see data from the other db. Relinking was not necessary.

 

[amorosik]

Tested with firebird 4 and ODBC 2.05.156 => After changing the database in the DSN file, a restart of the Access application was necessary to see data from the other db. Relinking was not necessary.

Then is Access version
Or some settings to activate

 

[Josef P.]

What does your connection string look like in the linked table?

debug.print currentdb.tablesDefs("YourLinkedTableName").Connect

This is mine: ODBC;FILEDSN=C:\Daten\fb\db.dsn

 

[amorosik]

What does your connection string look like in the linked table?

debug.print currentdb.tablesDefs("YourLinkedTableName").Connect

This is mine: ODBC;FILEDSN=C:\Daten\fb\db.dsn

ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=RYZ_ROB;UID=user1;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=OSCULATI;

 

[amorosik]

Ok now it works for me too
The sequence to do is:
- connect the db tables the first time (so that Access has the possibility to change the Connect property of each table)
- start the cycle that scrolls through the tables present and those that have the Connect property starting with ODBC: modify it using the ODBC;FILEDSN=C:\file_dsn.dsn (your routine)

At this point, changing the contents of file_dsn.dsn, the data 'seen' by Access will also correspond to those of the modified db
Obviously the two (or plus) different db's must have the identical structure, because Access remembers the structure read during the first connection to the tables

 

[amorosik]

Well, I don't understand why you have hundreds of tables. Maybe your database design could reduce the number of tables. My biggest database has about 100 linked tables. Relinking those, on rare occasions takes about 10 seconds or so from memory. That's not too long a delay. I imagine if a user was regularly changing the target database, and there were more tables, then a 20 or 30 second delay each time might be irritating for users, hence the suggestion you have permanently linked front ends.

I think it takes slightly longer to link to SQL server databases. Linking to jet/ace back ends seems to be rather faster, maybe a couple of seconds for 50 plus tables.

"..My biggest database has about 100 linked tables.."
Well, so what?
Do you think db with thousands of tables can't exist?

 

[gemma-the-husky]

"..My biggest database has about 100 linked tables.."
Well, so what?
Do you think db with thousands of tables can't exist?

I have to say 1000 tables sounds a lot. I can't really conceive of an application that would need so many tables. It's not a function of the amount of data. It's a function of designing an appropriate data structure, so that any amount of data can be processed. My system with about 100 tables is for a company with sales in the hundreds of millions.

But reconnecting 1000 tables is certainly going to take a few seconds, and if users are jumping from one back end to another, I can see the need for discussion.

How many large datasets do you have? If you have 3, for instance, you would just need 3 distinct front ends, and then you avoid the need for reconnections completely.

 

[amorosik]

Thank you very much for taking the time to hopefully help me solve a problem
I understand that everyone sees the problem in their own way, and based on their experiences and preferences tends to provide their own solution, but what I asked in the initial post is not ambiguous
If I had a generic "let's see how..." problem we could also discuss how each would approach a given problem
But we are not in that situation, I want to do this, keep one front-end and be able to connect it with different databases
And I'm looking for a way to speed up the reconnection procedure
The problem is very well defined and limited to a very narrow perimeter
And a solution has already been proposed
If there are others, very good, let's also consider other possibilities
But multiplying front-ends is not an option, no matter how many datasets are to be used

 

[gemma-the-husky]

Ok now it works for me too
The sequence to do is:
- connect the db tables the first time (so that Access has the possibility to change the Connect property of each table)
- start the cycle that scrolls through the tables present and those that have the Connect property starting with ODBC: modify it using the ODBC;FILEDSN=C:\file_dsn.dsn (your routine)

At this point, changing the contents of file_dsn.dsn, the data 'seen' by Access will also correspond to those of the modified db
Obviously the two (or plus) different db's must have the identical structure, because Access remembers the structure read during the first connection to the tables

I don't understand the last paragraph. If you change a named connection to point to a different back end, then this only works if the tables in both backends have the same structure. If they don't then your queries will stop working correctly as fields in those queries will no longer be available.

Do you just mean "must" as a requirement that the backends have identical structures

Out of interest how long does it take to modify all the connect properties in your database?

 

[isladogs]

@amorosik
You may find it useful to attend tomorrow's online Access Europe session on Automating DSN-Less Communication to SQL Server.
You may be able to make use of the ideas for your own setup.
For more details, 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

 

[amorosik]

I don't understand the last paragraph. If you change a named connection to point to a different back end, then this only works if the tables in both backends have the same structure. If they don't then your queries will stop working correctly as fields in those queries will no longer be available.

Do you just mean "must" as a requirement that the backends have identical structures

Out of interest how long does it take to modify all the connect properties in your database?

"..to modify all the connect properties.." if you mean a complete relink of all table, almost 30 sec

 

[amorosik]

@amorosik
You may find it useful to attend tomorrow's online Access Europe session on Automating DSN-Less Communication to SQL Server.
You may be able to make use of the ideas for your own setup.
For more details, 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

Very well, thank you

 

[gemma-the-husky]

"..to modify all the connect properties.." if you mean a complete relink of all table, almost 30 sec

Is that acceptable and better than it was?

 

[amorosik]

Is that acceptable and better than it was?

Waiting 30 sec. to change from Company1 to Company2 is acceptable?
No, is not acceptable, is a very long time for only switch from a db to another