Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create Link (Access 2002)

    Hi

    This is a bit of an unusual question.

    I have a DB (called Check DB) that does some data integrity checking of data in tables (among other things). Rather than load the form and code from it into every database I thought I could do the following:

    a) In the Check DB use an open dialog box in a form to select the other database (normally the BE)
    [img]/forums/images/smilies/cool.gif[/img] Link all of these tables in the selected database to the Check DB.
    c) Run the checking programmes etc.
    d) Once finished delete all the linked tables from the Check DB.

    The problem is I can't figure out the code to link the tables from the selected database into the Check DB. Is this possible (and appropriate)? If so, I would appreciate some advice on the code required. The only code I can find is to either refresh existing links, or if creating a link, I have to input the table name rather than using code to grab the name from the other DB.

    Thanks & Regards

    WTH

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Create Link (Access 2002)

    In order to do what you suggest, you need to open the second database, and using DAO, examine the TableDefs collection to determine the table names. Once you have the table names, you can link to the tables using the code you apparently already have. If you haven't dug into these kind of things in the Access object model, I would suggest you look at a book "Professional Access 2000 Programming" by Martin, et. al. As an alternative, you might consider creating a library which each of your databases reference, and then place simple VBA calls from each database to the library. If this creates more questions for you, please post back.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Link (Access 2002)

    Hi

    Thanks for the fast reply.

    Yes good idea. Not sure whether my programming skills are up to it as the whole table defs area is new for me. However, I will give the DAO suggestion a go, but don't be surprised to get a follow up reply for help later in the week!

    Regards
    WTH

  4. #4
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Link (Access 2002)

    Hi

    This code seems to work ok, but would appreciate any comments on fishhooks in using it.

    Dim wrkJet As DAO.Workspace
    Dim db As DAO.Database

    Dim prpLoop As DAO.Property
    Dim Tbdef As DAO.TableDef

    Dim strDBName As String

    Dim strFilePath As String
    Dim strFullDBName As String
    Dim strTable As String
    Dim strBackEndDB As String
    ' Create Microsoft Jet Workspace object.
    Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)


    Set db = wrkJet.OpenDatabase("c:cons_nz2000.mdb", _
    True)

    MsgBox "Database properties for " & db.Name & ":"
    For Each Tbdef In db.TableDefs
    If Tbdef.Attributes = 0 Then
    strTable = Tbdef.Name
    strDBName = "c:cons_nz2000.mdb"
    DoCmd.TransferDatabase transfertype:=acLink, databasetype:="Microsoft Access", _
    databasename:=strDBName, ObjectType:=acTable, Source:=strTable, _
    destination:=strTable

    End If
    Next Tbdef


    db.Close
    wrkJet.Close


    All I have to do now is to develop the function to delete the linked tables. Hopefully, this should not be too difficult.

    Regards
    WTH

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

    Re: Create Link (Access 2002)

    What do you mean by TbDef.Attributes = 0? Testing attributes involve bitwise manipulation. For example, this tests for a linked table:

    <pre>If (tbDef.Attributes And dbAttachedTable) <> 0 Then</pre>


    You do not want to link system tables, I'm sure, so you will have to test for that also:

    <pre>If (Not tbDef.Attributes And Not dbSystemObject) Then</pre>

    Charlotte

  6. #6
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Link (Access 2002)

    One additional suggestion: It is unlikely that you will need to link to all the non-system tables that may exist in the DB. Therefore, I would either keep a local table of "linkable" table names, which I could scan against each located table name, or I'd post a message box with the located table name and ask the user for permission to link the table.
    -- Jim

  7. #7
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Link (Access 2002)

    Hi

    Thanks for all the input.

    The purpose of the TbDef.Attributes = 0 was to ensure I only linked non system tables. I based this on testing what attributes were assigned to all the tables. The results from this suggested to me that only the linked tables returned zero hence the code. However, the suggested code by Charlotte seems more appropriate. As I said earlier this is new area for me and I have appreciated all the feedback given.

    Regards
    WTH

Posting Permissions

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