Solved

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

Posted on 2008-06-12
12
1,446 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
  • 6
  • 4
  • 2
12 Comments
 
LVL 17

Expert Comment

by:dbaSQL
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 27

Expert Comment

by:MikeToole
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:fsrebot
Comment Utility
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
Comment Utility
<DSN=FGSmainEXPRESS>
I think that you need to supply the whole connect string rather than just the DSN name.
0
 

Author Comment

by:fsrebot
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now