Solved

Connect to SQL server 2008 via VBA-script

Posted on 2011-09-11
3
585 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
3 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

828 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