VBA Code to Connect and Query a MySQL Table

[WPW07]

Hello Everyone,

I'm using Access 2003. I manually set up an ODBC connection to a
MySQL table using MySQL ODBC Driver 3.51. I then linked to the MySQL
table using that ODBC connection and created a query from that MySQL
table. I created a query for a combo box on a form that is bound to
that query. This works fine for testing purposes. But when I roll
this out, I'd like to have some VBA code in the OnClick event of the
combo box that connects to the MySQL database, then queries the MySQL
table to populate the combo box list.

I'm a little rusty with VBA coding, so I was reaching out for some
help. Can anyone get me started in the right direction?

Thanks for any help!

 

[DanRoss]

Here's a quick example:

'ADO Objects
Dim myCN As New ADODB.Connection
Dim myRS As New ADODB.Recordset

'ComboBox
Dim oCB As ComboBox
Set oCB = Me.Combo10

'Open Connection
myCN.ConnectionString = "[your connection string]"
myCN.Open

'open query
Set myRS.ActiveConnection = myCN
myRS.Open ("select this from yourdb..yourtable")


'Setup Combo box
oCB.RowSourceType = "Value List"
oCB.RowSource = ""
'Populate values
Do Until myRS.EOF
oCB.AddItem myRS(0)
myRS.MoveNext
Loop

'clean up
myRS.Close
myCN.Close
Set myCN = Nothing
Set myRS = Nothing

 

[Albert D. Kallal]

You might want to explain why you propose to write a whole bunch of code to
load up a combo box when you can just simply use a linked table for the
combo box source?

All you need here is to correctly setup your linked tables.

Note that if your combo box needs sorting, or does NOT return all Columns,
you'll find performance will remain snappy if you went directly to a view.

in other words I'm suggesting to place only a table name, or a view named as
the source for the combo box:

eg:vLocationList

(so don't specify anything more for the combo box, except just a table name,
or more likely to have you name. Specify the sorting an additional column
information inside of the view, not in the actual SQL of the combo box -- I
am suggesting this for your performance to remain snappy).


adopting the above reproach who will mean that you do not have to write one
line of code to load up and maintain a combo box, and the performance will
remain well.

To manage your linked tables on startup, you need to use a DSN-less
connection. code sample here:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

I am at a considerable loss as to why you're asking to dump a linked table,
and then write a whole whack of code to populate a combo box when you can
just simply specified a linked table view here?

