[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Setting up Linked Tables to not need ODBC connection setup

Posted on 2012-09-05
15
Medium Priority
?
474 Views
Last Modified: 2012-09-06
I have a script that I run to remap all my linked tables to use a different connection string. Normally I use trusted Source, but on this database I want to setup a username and password, but I can't get it to work. Can someone help me?


Private Sub Command0_Click()
' Run this subroutine in the Immediate Window to connect to SQL Server so that users don't
' need an ODBC DSN registered on their PC. Change server/database names if you copy this code.
    On Error Resume Next
    Dim cnstr As String
    cnstr = "Driver={SQL Server};Server=db1a;Database=GCT;Uid=username;Pwd=pass"
    Dim tbl
    For Each tbl In CurrentDb.TableDefs
        If tbl.Connect <> "" Then
            tbl.Connect = cnstr
            tbl.RefreshLink
            CurrentDb.TableDefs.Refresh
        End If
        DoEvents
   Next
   If Err Then MsgBox "Some or all database tables were not updated. Please contact your system administrator": Err = 0
   Debug.Print "Done"

End Sub
0
Comment
Question by:NickMalloy
  • 7
  • 7
15 Comments
 
LVL 14

Expert Comment

by:pteranodon72
ID: 38369100
NickMalloy,
You don't give the results other than "doesn't work", but you've also got all errors suppressed with On Error Resume Next

I'm suspicious of the line:
For Each tbl In CurrentDb.TableDefs

Try instead to create a database instance, set it to CurrentDb, then use its .TableDefs collection:
 Dim cnstr As String
    cnstr = "Driver={SQL Server};Server=db1a;Database=GCT;Uid=username;Pwd=pass"
    Dim db As Database
    Dim tbl As TableDef

    Set db = CurrentDb
    For Each tbl In db.TableDefs
        If tbl.Connect <> "" Then
            tbl.Connect = cnstr
            tbl.RefreshLink
            ''CurrentDb.TableDefs.Refresh You don't want to refresh all tabledefs after each one changes!
        End If
        DoEvents
   Next
'refresh all tabledefs outside of loop   
db.TableDefs.Refresh
Set db = Nothing

Open in new window


This requires a valid reference to DAO. Before deployment, you can change to:
    Dim db As Object 'Database
    Dim tbl As Object 'TableDef

pT72
0
 

Author Comment

by:NickMalloy
ID: 38369400
Did not work. The code does not error out when I run it. The users get an error when they try to open it.

ODBC -- connection to '{SQL Server}db1a' failed.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38369521
Are you literally using:

cnstr = "Driver={SQL Server};Server=db1a;Database=GCT;Uid=username;Pwd=pass"

as your connection string?  Or have you replaced "username" and "pass" with the appropriate username and password?  Maybe you need something like:

cnstr = "Driver={SQL Server};Server=db1a;Database=GCT;" _
          & "Uid=" & me.txtUserName & ";Pwd=" & me.txtPassword

It's been a while since I did this, but SQL Server may require the userID and Password to be delimited as text as well:

cnstr = "Driver={SQL Server};Server=db1a;Database=GCT;" _
          & "Uid=" chr$(34) & me.txtUserName & chr$(34) _
          & ";Pwd=" & chr$(34) & me.txtPassword & chr$(34)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:NickMalloy
ID: 38369740
I am replacing server and user info with the correct things, but otherwise the format is the same. It works on my computer but I have an ODBC setup. it's on computers that do not.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38369811
Have you tried:

cnstr = "ODBC;Driver={SQL Server};Server=db1a;Database=GCT;Uid=username;Pwd=pass"
0
 

Author Comment

by:NickMalloy
ID: 38370043
tried that and I still get the error
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38370460
Can you copy and post the current code you are using?  Would like to make sure what the most recently tested code looks like.

Do you already have one or more of the tables linked?

If so, in the Immediate window type

Set db = currentdb {Enter}
Set tdf = db.tabledefs("LinkedTableName") {Enter}
?tdf.connect {Enter}
?tdf.sourcetable {Enter}
set tdf = nothing {Enter}
set db = nothing {Enter}

Don't type {Enter}, hit the Enter button.  <grin>

Then paste the results of the print lines here.
0
 

Author Comment

by:NickMalloy
ID: 38372630
this is what I have so far. db, userid, and password have been changed to protect.


Private Sub Command0_Click()
' Run this subroutine in the Immediate Window to connect to SQL Server so that users don't
' need an ODBC DSN registered on their PC. Change server/database names if you copy this code.
    On Error Resume Next
    Dim cnstr As String
    cnstr = "ODBC;Driver={SQL Server};Server=server1;Database=db;Uid=userID;Pwd=Password"
    Dim db As Object 'Database
    Dim tbl As Object 'TableDef
    Set db = CurrentDb
    For Each tbl In db.TableDefs
        If tbl.Connect <> "" Then
            tbl.Connect = cnstr
            tbl.RefreshLink
            ''CurrentDb.TableDefs.Refresh You don't want to refresh all tabledefs after each one changes!
        End If
        DoEvents
   Next
  ' refresh all tabledefs outside of loop
   db.TableDefs.Refresh
   Set db = Nothing
   If Err Then MsgBox "Some or all database tables were not updated. Please contact your system administrator": Err = 0
   Debug.Print "Done"

End Sub

Here is what I get in the immediate window.

set db = currentdb
set tdf = db.tabledefs("dbo_tblRequest")
?tdf.connect
ODBC;Driver={SQL Server};Server=server1;Database=db;
?tdf.sourcetable (Produced this error. "Run-Time error '438': Object doesn't support this property or method.")
set tdf = nothing
set db = nothing
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38372848
sorry, that should have read:

?tdf.sourcetablename
0
 

Author Comment

by:NickMalloy
ID: 38372881
Updated.

set db = currentdb
set tdf = db.tabledefs("dbo_tblRequest")
?tdf.connect
ODBC;Driver={SQL Server};Server=Server1;Database=db;
?tdf.sourcetablename
dbo.tblRequest
set tdf = nothing
set db = nothing
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38372946
OK, that seems to work, now try:

set db = currentdb
set tdf = db.tabledefs("dbo_tblRequest")
tdf.connect = "ODBC;Driver={SQL Server};Server=Server1;Database=db;Uid=userID;Pwd=Password"
tdf.refreshlink
set tdf = nothing
set db = nothing

of course replacing the Server, Database, UserID and Password values as appropriate.

Did you get the error message?

I'm wondering whether you are getting the error message during the first pass through the loop in your code, or whether it is occuring at a later point.  It may be that the particular userId you are trying to update the links for does not have permissions for one or more of the tables.  Try inserting a debug.print statement into the loop, something like:

        If tbl.Connect <> "" Then
            debug.print "TableName: " & tbl.Name
            tbl.Connect = cnstr
            tbl.RefreshLink
        End If
0
 

Author Comment

by:NickMalloy
ID: 38373274
I did both of your request and received no error messages either time. This is the print out when I put the debug statement in.

TableName: dbo_tblMedicalRequest
TableName: dbo_tblMedicalholder
TableName: dbo_tblRequest
Done
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38373296
Are those the only three tables which should be linked to your SQL datasource?

If so, it looks like adding in the "ODBC;" to the SQL connection string resolved the problem.
0
 

Author Comment

by:NickMalloy
ID: 38373345
Does a user have to have access to the db in order to access? I thought entering a username and password would solve that issue??? If so, anyway around it????

My users error is as follows

Connection failed"
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC Sql Server Driver][SQL Server]Login failed for user 'username of user'.
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 1600 total points
ID: 38373391
Yes,

If you don't use a generic userid/password, then you need to assign permissions to the SQL database and to the individual tables and queries within the database.

I would post a new question with a new subject (maybe something like 'Assigning SQL Server database and object permissions').  Make sure to post to both the Access and SQL Server topic areas.  

It's been a while since I have used SQL Server, but I'm sure you can ask for a TSQL or Access script for granting an individual permissions to the database and the objects within the database.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

872 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