link tables with vba and .mdw file

Posted on 2009-04-22
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 56

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    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 different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    761 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

    8 Experts available now in Live!

    Get 1:1 Help Now