[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 93
  • Last Modified:

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?
0
Dale Fye
Asked:
Dale Fye
  • 5
  • 3
  • 2
  • +2
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
 
Dale FyeAuthor Commented:
Scott,

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

Dale
0
 
Dale FyeAuthor Commented:
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
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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/
0
 
Éric MoreauSenior .Net ConsultantCommented:
what do you have in your ServerName  and DBName variable? what if you do Msgbox(cnn)? Do you see a valid connection string?
0
 
bfuchsCommented:
I tried your code with DSN like following and worked. (A2003)

ODBC;DSN=MyDbName;UID=MyUserName;PWD=MyPwd;DATABASE=MyDbName
0
 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
 
Dale FyeAuthor Commented:
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.
0
 
Dale FyeAuthor Commented:
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
0
 
Éric MoreauSenior .Net ConsultantCommented:
so my comments in ID 41730391 were on the spot!
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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).
0
 
Olaf DoschkeSoftware DeveloperCommented:
Indeed. But it shows you better compare good vs bad connection string programmatically...

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

Bye, Olaf.
0
 
Dale FyeAuthor Commented:
Thanks, guys
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now