Solved: Using DAO to connect to odbc database

[Pirke]

Hello,

I'm trying to get create a word macro, that can make a connection to a database with odbc, read out 1 record (ID is known and given by the user) and fill from that record several fields in a word doc. We use Word XP, and the database is a MySQL database on a webserver.

I know VBa, and know how to make bookmarks and put some variables into the bookmarks. I can create a userform, ask the user for the id, etc etc.
But I do not have any experience with database connections and getting this info with ODBC.

I hope somebody here can help me wtih this. If you need more info, feel free to ask!

with kind regards, peter

[Ken Puls]

Hi there,

One of your countrymen submitted this to our knowledgebase: Get contacts data via DAO to document. Maybe it will help?

And welcome to VBAX! :)

[Pirke]

Ken,

Yes, I saw that article, but it's not exactly what i need.
I want to make a connection to a sql-database with ODBC, and for that I need the code.
Maybe it's the same as to a *.mdb but I would like an example with all parameters etc I should use.

thanks for the help sofar

Peter

[Ken Puls]

Hi Peter,

All of my experience in connecting to databases is using ADO not DAO. I've brought this thread to the attention of someone who knows DAO though, so hopefully they'll reply.

I'll keep watching though...

[Rembo]

Hello Peter and Ken,


I want to make a connection to a sql-database with ODBC, and for that I need the code.

You will also need a driver. I'm not sure if you installed it already but if not you can download the driver here:

http://dev.mysql.com/downloads/connector/odbc/3.51.html

I suppose it will also be possible to setup a connection using the OLEDB driver from Microsoft (MSDASQL) but I'd stick with the driver from the database manufacturer.


Maybe it's the same as to a *.mdb but I would like an example with all parameters etc I should use.

As a rule of thumb you best use DAO for Jet Engine based databases (Like Access, Fox Pro etc) and ADO for other databases. MySQL falls in the latter category so you best go with ADO.

MySQL has a sample VBA routine in their documentation. You can find it here:

http://dev.mysql.com/doc/refman/5.0/en/ado--rs-addnew.html (http://dev.mysql.com/downloads/connector/odbc/3.51.html)

I can't test it from here but if you just want to read data from a MySQL database I imagine the routine would look something like the one below. I wrote the database fields and values to an Excel workbook but you might like to write it plain and simple to your word document, or insert it into a table. Whatever you prefer, the general idea is exactly the same.

Private Sub MyODBC_2_MySQL()
'Rembo wrote this routine
'You can find me at vbaexpress.com

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String

'Connect to MySQL server using MySQL ODBC 3.51 Driver
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=your_hostname;" & _
" DATABASE=your_database;" & _
"UID=your_login_name;PWD=your_password; OPTION=3"

'Create SQL string
sql = "SELECT * FROM my_table WHERE some_field_value='my_value';"

'Open the connection
conn.Open

'Create a recordset and set the CursorLocation property for record navigation
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient

'Fetch data
rs.Open sql, conn

'Move to the first record
rs.MoveFirst

'Cycle through all the fields and write name to worksheet
'You could omit this step if you just want data
Dim i As Integer
For i = 1 To rs.Fields.Count
Worksheets(1).Cells(1, i).Value = rs.Fields(i - 1).Name
Next i

'Write all data to your worksheet
Dim j As Integer
For j = 1 To rs.RecordCount
For i = 1 To rs.Fields.Count
Worksheets(1).Cells(j + 1, i).Value = rs.Fields(i - 1).Value
Next i
rs.MoveNext
Next j

'Close connection again
rs.Close
conn.Close

End Sub

If you experience any problems let me know and I'll see if I can setup a test database for this.

Please note: if you want to use this routine you'll have to set a reference to the Microsoft ActiveX Data Objects 2.x Library. I use version 2.8 but it might differ on your computer.
Also, if you previously set a reference to the Microsoft DAO 3.x Object Library it's a good idea to uncheck it when using ADO because I believe they have some common method names. Mixing them up can lead to unexpected results.

Hope that helps,

Rembo

[Ken Puls]

Thanks, Remco! Much appreciated! :yes

[Pirke]

Rembo (and Ken)

thanks for the help, this got me going and I'm now able to get the data from the database as i want it

again thanks, this will make me return when ever I have another question ;-)

grts, Pirke

[Ken Puls]

Great! Glad it worked out for you. :)

[Rembo]

No problem, glad you could sort it :).

Cheers,

Rembo