Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Connect to SQL server 2008 via VBA-script

Posted on 2011-09-11
3
Medium Priority
?
598 Views
Last Modified: 2012-05-12
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
Comment
Question by:Zoodiaq
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 36519824
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
 
LVL 10

Expert Comment

by:plummet
ID: 36521608
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
 

Author Closing Comment

by:Zoodiaq
ID: 36525698
Thx.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question