How to Turn off Trusted Connection thru ODBC to MS SQL Server using MS Access?

I am using a laptop connected to a PC card and in a timely fashion, need to check if there is a connection and continue with an update. Currently I am using code that creates a linked table through an ODBC connection. The process will fail when there is no connection. The problem is, when the attempt is made, I have to shut down the application and reopen, even if the connection has been reatored.

Is there any way in checking to see if the ODBC connection is Present before I try to refresh a link or create a new table?

Is there a way find the Instance of SQL server being used is available without having any affect on the application so that I don't have to reopen it again?

Any help would be greatly appreciated.

Thanks

Frank Srebot
fsrebotAsked:
Who is Participating?
 
MikeTooleCommented:
I think that the DSN parameter is meant only for Machine data sources - the names are stored in the Registry so ADO knows where to look.
This worked for me with a File data source
    Dim cn As New ADODB.Connection
    cn.Open "FileDSN=C:\Users\MikeT\Documents\SandPit.dsn"
0
 
dbaSQLCommented:
sql 2005 has a new system procedure --- sp_testlinkedserver  --- which allows you to validate the linked server connections.  here is a nice sample of how to use it:

http://blogs.msdn.com/sqltips/archive/2005/06/07/426578.aspx
0
 
fsrebotAuthor Commented:
Thank you for the responce. I think the answer is close, but I have to test the instance of SQL SERVER 2005 express from a MS ACCESS 2003  GUI front end. The example demonstartes SQL Server to SQL Server.

Thanks.

Frank
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dbaSQLCommented:
I don't have an Access box here to test, but I would check and see whether it will work for you, from Access to SQL.  Remember, Access/Jet can be a linked server/datasource accessed via SQL.   sp_addlinkedserver works from SQL to Access

I want to think you outta be able to do it from Access to SQL.
0
 
MikeTooleCommented:
Try the function in the attached snippet.
It takes the connection string from a table linked to the server you're interested in and opens an ADODB connection to it.
Just replace the table name with one of your own.
Public Function IsConnected() As Boolean
    Dim strConnect As String
    strConnect = CurrentDb.TableDefs!Production_Product1.Connect
    Dim cn As New ADODB.Connection
    cn.ConnectionTimeout = 5 ' Seconds: the default is 15
    cn.Open strConnect
    IsConnected = (cn.State = adStateOpen)
    
End Function

Open in new window

0
 
fsrebotAuthor Commented:
Thank you for the response again. I think the answer is close, but I have to test the instance of SQL SERVER 2005 express from a MS ACCESS 2003  GUI front end. The example demonstrates a test to see if a linked table has a connection. I was wondering if there was a test to find if a DSN connection to an instance SQL Server was open or closed. I will be utlilizing Pass Through Queries to comunicate with SQL Server so there will be no linked tables, Only a connection string to the SQL Server.
Thanks.

Frank
0
 
MikeTooleCommented:
Frank,
My example just uses the Linked table as a convenient place to get connection information. you can cut out the table reference and supply your own connection string to the cn.Open command.
Mike
0
 
fsrebotAuthor Commented:
Hi Mike    
I used:
Dim strConnect As String
    strConnect = "ODBC;DSN=FGSmainEXPRESS;UID=sa;PWD=!1solutions;DATABASE=FGS Tables"
        Dim cn As New ADODB.Connection
    cn.ConnectionTimeout = 5 ' Seconds: the default is 15
    cn.Open strConnect
    IsConnected = (cn.State = adStateOpen)
and got the following error:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
 The connection string works for a pass through query.

Am I close?

Thanks

Frank
0
 
MikeTooleCommented:
<DSN=FGSmainEXPRESS>
I think that you need to supply the whole connect string rather than just the DSN name.
0
 
fsrebotAuthor Commented:
Hi Mike,
I tried the change and still got the same error.

Dim strConnect As String
  OLD- strConnect = "ODBC;DSN=FGSmainEXPRESS;UID=sa;PWD=!1solutions;DATABASE=FGS Tables"
  NEW strConnect = "ODBC;DSN=.\FGSSQLEXPRESS;UID=sa;PWD=!1solutions;DATABASE=FGS Tables"
        Dim cn As New ADODB.Connection
    cn.ConnectionTimeout = 5 ' Seconds: the default is 15
    cn.Open strConnect
    IsConnected = (cn.State = adStateOpen)
I  also tried 'fgs-dolutions\FGSSQLEXPRESS' (the name of the computer) instead of .\FGSSQLEXPRESSand it got the sme error.

thanks
frank
0
 
fsrebotAuthor Commented:
Hi Mike,
   The File data source suggestion works fine. Thankyou very mush for your support. I will need this test for database integration method I am developing for a mobile computing system.

Thanks again!!

Frank
0
 
fsrebotAuthor Commented:
I really appreciate the help. Something I thought that seemed to be a trivial issue ended up being challenging one. Thanks Again- Frank
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.