Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking tables after Unlink (A97/A2k)

    If I use the logic below to Unlink tables (delete them), I do not know how to relink the tables from the Back End. My objective is to easily "unlink" the Back end database, and either exit the Front End (for now) or connect to another Back End database (same data structure, different data from a different time period).

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef

    Set dbs = CurrentDb

    ' Loop through tabledefs.
    For Each tdf In CurrentDb.TableDefs
    ' If Connect property is not empty, table is linked.
    If Len(tdf.Connect) > 0 Then
    dbs.TableDefs.Delete tdf.Name
    End If
    Next tdf
    Set tdf = Nothing
    Set dbs = Nothing


    If the table definitions exist in the Front End, I use the following code:

    For Each tdf In dbs.TableDefs
    If tdf.Connect <> "" Then
    tdf.Connect = ";DATABASE=" & strDatabaseName & ";pwd=" & conPWD
    tdf.RefreshLink
    End If
    Next tdf

    But this does not work after the table "delete" command. So, how do I reconnect to my chosen Back End? I know what the 15 tables will be named. Does this help? I'm at a loss. I feel like I'm missing something easy, but I can't see it.

    TIA, Jerry

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

    Re: Linking tables after Unlink (A97/A2k)

    You need a list of tables to link and, if you are enforcing referential integrity, the order in which to link them. You could create a table in your front end that would contain the name of each table to be linked and any other information you might need, like the order in which to link them. Then you could use that list of tables to link and unlink back ends by getting a recordset of tablenames and working with that.
    Charlotte

  3. #3
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking tables after Unlink (A97/A2k)

    A few more questions.
    1. If the relationships are defined in the Back End, do I need to consider them in the Front End?
    2. Would the code be similar to what I already have, except I would refer to the <new table> instead of the TableDefs?

    '--------------------
    Dim rstRecords As Recordset, dbs As Database, qdfTemp As QueryDef, strSQL As String

    strSQL = "select * from tblTables

  4. #4
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking tables after Unlink (A97/A2k)

    I tried this code an got an error, "Invalid Operation". This occurs at the "RefreshLink" statement. And, none of the tables are created or relinked. What do I need to change to get this working?

    TIA, Jerry

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

    Re: Linking tables after Unlink (A97/A2k)

    You only need to use the Order field to control the order in which the tables are relinked, and even that may be overkill. We use a table like this for handling imports as well, and you have to be careful about the order in which you import data.

    Are you trying to relink tables or refresh the links? You unlink the tables in the current database, which means they are no longer in the tabledefs collection, so you can't loop through the collection to relink them. You have to use the tablename from a recordset to do that and create new links. RefreshLink doesn't relink tables that aren't there, and I thought that was what you were trying to do. Your refreshlink line is referring to a variable strDatabaseName, but I don't see that populated anywhere.

    In any case the correct syntax for the link is

    <code>tdf.Connect = ";DATABASE=" & strDatabaseName & ";TABLE=" & strTableName</code>

    Where strTableName is the name of the table to link.

    You can use <code>DoCmd.TransferDatabase acLink, , strDatabaseName, acTable, strTableName, strTableName </code> to create a new link, assuming the tables are in an Access backend.
    Charlotte

  6. #6
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking tables after Unlink (A97/A2k)

    OK. I have my computer back again, and I have more questions.

    You are right - I am trying to relink tables. Since I have only refreshed links before, I am at a loss about how to procede. Since my Back End database is password protected, I assume that the "DoCmd" statement will not work. Therefore, I am working with the Connect statement. The logic below is giving me the following error message,

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

    Re: Linking tables after Unlink (A97/A2k)

    What do you mean by password protected? Are you using Access security or password protection on the mdb file or on the VBA project? I can help with security but frankly I haven't ever bothered with password protection.

    Anyhow, try to understand that you don't *have* a tabledef object in your current database so you can't set its connect property. Instead, you will need to open the other database in code, passing the password as appropriate, and then set your tabledef in the current database = to the tabledef in the backend database, which will create a copy. Then you set the connect property and then refresh the link.

    When Access security is in place, you open a secured workspace by creating a workspace object and passing in the username and password. Then you open the database object on the workspace. I have no idea how you would do it for a file or project password.
    Charlotte

  8. #8
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking tables after Unlink (A97/A2k)

    The Back End database password was set by: "Tools" - "Security" - "Set Database Password". If my Front End has the table definitions in place, all I do is the following commands:
    ' Loop through tabledefs and refresh link to each table
    For Each tdf In dbs.TableDefs
    If tdf.Connect <> "" Then
    tdf.Connect = ";DATABASE=" & strDatabaseName & ";pwd=" & conPWD
    tdf.RefreshLink
    End If
    Next tdf

    My problem is after closing the Front End, the links to the Back End are still valid. This allows anyone free access to the data in the Back End (ignoring the database password) by importing files through the Front End. To avoid this issue, I created a blank database and link to it when the Front End is closed. However, this takes a lot of time. And for some users, the blank database is located where they can not access it. This leaves the data exposed. If I unlink the tables, this data security issue is solved. However, since I have never tried to create a link

Posting Permissions

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