Solved

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

Posted on 2008-06-12
12
1,467 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
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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…
Viewers will learn how the fundamental information of how to create a table.

776 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