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:
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.
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.
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
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.
Guess I'm just used to working with DAO, and rarely use ADO. I'll give that a try.
Dale