Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Access ODBC disconnects

Posted on 2009-02-24
Medium Priority
Last Modified: 2013-11-29
   I have an Access database with linked tables to SQL Server 2005. I created it on my main XP Pro PC with Access 2003. This Access program will be shared with at least 20 more users on a network. I created an ODBC connection to link the database. I have tried to run the application on another PC with MY Windows login. The first time I would need to set up an ODBC and update the tables with the Link Manager in Access. Then I run a form with the code I found on this page http://www.accessmvp.com/djsteele/DSNLessLinks.html
It works fine. I close and reopen Access and the linked tables work fine, even on my main computer; works without having to update the linked tables.

    When I go to another user I have a hard time trying to set up the ODBC, and when I finally do the linked tables fail and I get an ODBC Call Failed message on different tables. When I close and reopen access none of the linked tables work and I have to re-link them to the ODBC.

    I am wondering if this could be a permissions issue, if so which one? I am a local administrator on my main PC. I would really appreciate any help on this matter. Thanks.
Question by:Papote
LVL 85
ID: 23720803
You might try increasing the ODBC timeout: Tools - Options - Advanced, you'll see a couple of settings you can change.

Author Comment

ID: 23721120
I am not getting a Timeout error. nevertheless I set it to 0. So far when I udpate the linked tales it works perfect. But when I close Access and reopen I get the ODBC Connection to server failed.
I can't have the user updating the tables. I even restricted them from viewing tables.
LVL 85
ID: 23721166
Do you immediately get this error? Do ANY Of the tables relink?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 85
ID: 23721173
What driver or Provider are you using? You may have an issue with that on the problem machine.

Author Comment

ID: 23721474
I get the error as son as I open the Access database.
Driver {sql server}

Author Comment

ID: 23721518
None of the tables have remained linked from the last update.
After I deleted all the linked tables and imported the linked tables on the user's PC I can still view them on my main PC without having to update at all.
LVL 18

Expert Comment

ID: 23733161
What kind of permissions does everyone have on the server?
LVL 38

Expert Comment

by:Jim P.
ID: 23750689
The code below is how to programatically add an ODBC call to a machine.

Have the first form that opens fire it on the On Open event.

This is using SQL Authentication.

Also when you link the tables in check mark the Sqve Password box.
Option Compare Database
Option Explicit
Option Base 1
Private Const REG_SZ = 1    'Constant for a string variable type.
Private Const HKEY_LOCAL_MACHINE = &H80000002
Private Declare Function RegCreateKey Lib "advapi32.dll" Alias _
   "RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, _
   phkResult As Long) As Long
Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias _
   "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
   ByVal reserved As Long, ByVal dwType As Long, lpData As Any, ByVal _
   cbData As Long) As Long
Private Declare Function RegCloseKey Lib "advapi32.dll" _
   (ByVal hKey As Long) As Long
Public Function Add_System_DSN()
'Open Þ Load Þ Resize Þ Activate Þ Current
Dim DataSourceName As String
Dim DatabaseName As String
Dim Description As String
Dim DriverPath As String
Dim DriverName As String
Dim LastUser As String
Dim Regional As String
Dim Server As String
Dim AddSetup As String
Dim lResult As Long
Dim hKeyHandle As Long
   'Specify the DSN parameters.
   DataSourceName = "DSNName"
   DatabaseName = "DBName"
   Description = "Descrption"
   DriverPath = "C:\WINDOWS\system32"
   LastUser = "sa"
   Server = "ServerName or IP"
   DriverName = "SQL Server"
   AddSetup = "No"
   'Create the new DSN key.
        DataSourceName, hKeyHandle)
   'Set the values of the new DSN key.
   lResult = RegSetValueEx(hKeyHandle, "QuotedId", 0&, REG_SZ, _
      ByVal AddSetup, Len(AddSetup))
   lResult = RegSetValueEx(hKeyHandle, "AnsiNPW", 0&, REG_SZ, _
      ByVal AddSetup, Len(AddSetup))
   lResult = RegSetValueEx(hKeyHandle, "AutoTranslate", 0&, REG_SZ, _
      ByVal AddSetup, Len(AddSetup))
   lResult = RegSetValueEx(hKeyHandle, "Database", 0&, REG_SZ, _
      ByVal DatabaseName, Len(DatabaseName))
   lResult = RegSetValueEx(hKeyHandle, "Description", 0&, REG_SZ, _
      ByVal Description, Len(Description))
   lResult = RegSetValueEx(hKeyHandle, "Driver", 0&, REG_SZ, _
      ByVal DriverPath, Len(DriverPath))
   lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, REG_SZ, _
      ByVal LastUser, Len(LastUser))
   lResult = RegSetValueEx(hKeyHandle, "Server", 0&, REG_SZ, _
      ByVal Server, Len(Server))
   'Close the new DSN key.
   lResult = RegCloseKey(hKeyHandle)
   'Open ODBC Data Sources key to list the new DSN in the ODBC Manager.
   'Specify the new value.
   'Close the key.
   lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
      "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
   lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, _
      ByVal DriverName, Len(DriverName))
   lResult = RegCloseKey(hKeyHandle)
End Function

Open in new window


Accepted Solution

Papote earned 0 total points
ID: 23751443
I for got to use the tdfCurrent.Attributes = DB_ATTACHSAVEPWD statement in my routine that I got from the site I mentioned before. I have elected to use username and password for the connection as I am already using a username and password on Access anyways.
I'll take a look at the code that jimpen posted.
LVL 85
ID: 24338660
How did you resolve this on your own? Can you post your resolution?

Author Comment

ID: 24338824
First of all, I left our the tdfCurrent.Attributes = DB_ATTACHSAVEPWD statement in my routine. Second, aperently the DBA had setup the group from Active Directory in the SQL Server wrong. We started over entering the user in Security\Logins and mapping the group to the database as db_datareader, and db_datarawriter.

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Implementing simple internal controls in the Microsoft Access application.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

578 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