Avatar of Dale Fye
Dale Fye
Flag for United States of America asked on

Determine whether I have a valid connection to a SQL database

I have the following code which attempts to create a query and recordset to determine whether the parameters in a form for ServerName and dbName are valid:

strDriver = "SQL Server Native Client 11.0"
cnn = "ODBC;DRIVER=" & strDriver & ";SERVER=" & ServerName _
    & ";Trusted_Connection=Yes;APP=Microsoft Office 2003;WSID=" & ServerName _
    & ";DATABASE=" & DBName & "; "
Set db = CurrentDb
Set qdf = db.CreateQueryDef("")
qdf.Connect = cnn
qdf.ReturnsRecords = True
qdf.SQL = "SELECT top 1 dbo.SomeTableName. * from dbo.SomeTableName"
Set rs = qdf.OpenRecordset()
rs.Close

Open in new window

This code bombs out on the line that starts: SET rs =

With an error that reads:

 3151         ODBC--connection to 'SQL Server Native Client 11.0ServerName' failed.

But if I copy the connection string generated above:
ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=ServerName;Trusted_Connection=Yes;APP=Microsoft Office 2003;WSID=ServerName;DATABASE=dbName;

Open in new window

and create a new pass-through query with identical SQL string, it returns the 1 record I'm looking for, so the connection is valid, but I keep getting this error message.

Any ideas what I might be doing wrong?
Microsoft AccessMicrosoft SQL Server

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
SOLUTION
Scott McDaniel (EE MVE )

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Dale Fye

ASKER
Scott,

Guess I'm just used to working with DAO, and rarely use ADO.  I'll give that a try.

Dale
Dale Fye

ASKER
Scott,

I'm getting an error:

-2147467259   [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

But with the exact same SQL string with is assigned to the linked tables
Scott McDaniel (EE MVE )

Try a different connection string:

Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;Uid=myUsername;
Pwd=myPassword;

REfer to here for more info on strings:

https://www.connectionstrings.com/sql-server/
Your help has saved me hundreds of hours of internet surfing.
fblack61
Éric Moreau

what do you have in your ServerName  and DBName variable? what if you do Msgbox(cnn)? Do you see a valid connection string?
bfuchs

I tried your code with DSN like following and worked. (A2003)

ODBC;DSN=MyDbName;UID=MyUserName;PWD=MyPwd;DATABASE=MyDbName
Olaf Doschke

qdf is created in CurrentDb, and then you set a connstring? To the same database? Another? Forget this, if it is normal, I don't work much with Access, but it seems odd to root a querydefinition in some database and then impose a new connection on it.

Bye, Olaf.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Dale Fye

ASKER
OK, so this is really strange.  Assume:

ServerName = "XXX"
DBName = "YYY"

I found that if I construct my connection string dynamically at runtime (lines 1&2) below, I get an error message on the rs.Open line below.  But if I define the connection string as depicted in lines 3&4, the recordset opens properly.  Anybody have any ideas?
'   strConn = "DRIVER={SQL Server Native Client 11.0};" _
            & "SERVER=" & ServerName & ";DATABASE=" & DBName & ";Trusted_Connection=Yes;"
    strConn = "DRIVER={SQL Server Native Client 11.0};" _
            & "SERVER=XXX;DATABASE=YYY;Trusted_Connection=Yes;"

    Dim rs As New ADODB.Recordset
    rs.Open "SELECT COUNT(*) as RecCount FROM SomeTable", strConn

Open in new window

Prior to running this segment of code, I get the ServerName and dbName from the registry, use the NZ() function to give them a default value and then TRIM() the values to make certain that the strings don't contain any extraneous characters.  

When I print the connection strings, they are identical but the first returns the error:

-2147217843   [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open database "YYY" requested by the login. The login failed.
Dale Fye

ASKER
I'm an idiot, or I need better glasses.

The dbName variable contained an underscore instead of a hyphen in one character and I kept missing it when I looked at the error message and the connection strings when printed, one above the other.

Thanks all, for you help and recommendations.  I actually prefer the ADO method you show above Scott, over the querydef method I've used in the past.

Dale
ASKER CERTIFIED SOLUTION
Éric Moreau

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Scott McDaniel (EE MVE )

I'm an idiot, or I need better glasses.
I always blame it on the glasses!!

Glad you got it sorted. I much prefer to use the ADO method when working with SQL Server (or any other server database, for that matter).
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Olaf Doschke

Indeed. But it shows you better compare good vs bad connection string programmatically...

https://youtu.be/Dkjkh3OrjeA?t=2m56s

Bye, Olaf.
Dale Fye

ASKER
Thanks, guys