Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    12

    Question Unanswered: Linking all tables in a seperate database

    I am trying to link all the tables in an external access database with the following code. The problem I get is an error on the append of "Could not find installable ISAM" Error '3170'. I have followed microsofts instructions on how to repair this error but to no success. Hass anyone ever run across this error before.

    Private Sub LinkDatabases_Click()
    Dim wrkSPACE As Workspace
    Dim FDDatabase As Database
    Dim LUDatabase As Database
    Dim dbs As Database
    Dim tblDefLink As tabledef

    Set wrkSPACE = CreateWorkspace("", "admin", "", dbUseJet)
    Set dbs = CurrentDB()

    strFilePath = Me!FDFileName.Caption
    Set FDDatabase = wrkSPACE.OpenDatabase(strFilePath, , True, "ACCESS")
    strFilePath = Me!LUFileName.Caption
    Set LUDatabase = wrkSPACE.OpenDatabase(strFilePath, , True, "ACCESS")

    For Each Database In wrkSPACE.Databases
    For Each tabledef In FDDatabase.TableDefs
    If Left(tabledef.NAME, 3) = "tbl" Then
    Debug.Print Database.NAME & " " & tabledef.NAME
    Set tblDefLink = dbs.CreateTableDef(Database.NAME)
    tblDefLink.Connect = "DATABASE=" & Database.NAME
    tblDefLink.SourceTableName = tabledef.NAME
    dbs.TableDefs.Append tblDefLink
    End If
    Next tabledef
    Next Database

    LUDatabase.Close
    FDDatabase.Close
    dbs.Close

    End Sub

    Thanks,

    Ryan Snow

  2. #2
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67

    Re: Linking all tables in a seperate database

    It looks like it should work but I am a little fuzzy on Workspace. Here is a method that links tables to the current database.

    Private Sub LinkDatabases_Click()

    Dim dbs As Database
    Dim LUDatabase As Database
    Dim TableDef As TableDef
    Dim strFilePath$

    Set dbs = CurrentDb()

    strFilePath = Me!FDFileName.Caption

    Set LUDatabase = DBEngine(0).OpenDatabase(strFilePath, , True)

    For Each TableDef In LUDatabase.TableDefs
    If Left(TableDef.Name, 3) = "tbl" Then
    DoCmd.TransferDatabase acLink, "Microsoft Access", strFilePath, acTable, TableDef.Name, TableDef.Name
    End If
    Next

    dbs.close
    LUDatabase.close
    set dbs = nothing
    set LUDatabase = nothing

    End Sub

  3. #3
    Join Date
    Oct 2002
    Posts
    12

    Thumbs up

    Thanks Robt917

    That fixed my problem. I use the workspace to play with multiple databases at the same time. That way i can pass a string of multiple databases, parse the string, and perform the same funtions on each database one at at time in order. This was just a test function to get the basic syntax. The code below took two database and linked all tables from both about 104 tables in about 15 seconds on my machine. Not to bad.

    I greatly appreciate your help on this one.


    Dim wrkSPACE As Workspace
    Dim FDDatabase As Database
    Dim LUDatabase As Database
    Dim dbs As Database
    Dim tblDefLink As TableDef

    Set wrkSPACE = CreateWorkspace("", "admin", "", dbUseJet)
    Set dbs = CurrentDB()

    strFilePath = Me!FullFDFileName.Caption
    Set FDDatabase = wrkSPACE.OpenDatabase(strFilePath, , True, "ACCESS")
    strFilePath = Me!FullLUFileName.Caption
    Set LUDatabase = wrkSPACE.OpenDatabase(strFilePath, , True, "ACCESS")

    For Each Database In wrkSPACE.Databases
    For Each TableDef In FDDatabase.TableDefs
    If Left(TableDef.NAME, 3) = "tbl" Then
    DoCmd.TransferDatabase acLink, "Microsoft Access", Database.NAME, acTable, TableDef.NAME, TableDef.NAME
    Debug.Print Database.NAME & " " & TableDef.NAME & " "

    End If
    Next TableDef
    Next Database

    LUDatabase.Close
    FDDatabase.Close
    dbs.Close

Posting Permissions

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