Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2010
    Posts
    5

    Unanswered: Open one access Db From anther Access DB

    I am new to Access. I have project.I need some help to get started.
    I have 14 Access db Linked to SharePoint. I need to link them back to a central Database. Since the tables are linked to SharePoint I can not Link them back to the Central Db. I set up a query to make a new table it is an exact duplicate of the linked table to SharePoint then linked those table back the central db.

    I am Trying to do the following to make sure the hub is uptodate on the open of the DB
    1 Open the 14 dbs from the existing DB open with out closing
    2 Run the Query for the make table
    3 Close the Db
    4 Repet for the remaing 13 db

    I would look to do it with a loop to do each db one at a time.

    Here is what I have got it does not work access keep telling me it can not find the or the db is be used by someone else
    Code:
    Private Sub UpdateTables()
    Dim DBTOUPDATE(0 To 1) As String
    Dim Querytorun(0 To 1) As String
    Dim UPDATEDB As Access.Application
    Set UPDATEDB = CreateObject("Access.Application")
    UPDATEDB.Visible = True
    
    Dim i As Long
    
    'List of the DB names for the Loop
    DBTOUPDATE(0) = "TestDB1.accdb"
    DBTOUPDATE(1) = "TestDB2.accdb"
    'List of the Query Names for the Loop
    Querytorun(0) = "01-01_DB1_Link_to_the_Hub"
    Querytorun(1) = "01-01_DB2_Link_to_the_Hub"
    
    For i = LBound(DBTOUPDATE) To UBound(DBTOUPDATE)
       UPDATEDB.OpenCurrentDatabase ("C:\Documents and Settings\James\Desktop\AccessDB Project\DBTOUPDATE(i)")
         UPDATEDB.DoCmd.OpenQuery "Querytorun(i)"
         UPDATEDB.CloseCurrentDatabase
        Next i
    End Sub
    Any help would be appreciated.

    Thanks
    James

  2. #2
    Join Date
    Jul 2010
    Posts
    5
    I played with is some more and I got it to work. Now just to apply it to the 14

    Code:
    Private Sub UpdateTables()
    Dim DBTOUPDATE(0 To 1) As String
    Dim Querytorun(0 To 1) As String
    Dim i As Long
    
    'List of the DB names for the Loop
    DBTOUPDATE(0) = "TestDB1.accdb"
    DBTOUPDATE(1) = "TestDB2.accdb"
    'List of the Query Names for the Loop
    Querytorun(0) = "01-01_DB1_Link_to_the_Hub"
    Querytorun(1) = "01-01_DB2_Link_to_the_Hub"
    
    For i = LBound(DBTOUPDATE) To UBound(DBTOUPDATE)
        Dim UPDATEDB As Access.Application
        Set UPDATEDB = New Access.Application
            UPDATEDB.Visible = True
            UPDATEDB.OpenCurrentDatabase ("C:\Documents and Settings\James\Desktop\AccessDB Project\" & DBTOUPDATE(i) & "")
            UPDATEDB.DoCmd.OpenQuery "" & Querytorun(i) & ""
            UPDATEDB.CloseCurrentDatabase
        Set UPDATEDB = Nothing
    Next i
    End Sub

Posting Permissions

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