Solved

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

Posted on 2008-06-12
12
1,553 Views
Last Modified: 2010-04-21
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
Comment
Question by:fsrebot
[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
  • 6
  • 4
  • 2
12 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 21778450
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
 

Author Comment

by:fsrebot
ID: 21778771
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
 
LVL 17

Expert Comment

by:dbaSQL
ID: 21779019
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 27

Expert Comment

by:MikeToole
ID: 21779620
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
 

Author Comment

by:fsrebot
ID: 21780867
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
 
LVL 27

Expert Comment

by:MikeToole
ID: 21781160
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
 

Author Comment

by:fsrebot
ID: 21782147
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
 
LVL 27

Expert Comment

by:MikeToole
ID: 21783542
<DSN=FGSmainEXPRESS>
I think that you need to supply the whole connect string rather than just the DSN name.
0
 

Author Comment

by:fsrebot
ID: 21784318
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
 
LVL 27

Accepted Solution

by:
MikeToole earned 250 total points
ID: 21784816
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
 

Author Comment

by:fsrebot
ID: 21787045
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
 

Author Closing Comment

by:fsrebot
ID: 31466590
I really appreciate the help. Something I thought that seemed to be a trivial issue ended up being challenging one. Thanks Again- Frank
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how the fundamental information of how to create a table.

622 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