Solved ODBC link through VBA

[freuzo]

Hi everyone,
I used the control panel to set the odbc and link my application to a MYSQL database and it work well.
Now I'm about to create another application and want to make the linking through VBA.

My question is when do I create the connection ?
I guess it should be at the launch of the application.
Then if the connection is make at that moment, are the linked tables and views downloaded and available for all the queries and bound forms or do I need to recreate the connection everytime ?

Thanks in advance.

 

[theDBguy]

Hi. Are you saying you don't want your FE to have permanent linked tables? Meaning, if a user opens your FE in a non-trusted location, they won't see any tables listed? Is this for security reasons?

As for when to execute your linking code, you could run on the application startup.

 

[freuzo]

i. Are you saying you don't want your FE to have permanent linked tables?

That was my question. Will the linked tables be permanent ?
I don't want to have to go set up the connection through control panel on every user's machine like I did with the previous app.

 

[Isaac]

I think what you mean is that you want to actually create DSN's using code. This is very possible, I used to do it in a former app of mine.

It's difficult to tell from your original post if you 1) want to create a DSN in code, 2) want to link to tables in your database in code (two totally different things), or both.

 

[theDBguy]

That was my question. Will the linked tables be permanent ?
I don't want to have to go set up the connection through control panel on every user's machine like I did with the previous app.

Yes, and to avoid setting it up for every user, use a File DSN, instead of a System DSN.

 

[freuzo]

It's difficult to tell from your original post if you 1) want to create a DSN in code, 2) want to link to tables in your database in code (two totally different things), or both.

Sorry maybe I really don't know the difference. I just want to avoid setting up the connection for every user.

 

[freuzo]

Yes, and to avoid setting it up for every user, use a File DSN, instead of a System DSN.

Okay I'm going to read about that File DSN thing.
Thanks.

 

[freuzo]

Hi everyone,
Just to close this,
After some research, I found what I was looking for.
A function with something like that :

Set ADOConn = New ADODB.Connection
ADOConn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};SERVER=localhost;DATABASE=testdb;USER=root;PASSWORD=123456;OPTION=3;"
ADOConn.CursorLocation = adUseClient
ADOConn.Open

I don't need to manualy set an odbc connection on my users' PC anymore.

Thank you all.

 

[theDBguy]

Hi everyone,
Just to close this,
After some research, I found what I was looking for.
A function with something like that :

Set ADOConn = New ADODB.Connection
ADOConn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};SERVER=localhost;DATABASE=testdb;USER=root;PASSWORD=123456;OPTION=3;"
ADOConn.CursorLocation = adUseClient
ADOConn.Open

I don't need to manualy set an odbc connection on my users' PC anymore.

Thank you all.

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.