asked on

Connect to SQL server 2008 via VBA-script

I have created a standard script trying to connect to our SQL server 2008 from Word, vba.

When trying to run the scrip I get a run-time error -2147467259 (80004005) [Microsoft] [ODBC Driver Manager].....

I'm running the script from a workstation running Word 2003 and Windows 7. I can connect to my SQL server via Control Panel, ODBC, so I don't think it a firewall issue.

I have attached my script.

Any ideas?

Sub sqlTest()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
With cn
    .ConnectionString = "Data Source=MyServer\MyDatabase;Initial Catalog=MyCatalog;User Id=sa;Password=Test;"
    .Open
    
    Set rs = .Execute("SELECT ID FROM People WHERE FirstName = 'John' AND LastName = 'Smith'")
    MsgBox rs.Fields("ID").Value
    rs.Close
    
    Set rs = Nothing
    
    .Close
End With
Set cn = Nothing
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

plummet

As Acperkins says, you need to make sure your connection string is correct.

I do this by creating a .UDL file somewhere. You can do this by creating a new text file and renaming it eg "test.udl"

A UDL file contains connection details and allows you to create, edit, test and copy the connection string. Double clicking the UDL file should bring up a user friendly interface for you to connect to the SQL database. When it's all working correctly you can open up the UDL file with notepad and copy the connection string out to use in your application. It's stored in unicode so you might need to convert it to ASCII.

I hope that helps.

Zoodiaq

ASKER

Thx.