asked on

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?

fhillyer1

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

fhillyer1

i do have the native client on SQL 2005, but i dont use it

ASKER CERTIFIED SOLUTION

fhillyer1

membership

This solution is only available to members.

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

Start Free Trial

Garry Shape

ASKER

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"

Garry Shape

ASKER

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.

fhillyer1

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