Solved

ODBC Connection Access-SQL Server

Posted on 2001-08-02
11
750 Views
Last Modified: 2007-12-19
I've just converted an entire Access application to run from a SQL Server backend.  While I've eliminated the need for local tables by using stored procedures, I've found I still need linked tables to run reports.  

The Access database is in a shared folder, and it's tables are only accessible by the client where the linked tables were actually linked.  Any other user who trys to open the tables will receive an ODBC call failed error.  

How can I link the tables so that they are accessible by all users?
0
Comment
Question by:miteekwin
11 Comments
 
LVL 1

Expert Comment

by:TheNextStep
ID: 6346958
You have to add the driver to every computer that accesses the database.  Then you have to establish the ODBC connection at each computer as well (My Computer/ODBC Connections/...).  Kind of arduous, but necessary.
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6348543
here's a programmatic solution:

one of the things i do is create a DSN programmatically so the user never has to do this type of work themself. what i like to do is when the app starts, in the autoexec macro, or the first form that loads i put the following code:

Function AddODBCDataSource() As Long
Dim lngReturn  As Long
lngReturn = acsSQLConfigDataSource(0&, _
   1, _
   "SQL Server", _
   "DSN=[DSN Name]" & vbNullChar & _
   "Server=[ServerName/IP Address]" & vbNullChar & _
   "Database=ASLIMS" & vbNullChar & _
   "Network=DBMSSOCN" & vbNullChar & _
   "OEMtoANSI=No" & vbNullChar & _
   vbNullChar)
AddODBCDataSource = lngReturn
End Function

you also need to create (and save) a new module with the following API call declaration:

' DLL function declarations for adding odbc source
Declare Function acsSQLConfigDataSource _
   Lib "odbccp32.dll" Alias "SQLConfigDataSource" _
   (ByVal hWndParent As Long, ByVal lngRequest As Long, _
   ByVal rstrDriver As String, ByVal rstrAttributes As String) As Boolean


this will re-create the DSN every time the user opens the application. you could add some code that checks to see if the DSN is added, but personally i think that overwriting the DSN every time is acceptable.  (i also delete the DSN when the app closes).

using this API call, you won't have to force your users (or you) to create a DSN on every pc.  i find this to be very handy. also, i use sql authentication and TCP/IP transport. this short method will set these for you.

hope this is helpful.

dovholuk
0
 
LVL 6

Expert Comment

by:devtha
ID: 6348700
This is an accepted solution by one of the experts..
Try it..
http://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=20159618

0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6348754
ha ha ha... "by one of the experts.."??? don't you mean "by me"???

lol

dovholuk
0
 
LVL 6

Expert Comment

by:devtha
ID: 6348767
No K-9
duvh ~ duh
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 6

Expert Comment

by:devtha
ID: 6348771
The solution was provided by me and accepted by K-9.
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6348885
duh eh?

the way i read your statement, "the accepted solution by one of the experts" is easily mistaken for what you intened.  alternatively, i'd have written "the solution, accepted by one of the experts". this is less ambiguous.

also, why don't you post your answer. if miteekwin isn't on KPRO s/he'll have to purchase the PAQ.

cheers,

dovholuk
0
 
LVL 6

Expert Comment

by:devtha
ID: 6348894
You should be a moderator for a english sentences construction...


Here is the code from a form that has 2 text boxes and 2 cmd butons. UID, PWD, OK and CANCEL.


'gconnect is a global variable that is set to  
'the following value in a module.
Global Const gcConnect = "ODBC;DSN=DSN_NAME;SERVER=MAINSPISSQL;;APP=Microsoft? Access;DATABASE=MASTER;Network=DBMSSOCN;Address=sqlServer,1433;AnsiNPW=No"

Global gStrPWD$

This works for me.....



Sub OKButton_Click()
On Error GoTo Err_OKButton_Click
   Dim MyWS As Workspace, mydb As Database, ConnStr As String

   
   DoCmd.Hourglass True
 
   If IsNull(Me![txtUID]) Then
       DoCmd.Hourglass False
       MsgBox "Please enter UID", vbExclamation, "Login to SQL Server"
       Me![txtUID].SetFocus
       Exit Sub
   End If


   If IsNull(Me![Password]) Then
       DoCmd.Hourglass False
       MsgBox "Please enter your password to SQL Server", vbExclamation, "Login to SQL Server"
       Me![Password].SetFocus
       Exit Sub
   End If


   
   ConnStr = gcConnect
   ConnStr = ConnStr & ";UID=" & Me![txtUID] & ";"
   ConnStr = ConnStr & "PWD=" & Me![Password] & ";"
 
   'global variable
   gStrPWD = Me![Password]
   
   Set MyWS = DBEngine.Workspaces(0)
   Set mydb = MyWS.OpenDatabase("", False, False, ConnStr)
   mydb.Close
   
   DoCmd.OpenForm "SwitchBoard"
   DoCmd.Close acForm, "SQL Login"

Exit_OKButton_Click:
   DoCmd.Hourglass False
   DoCmd.SetWarnings True
   Exit Sub

Err_OKButton_Click:
   DoCmd.Hourglass False
   MsgBox Error$
   Resume Exit_OKButton_Click
End Sub
0
 
LVL 6

Expert Comment

by:devtha
ID: 6357214
???????????????????????????????????????????
0
 

Author Comment

by:miteekwin
ID: 6357749
I tried the first method prescribed above by dovhulok, and while the code runs without any compile problems or errors. It doesn't allow a linked table to be opened (i.e. ODBC Call Failed error)

Trying the second method...
0
 

Accepted Solution

by:
TimothyCase earned 300 total points
ID: 6365965
Okay the solution to the problem is as follows:

1.  Linked tables through ODBC cannot be shared.  A copy of the shared application must be copied locally to each client.

2. The following code will open at the start of the database and check if the local copy of the client's application has a valid connection with the ODBC datasource.  If it is not a valid connection, then tables are relinked with the Client's ODBC connection on the LOCAL copy of the database.

Part of this solution is from Wrox Press Access 2000 Programming.

(NOTE: This solution requires hard-coding of a valid SQL Server login and password, which may not be acceptable because of security issues)

Private Sub Form_Load()
'Setup Error Handling
On Error GoTo Err_Load

'Declare variable for Connection Test
Dim varConnect As Variant

'Use DLookup to attempt to find values in a linked table
strTrial = DLookup("[CustomerID]", "dbo_tblCustomer", "[CustomerID]=1")

'If links okay then open the switchboard
DoCmd.Open "frmSwitchboard

On Error GoTo 0
Exit Sub

'If DLookup causes an error then this code will fire off a procedure to relink the tables
Err_Load:
     Call TableRelink  
End Sub

Private Sub TableRelink()

Dim tbl As TableDef

For Each tbl In CurrentDb.TableDefs
    'Loop Through all Tables and change connect string
     If tbl.Connect <> "" Then
             tbl.Connect= "ODBC;Database=ProductionData; _
             UID=JOEUSER;PWD=JOEPASS;DSN=ODBCTest"
             tbl.RefreshLink
     End If
Next
   
CurrentDb.TableDefs.Refresh
   
End Sub
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

896 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

18 Experts available now in Live!

Get 1:1 Help Now