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?

' 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

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 86

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented: 2016-07-26

What's the reasoning for creating a QueryDef and then opening the Recordset? In other words - why not just open the Recordset directly:

strDriver = "SQL Server Native Client 11.0"
cnn = "ODBC;DRIVER=" & strDriver & ";SERVER=" & ServerName _
& ";Trusted_Connection=Yes;APP=Microsoft Office 2003;WSID=" & ServerName _
& ";DATABASE=" & DBName & "; "
Dim rs As New ADODB.Recordset
rs.Open "Your SQL", cnn

LVL 52

Dale FyeOwner, Developing Solutions LLCAuthor Commented: 2016-07-26

Scott,

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

Dale

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

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/

LVL 71

Éric MoreauSenior .Net ConsultantCommented: 2016-07-26

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

LVL 6

bfuchsCommented: 2016-07-26

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

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

LVL 31

Olaf DoschkeSoftware DeveloperCommented: 2016-07-27

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.

Dale FyeOwner, Developing Solutions LLCAuthor Commented: 2016-07-27

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?

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.

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

Éric MoreauSenior .Net ConsultantCommented: 2016-07-27

so my comments in ID 41730391 were on the spot!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented: 2016-07-28

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).

Olaf DoschkeSoftware DeveloperCommented: 2016-07-28

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

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

Bye, Olaf.

Dale FyeOwner, Developing Solutions LLCAuthor Commented: 2016-07-31

Thanks, guys

Determine whether I have a valid connection to a SQL database

Who is Participating?