• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 605
  • Last Modified:

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

0
Zoodiaq
Asked:
Zoodiaq
1 Solution
 
Anthony PerkinsCommented:
Your connection string is wrong.  It should be something like this:
Provider=sqloledb; Data Source=MyServer; Initial Catalog=MyDatabase; User Id=sa; Password=Test;

See here for a full list:
http://www.connectionstrings.com
0
 
plummetCommented:
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.
0
 
ZoodiaqAuthor Commented:
Thx.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now