Relink ODBC Tables trough VBA (1 Viewer)

[LeoM]

Good afternoon everyone. Having a FrontEnd MS Access DB (shared with users) with one linked ODBC Table (located in another separate database) I'm looking to a function who can reconnect that ODBC Table when the end user doesn't have any DSN. This because I would like to avoid to create a DSN on each end user i share the FrontEnd. I have all necessary information (server name, uid, psw, etc). Any suggestion please ? I tried to look around but, or I don't understand ho to do or I cannot use.
Cheers.

 

[Josef P.]

You can use a DSN less connection string.

ConnectionStrings.com - Forgot that connection string? Get it here!

All connection strings in one place. Find the syntax for your database connection using ADO.NET, ADO, ODBC, OLEDB, C#, VB, VB.NET, ASP.NET and more.

www.connectionstrings.com

Note: if you store the password in the connection string of the linked table, it can be read with little effort.

 

[LeoM]

Thank you for answer, yes i saw it but is not clear to me. It could be possible to get an example of reconnection in a small vba function? Thanks.

 

[Josef P.]

AccessCodeLib/data/odbc/OdbcHandler.cls at master · AccessCodeLib/AccessCodeLib

Reusable VBA code for Microsoft Access applications - AccessCodeLib/AccessCodeLib

github.com

But there are certainly many examples to be found in this forum.

 

[Gasman]

Having a FrontEnd MS Access DB (shared with users)

NOT recommended.

 

[LeoM]

NOT recommended.

Not recommended share FrontEnd database with user ? I suppose is the correct way to do having FE and BE for multiple users database.
Cheers

 

[Gasman]

Each has their own copy on their computer, never shared.

 

[LeoM]

Each has their own copy on their computer, never shared.

Apologies, when i mentioned "shared", i mean that i share with each of them a local FE DB. I understood that the "shared" word maybe is not the correct one.

 

[Pat Hartman]

Individual users should NEVER be relinking the BE. When you upload the new FE to the master folder on the server, you relink the tables at that time. It is far better to use the UNC format -- \\servername\foldername\database_BE.accdb Rather than using specific drive letters. When you use specific drive letters, you may run into trouble if you have a lot of mapped drives and you don't use consistent drive letters. So, if you have a bunch of users and a bunch of drives, you might end up with not being able to find a drive letter that is available on every workstation.

 

[LeoM]

Individual users should NEVER be relinking the BE. When you upload the new FE to the master folder on the server, you relink the tables at that time. It is far better to use the UNC format -- \\servername\foldername\database_BE.accdb Rather than using specific drive letters. When you use specific drive letters, you may run into trouble if you have a lot of mapped drives and you don't use consistent drive letters. So, if you have a bunch of users and a bunch of drives, you might end up with not being able to find a drive letter that is available on every workstation.

Thank you, absolutely yes, I do exactly like that exactly to avoid problems with different network drive letters setting. Usually I prepare the interface with linked table (using the structure \\servername\......) and than I locate it in a specific folder where the users will copy and paste in their own local drive. Cheers.

 

[Pat Hartman]

So, each user has his own BE? That's unusual.

 

[LeoM]

So, each user has his own BE? That's unusual.

Absolutely no. In the FE database, which is deployed, tables are linked to the BE which is one for all.

 

[Pat Hartman]

OK, then the link happens only once when you first distribute the FE. The users never have to link again.