Excel macro connect to database driver error

We have a macro made that has to connect to an sql server/database before proceeding.

But I think there's a driver issue, and I'm not sure what else I can try.

The macro spreadsheet worked before I did a sql management studio on my client PC and now when I run the macro I get an error:

"Unable to connect to the database. Please try again later".

This is the line I'm using:

Const cnct As String = "DRIVER=SQL Native Client;SERVER=SQL001\SQL001;UID=user;PWD=password;Database=sql_001;"

I know the database name and server and credentials are correct, because when I use "Provider=SQLOLEDB.1" in place of "DRIVER-SQL Native Client" I don't get the error and our spreadsheet can continue to run.

What else besides DRIVER or Provider can we use?
garryshapeAsked:
Who is Participating?
 
fhillyer1Connect With a Mentor Commented:
for your SQL Native you can use the following

DRIVER=SQL Native Client;SERVER=NFP10\DYNAMICS;UID=sa;;APP=2007 Microsoft Office system;WSID=NFP10;DATABASE=HTC;

just replace your server and database
0
 
fhillyer1Commented:
i use the following

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=DATABASEHERE;Data Source=NFP10\DYNAMICS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=NFP10;Use Encryption for Data=False;Tag with column collation when possible=False
0
 
fhillyer1Commented:
i do have the native client on SQL 2005, but i dont use it
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
garryshapeAuthor Commented:
DRIVER=SQL Native Client;SERVER=NFP10\DYNAMICS;UID=sa;;APP=2007 Microsoft Office system;WSID=NFP10;DATABASE=HTC;

I tried changing to that with specifying my username and server/database, but I still get this "Could not connect to database! Please try again later"
0
 
garryshapeAuthor Commented:
I think I found the issue

I went into SQL Server Configuration Manager and expanded the "SQL Native Client Configuration" in the left pane, and under "Client Protocols" there is a TCP/IP listed, and it was "disabled". After I changed this to "enabled" I was able to run the report and it could connect to the database.
0
 
fhillyer1Commented:
good, that you found it, i guess on mine worked because when i configured my SQL i always go to the surface config and enable everthing on the connections except for named pipes
0
All Courses

From novice to tech pro — start learning today.