Improve company productivity with a Business Account.Sign Up

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

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
0
fsrebot
Asked:
fsrebot
  • 6
  • 4
  • 2
1 Solution
 
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
 
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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
 
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
 
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.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now