Furthermore, I should also point out that you can assign a recordset
direclty to the combo box (since a2002 you can do this) So, you can well
ignore that long sample of code posted in this thread to populate the combo
box by looping...you don't need to do that *even* if you resort to creating
a reocrdset in code (which you don't need to do..).

 

[WPW07]

You might want to explain why you propose to write a whole bunch of code to
load up a combo box when you can just simply use a linked table for the
combo box source?

All you need here is to correctly setup your linked tables.

Note that if your combo box needs sorting, or does NOT return all Columns,
you'll find performance will remain snappy if you went directly to a view.

in other words I'm suggesting to place only a table name, or a view named as
the source for the combo box:

eg:vLocationList

(so don't specify anything more for the combo box, except just a table name,
or more likely to have you name. Specify the sorting an additional column
information inside of the view, not in the actual SQL of the combo box -- I
am suggesting this for your performance to remain snappy).

adopting the above reproach who will mean that you do not have to write one
line of code to load up and maintain a combo box, and the performance will
remain well.

To manage your linked tables on startup, you need to use a DSN-less
connection. code sample here:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

I am at a considerable loss as to why you're asking to dump a linked table,
and then write a whole whack of code to populate a combo box when you can
just simply specified a linked table view here?

Furthermore, I should also point out that you can assign a recordset
direclty to the combo box (since a2002 you can do this) So, you can well
ignore that long sample of code posted in this thread to populate the combo
box by looping...you don't need to do that *even* if you resort to creating
a reocrdset in code (which you don't need to do..).

Thank you Dan and Albert,

I may be going down the wrong road. I thought I had write code with a
connection string so that new people who use the Access database would
automatically be linked to the MySQL table. I want to avoid having to
go to each PC for a new user and create the ODBC connection. Am I way
off base here? I'm hoping for a solution that is easy to manage..

 

[DanRoss]

Just link the table using a driver reference rather than an ODBC connection,
something like:

Driver={mySQL};Server=myServerAddress;Port=3306;Option=131072;Stmt=;
Database=myDataBase;Uid=myUsername;Pwd=myPassword;
Taken from: http://www.connectionstrings.com/?carrier=mysql

as long as your clients have the correct driver installed you should be
good.

You can also look into Access Pass Throug queries rather than linked tables
if you want to offload some of the processing to the server. Then you can
write quries with native MySQL commands.

 

[WPW07]

Thanks Dan. I used a pass through query at first and it worked on my
machine. it choked when I tried to run it from the test user's pc for
some reason, so that's when i manually linked the table for the user
with the ODBC Dat Source Administrator. the pass through query should
work though, as long as the driver is installed correctly? i don't
need to do anything else as long as when I link the table, i use the
same user id and password that the pass through query uses?

 

[WPW07]

Also, If I want to use the driver reference you mentioned, where would
I place that code, in a module? Would I make it a function that is
called from the onclick event of the combo box? What would that code
look like?

 

[Albert D. Kallal]

Thank you Dan and Albert,

I may be going down the wrong road. I thought I had write code with a
connection string so that new people who use the Access database would
automatically be linked to the MySQL table. I want to avoid having to
go to each PC for a new user and create the ODBC connection. Am I way
off base here? I'm hoping for a solution that is easy to manage..

Actually you're going down the right road. The way this works is that you'll
take your data tables that were once a MS access, and move them to your
database server (oracle, Sql server, or on this case MySql).

You then delete the local tables, and replace them with what we call linked
tables to your SQL server. At this point to find most of the forms, boxes
and just about anything else who should work, with a few tweaks here and
they're needed for performance.

Once you have all of the links and application running correctly, then you
convert the application into a mde (converting to mde is not required, but
it forces you to ensure that all of your code compiles correctly), you then
distribute (place/install) this new mde on each users work station.

As a general rule the links to the sql server back should remain intact. You
certainly don't have to write any new code for forms reports or queries, or
even for those combo boxes. They should remain functional and work just fine
being linked to sql server, or remain attahced to a local table.

The only issue here to manage is to ensure that the link to MySql is active.
That obivlity means that the ODBC driver will have to be instlaled on each
workstatiion (preferably before you attempt to run your access application).
There is also the possibility that the production server you use is
different than your development, or testing debugger server. That is why I
suggested to the use DNS less code sample. This code can be run on startup
to connect to the correct data base of your choice, who once this connection
code is run, each additional lunching of the application should in fact not
require you to run that code.

So simply move and transfer your access table data to the database server,
and then replace the table names that were once the local tables, with what
is called a linked table. Your forms reports and things like combo boxes
should continue to run as before, and you'll not be fooling around
connection strings when you use a linked table.

You just have to play around and try creating a few table links to your
database server, at that point I think you'll instantly see what is going on
here. when you have a linked table in the table definitions, if you open
that it'll open up the table as if it was local, but the date is coming from
your database server....

 

[WPW07]

Dan, To experiment, I've attempted to write some code to use with the
drop down box. However, it's not working. Nothing is getting
populated. I think it's making an ODBC connection because I'm not
getting an error. Any idea where I'm making the mistake? Here is the
code I'm using:


Private Sub Combo4_Click()

'ADO Objects
Dim myCN As New ADODB.Connection
Dim myRS As New ADODB.Recordset

'ComboBox
Dim oCB As ComboBox
Set oCB = Me.Combo4

'Open Connection
myCN.ConnectionString = "Driver={MySQL ODBC 3.51
Driver};Server=test.domain.org;Port=3306;Database=art;User=test;
Password=xxx;Option=3;"
myCN.Open

'open query
Set myRS.ActiveConnection = myCN
myRS.Open ("SELECT name FROM images")

'Setup Combo box
oCB.RowSourceType = "Value List"
oCB.RowSource = "name"
'Populate values
Do Until myRS.EOF
oCB.AddItem myRS(0)
myRS.MoveNext
Loop

'clean up
myRS.Close
myCN.Close
Set myCN = Nothing
Set myRS = Nothing

End Sub


Thanks again.

 

[DanRoss]

Put the connection string in the connection string property of the PTQ.

The builder will ask you for an ODBC DSN but any Valid ODBC properties will
work.

 

[WPW07]

Thanks Dan. But doesn't this mean I still have to first set up a
machine data source on each PC? When I open the property dialog box
for the query, on the ODBC str line, there is an ellipse, that when
clicked brings me to the Select Data Source dialog box, where I need
to select a data source. This is what I want to bypass. Is that
possible?

 

[DanRoss]

Yeah the builder asks for an ODBC DSN. Simply Edit the string with the
appropriate ODBC Connection String.

the value in the property should looksomething like

"ODBC;
Driver={mySQL};Server=myServerAddress;Port=3306;Option=131072;Stmt=;Database=myDataBase;Uid=myUsername;Pwd=myPassword;"

rather than

"ODBC; DSN=DSNNAME,. . . .. . "

 

[WPW07]

Eureka....success!! (at least in my test environment). Thank you very
much for your help with this Dan. And Albert, thank you for the
advice regarding the bigger picture, it is much appreciated...