link tables with vba and .mdw file

Posted on 2009-04-22
Medium Priority
Last Modified: 2013-11-27
i have the following code which DOES establish a connection to a db that is secured with a .MDW file (TS_MASTER.mdb).  It also DOES link a table (TS_Public) from the secured DB into a NON- secured DB (ben.mdb) called the same the table name.

BUT when try to open this table, i still get the error message (see image).  What am I missing in my code to bypass this error message?  Is there a place in the code where I am missing a place to pass credentials?
Public Function LinkTables() As Integer
Dim myConnStr As String
Dim strDbName As String
Dim Cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
strDbName = "C:\Documents and Settings\zadrogas\Desktop\Ben\ben.mdb"
psTable = "TS_PUBLIC"
sPath = "C:\Documents and Settings\zadrogas\Desktop\GSIC-TST Reporting\RPM Master Data\TS_MASTER.mdb"
'connect to the target database
    Set Cnn = New ADODB.Connection
    With Cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Data Source") = strDbName
        .Properties("User Id") = "myUsername"
        .Properties("Password") = ""
        .Properties("Jet OLEDB:System database") = "C:\Documents and Settings\zadrogas\Desktop\GSIC-TST Reporting\RPM Master Data\security.mdw"
        .Properties("Jet OLEDB:Database Locking Mode") = 0
   End With
    Set cat = New ADOX.Catalog
    Set cat.ActiveConnection = Cnn
        'link table
    Set tbl = New ADOX.Table
    With tbl
         .Name = psTable
         Set .ParentCatalog = cat
         .Properties("Jet OLEDB:Create Link") = True
         .Properties("Jet OLEDB:Link Datasource") = sPath
         .Properties("Jet OLEDB:Remote Table Name") = psTable
         On Error Resume Next
         cat.Tables.Delete psTable
         On Error GoTo 0
         cat.Tables.Append tbl
    End With
    Set tbl = Nothing
    'release references
    Set Cnn = Nothing
    Set cat = Nothing
End Function

Open in new window

Question by:szadroga
1 Comment
LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 24208002
Your not going to be able to do that.  Linked tables work only within the default workspace, not the workspace you defined on the connection.
The only way you can work with a secured BE that was secured with a workgroup other then the default of the current database is to:
A.  only work with the data in code via another workspace.
B. By adding the a group with the same name and PID to both workgroups and having all users as part of that group.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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.
Suggested Courses

864 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