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:
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:
Any ideas what I might be doing wrong?
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
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;
Any ideas what I might be doing wrong?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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;A
& ";DATABASE=" & DBName & "; "
Dim rs As New ADODB.Recordset
rs.Open "Your SQL", cnn
strDriver = "SQL Server Native Client 11.0"
cnn = "ODBC;DRIVER=" & strDriver & ";SERVER=" & ServerName _
& ";Trusted_Connection=Yes;A
& ";DATABASE=" & DBName & "; "
Dim rs As New ADODB.Recordset
rs.Open "Your SQL", cnn
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
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=
Pwd=myPassword;
REfer to here for more info on strings:
https://www.connectionstrings.com/sql-server/
Provider=SQLNCLI11;Server=
Pwd=myPassword;
REfer to here for more info on strings:
https://www.connectionstrings.com/sql-server/
what do you have in your ServerName and DBName variable? what if you do Msgbox(cnn)? Do you see a valid connection string?
I tried your code with DSN like following and worked. (A2003)
ODBC;DSN=MyDbName;UID=MyUs
ODBC;DSN=MyDbName;UID=MyUs
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.
Bye, Olaf.
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?
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.
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
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
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
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).
Indeed. But it shows you better compare good vs bad connection string programmatically...
https://youtu.be/Dkjkh3OrjeA?t=2m56s
Bye, Olaf.
https://youtu.be/Dkjkh3OrjeA?t=2m56s
Bye, Olaf.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial