Thursday, October 13, 2005

DB access in word VBA

I was working on little program to extract data from a word document, and insert it into a database. Pretty straightforward VBA for a VBA developper... Except I'm not a VBA developper.

Actually it proved quite simple up to the point where I need to make an ODBC connection to the database. Then all hell broke loose.

In time honoured VBA programming practise, when you hit upon a problem, record a macro, and do the step you want to, then view and edit the code.. .

This I did and discovered the nice looking InsertDatabase [Link to msdn]
. Worked a treat.. Read data from the odbc, and displayed in it the doc. Wonderful!

Except for the teeny weeny problem thatI wanted to display the data in a userForm. Reading the docs in that link above, the InsertDatabase method can only be called on a Range object. Try as I might I couldn't find a Range object in the UserForm.

Long story short.. I found the ADO object which solved my problems.. Gives you a full API for talking to DB's. You will need to install the reference libraries Microsoft AxtiveX Data Object library (you will need your office install media for this), and include them in your references first.

I did briefly run into the infamous Error 80004005 "Data Source Name Not Found"

But with a bit of tinkering around I got this to work.

First step, create a systemDSN (userDSN should also work) of the name for the ODBC connection you want (vba in this instance)

Then the following code calls a table there and inserts the data into my UserForm (called UserInput)


Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String

Const ConnectString = "DATABASE=vba;DESCRIPTION=Vba Test;DSN=vba;OPTION=0;PORT=0;SERVER=localhost"


cn.ConnectionString = ConnectString
'cn.CursorLocation = adUseClient
cn.Open

' Find out if the attempt to connect worked.
If cn.State = adStateOpen Then
Set rs = cn.Execute("Select * From test")
If rs.EOF Then
UserInput.namesList.AddItem "No Entries returned from Database."
Else
While Not rs.EOF
UserInput.namesList.AddItem rs("data") & ":" & rs("date")
rs.MoveNext
Wend
End If
'MsgBox (rs("data") & ":" & rs("date"))
rs.Close
Else
MsgBox "Sorry there was a problem connecting to the database."
End If

cn.Close


Wonderful..

Any questions, drop me a comment,