Using VBA to connect Word 2003 to SQL Server 2000

[Ploobers]

I've been searching around to figure out how to access a SQL Server
table programatically. I am very familiar with ADODB, but I have never
used it in Word VBA before. All I need to do is figure out how to open
the connection so I can access records and I can handle the rest. This
is some sample code I've used that I found elsewhere, but no matter
what I put it, it hangs up on the declaration of setting up ADODB
connections, making a Workspace. VBA doesn't even show ADODB as a
valid object. How can I connect? Thanks in advance!

'Dim odbc As Workspace
'Dim sql As Connection
'Dim connectStr As String

'connectStr = "ODBC;DRIVER={SQL Server};" + _
"SERVER=servername;" + _
"DATABASE=database name;" + _
"UID=username;" + _
"PWD=password;" + _
"LOGINTIMEOUT=5;"


'Set odbc = CreateWorkspace("Test Sql", "username", "password",
dbUseODBC)
'Set sql = odbc.OpenConnection("TestSql", dbDriverCompleteRequired,
True, _
connectStr)


' Do work


' sql.Close
'odbc.Close

 

[Jonathan West]

Hi Ploobers

It looks like all you need to do is set a reference to the appropriate
version of the Microsoft ActiveX Data Objects library. In the VBA editor, go
to Tools, References, and check the appropriate library.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Ploobers]

Thank you so much for the quick reply. I feel pretty stupid now,
especially because I still can't get it to work. I added the reference
for ADODB and that code works fine, but it is still hanging up on
creating the workspace. What exactly is a workspace and do I even need
to use it?

 

[Jonathan West]

Thank you so much for the quick reply. I feel pretty stupid now,
especially because I still can't get it to work. I added the reference
for ADODB and that code works fine, but it is still hanging up on
creating the workspace. What exactly is a workspace and do I even need
to use it?

I don't know. I'm not an ADODB expert - this group deals mainly with Word
VBA issues. Now that you have the connection working, I suggest you try a
newsgroup that deals with ADO issues. Perhaps microsoft.public.data.ado or
microsoft.public.vb.database.ado


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org