Solved

Determine whether I have a valid connection to a SQL database

Posted on 2016-07-26
13
69 Views
Last Modified: 2016-07-31
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
Comment
Question by:Dale Fye (Access MVP)
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
13 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 41730250
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
 
LVL 48

Author Comment

by:Dale Fye (Access MVP)
ID: 41730262
Scott,

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

Dale
0
 
LVL 48

Author Comment

by:Dale Fye (Access MVP)
ID: 41730367
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 84
ID: 41730372
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41730391
what do you have in your ServerName  and DBName variable? what if you do Msgbox(cnn)? Do you see a valid connection string?
0
 
LVL 4

Expert Comment

by:bfuchs
ID: 41730542
I tried your code with DSN like following and worked. (A2003)

ODBC;DSN=MyDbName;UID=MyUserName;PWD=MyPwd;DATABASE=MyDbName
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41730753
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
 
LVL 48

Author Comment

by:Dale Fye (Access MVP)
ID: 41732103
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
 
LVL 48

Author Comment

by:Dale Fye (Access MVP)
ID: 41732138
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
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 250 total points
ID: 41732199
so my comments in ID 41730391 were on the spot!
0
 
LVL 84
ID: 41732819
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
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41732825
Indeed. But it shows you better compare good vs bad connection string programmatically...

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

Bye, Olaf.
0
 
LVL 48

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 41736825
Thanks, guys
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question