Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Relinking tables to an mdb with a password

    I have a system in which the code is in an FE and the data is in BE's. The data tables appear in the FE database window as links. The system will eventually be secured by making the FE an mde and the BE's will have a password.

    I have experienced random "spontaneous disappearances" of individual links. Therefore, when the FE starts, I want to use VBA to check that all the links are still there and relink them if they are missing.

    I have been able to make an ADO Connection to a BE that *has* a password.

    I have been able to use "DoCmd.TransferDatabase yada,yada..." to relink a table from a BE that does *not* have a password.

    How do I use VBA to relink a table in a mdb that has a password?

    TIA
    Donald

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Relinking tables to an mdb with a password

    If you already have a valid connection, you can use an ADO catalog to relink. However, it isn't as simple as DoCmd.TransferDatabase. Here's a routine that will get you started with an ADO relink if the back end is a Jet mdb:

    <pre>Public Function RelinkTableADO( _
    ByRef cnn as ADODB.Connection, _
    ByVal strTbl As String)
    'Created by Charlotte Foust 4/24/2001
    'relinks the specified table using the current settings

    Dim objTDF As ADOX.Table 'holds catalog table
    Dim cat As ADOX.Catalog 'holds catalog object
    Dim strSource As String 'holds path of linked database
    Dim strRemoteTbl As String 'holds name of remote table
    Dim strConnect As String 'holds connect string of _
    table

    Set cat = New ADOX.Catalog
    cat.ActiveConnection = cnn

    'initialize the table object and
    'store the current values
    Set objTDF = cat.Tables(strTbl)

    With objTDF
    'store the existing connect string
    strConnect = .Properties( _
    "Jet OLEDB:Link Provider String")

    'store the existing data source
    strSource = .Properties( _
    "Jet OLEDB:Link Datasource")

    'store the remote table name
    strRemoteTbl = .Properties( _
    "Jet OLEDB:Remote Table Name")
    End With 'objTDF

    'destroy the object variable
    Set objTDF = Nothing

    'delete the existing link
    cat.Tables.Delete strTbl

    'try to relink to the original data source
    Set objTDF = New ADOX.Table
    With objTDF
    ' give the Table its original name and
    ' set its ParentCatalog property to the
    ' open Catalog
    .name = strTbl
    Set .ParentCatalog = cat

    ' Set the properties to create the link.
    .Properties("Jet OLEDB:Create Link") = True

    'set the table's provider string
    .Properties("Jet OLEDB:Link Provider String") = _
    strConnect

    'set the linked table path and filename
    .Properties("Jet OLEDB:Link Datasource") = _
    strSource

    'set the remote table for the link
    .Properties("Jet OLEDB:Remote Table Name") = _
    strRemoteTbl
    End With 'objLink

    ' Append the table to the Tables collection.
    cat.Tables.Append objTDF
    End Function 'RelinkTableADO(ByVal strTbl As String)</pre>


    Obviously, I've left out error handling in order to save space. You would need to figure out how to pass the table name into the routine or else to loop through the AllTables collection of the catalog object and relink each table as necessary. And, of course, this routine doesn't deal with whether the back end is actually where you expect it to be.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relinking tables to an mdb with a password

    It took some tinkening to bend your code into what I needed, but it held the key that had been eluding me for many, many days (not to mention failed queries to other lists).

    Thanks a lot!
    Donald

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Relinking tables to an mdb with a password

    Glad it was of some use. I pulled it out of a larger routine that made me crazy until I figured it out, so I'm not surprised you needed to adapt it. ADO is tremendously useful, but it is NOT easy!
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •