Solved ODBC link through VBA (1 Viewer)

freuzo

Member
Local time
Today, 05:52
Joined
Apr 14, 2020
Messages
98
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

I’m here to help
Staff member
Local time
Yesterday, 21:52
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 05:52
Joined
Apr 14, 2020
Messages
98
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

Lifelong Learner
Local time
Yesterday, 21:52
Joined
Mar 14, 2017
Messages
8,738
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

I’m here to help
Staff member
Local time
Yesterday, 21:52
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 05:52
Joined
Apr 14, 2020
Messages
98
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

Member
Local time
Today, 05:52
Joined
Apr 14, 2020
Messages
98
Hi everyone,
Just to close this,
After some research, I found what I was looking for.
A function with something like that :

Code:
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

I’m here to help
Staff member
Local time
Yesterday, 21:52
Joined
Oct 29, 2018
Messages
21,358
Hi everyone,
Just to close this,
After some research, I found what I was looking for.
A function with something like that :

Code:
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.
 

Users who are viewing this thread

Top Bottom