Results 1 to 9 of 9
  1. #1
    Join Date
    May 2003
    Posts
    14

    Question Unanswered: Import Access Objects into Access using VB

    Hello,
    Thank you for your time.

    Is it possible using VB to import MS Access objects like: Forms, Tables etc. without knowing the objects names or types in the import source?

    I’ve used the Docmd.TransferDatabase but in the code it requests a name for the object to be imported and a name for the object once imported to the destination.

    I will know the Name of the Database and its location, but not the names or types of the objects. I would like it to import any objects the source database will contain without knowing what types of objects are there.

    Once imported into the destination, I would like it to overwrite any objects with the same name.

    For the imported objects with no conflict of names, once imported it would become part of the database.

    Again, Thank you for your time.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You have first to enumerate the objects present in the source mdb file, scanning its different collections (TableDefs, QueryDefs etc).

    You can then:
    a) delete any matching object in the destination file.
    b) import the objects present in the source file, as you now know their names and types.
    Have a nice day!

  3. #3
    Join Date
    May 2003
    Posts
    14

    Smile Import

    Thank you for your response and letting me know the direction i need to go. As far enumerating for scanning its different collections (TableDefs, QueryDefs etc).

    Could you give me an example of the code and then from there i can analyze and implement.

    Thanks Again.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here is a solution:
    Code:
    Option Compare Database
    Option Explicit
    
    Function EnumerateMDBObjects(SourceMDBFile As String)
    
        Const EvalString As String = "'$' IN ('Forms', 'Reports', 'Scripts', 'Modules')"
        Dim dbf As DAO.Database
        Dim tdf As DAO.TableDef
        Dim qdf As DAO.QueryDef
        Dim ctr As DAO.Container
        Dim doc As DAO.Document
        
    
        Set dbf = DBEngine.OpenDatabase(SourceMDBFile)
        For Each tdf In dbf.TableDefs
            If Left(tdf.Name, 4) <> "MSys" And Left(tdf.Name, 1) <> "~" Then
                Debug.Print tdf.Name
            End If
        Next
        For Each qdf In dbf.QueryDefs
            If Left(qdf.Name, 1) <> "~" Then
                Debug.Print qdf.Name
            End If
        Next
        For Each ctr In dbf.Containers
            If Eval(Replace(EvalString, "$", ctr.Name)) Then
                For Each doc In ctr.Documents
                    Debug.Print doc.Name
                Next
            End If
        Next
        dbf.Close
        Set dbf = Nothing
    
    End Function
    
    Function Test()
    
        EnumerateMDBObjects "C:\Documents and Settings\Sinndho\My documents\Access\CF_Sit\CF_Sit_2\CF_Sit.mdb"
        
    End Function
    Have a nice day!

  5. #5
    Join Date
    May 2003
    Posts
    14

    Smile Import

    Thanks! I'll check it out and see what i can do. I Appreciate Ya!

  6. #6
    Join Date
    May 2003
    Posts
    14

    Import

    Hello,

    Wondering where should i place this code. I would like to place it on a command button. I noticed the "Option Compare Database Option Explicit
    Function EnumerateMDBObjects(SourceMDBFile As String)" Is this placed inside a module? And then call the module from a command button?

    Sorry, been using VB for a couple of years and i'm no way on your level. I see there's so much more to learn.

    This is the code with my modifications basically the path of the DB to be Enumerated, I stated the source DB location twice in the code.


    Option Compare Database
    Option Explicit

    Function EnumerateMDBObjects(SourceMDBFile As String)

    Const EvalString As String = "'$' IN ('Forms', 'Reports', 'Scripts', 'Modules')"
    Dim dbf As DAO.Database
    Dim tdf As DAO.TableDef
    Dim qdf As DAO.QueryDef
    Dim ctr As DAO.Container
    Dim doc As DAO.Document


    Set dbf = DBEngine.OpenDatabase("C:\SMS\Supplier Management System Back Up (1.1).mdb")
    For Each tdf In dbf.TableDefs
    If Left(tdf.Name, 4) <> "MSys" And Left(tdf.Name, 1) <> "~" Then
    Debug.Print tdf.Name
    End If
    Next
    For Each qdf In dbf.QueryDefs
    If Left(qdf.Name, 1) <> "~" Then
    Debug.Print qdf.Name
    End If
    Next
    For Each ctr In dbf.Containers
    If Eval(Replace(EvalString, "$", ctr.Name)) Then
    For Each doc In ctr.Documents
    Debug.Print doc.Name
    Next
    End If
    Next
    dbf.Close
    Set dbf = Nothing

    End Function

    Function Test()

    EnumerateMDBObjects "C:\SMS\Supplier Management System Back Up (1.1).mdb"

    End Function

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Yes you can place the code I posted in a module (you can delete the Test() function, it was just there to show you how to call the function EnumerateMDBObjects().

    It would be better not to hard-code the name of the mdb file in the function as you might want to re-use it to enumerate objects in another mdb file.

    To call the function form a command button on a form, use this code:

    Code:
    Private Sub Button1_Click()
        EnumerateMDBObjects "C:\SMS\Supplier Management System Back Up (1.1).mdb"
    End Sub
    In a real application you could store the enumerated objects into a table, then use the table contents to import the objects:

    Table DBObjects:
    ----------------
    ObjectName Text(240)
    ObjectType Text(6)

    Now the function becomes:
    Code:
    Function EnumerateMDBObjects(SourceMDBFile As String)
    
        Const EvalString As String = "'$' IN ('Forms', 'Reports', 'Scripts', 'Modules')"
        Dim dbf As DAO.Database
        Dim tdf As DAO.TableDef
        Dim qdf As DAO.QueryDef
        Dim ctr As DAO.Container
        Dim doc As DAO.Document
        Dim strSQL As String
        
        strSQL = "DELETE * FROM DBObjects"
        CurrentDb.Execute strSQL
        Set dbf = DBEngine.OpenDatabase(SourceMDBFile)
        For Each tdf In dbf.TableDefs
            If Left(tdf.Name, 4) <> "MSys" And Left(tdf.Name, 1) <> "~" Then
    '            Debug.Print tdf.Name
                strSQL = "INSERT INTO DBObjects ( ObjectName, ObjectType ) VALUES ( '" & tdf.Name & "', 'Table' )"
                CurrentDb.Execute strSQL
            End If
        Next
        For Each qdf In dbf.QueryDefs
            If Left(qdf.Name, 1) <> "~" Then
    '            Debug.Print qdf.Name
                strSQL = "INSERT INTO DBObjects ( ObjectName, ObjectType ) VALUES ( '" & qdf.Name & "', 'Query' )"
                CurrentDb.Execute strSQL
            End If
        Next
        For Each ctr In dbf.Containers
            If Eval(Replace(EvalString, "$", ctr.Name)) Then
                For Each doc In ctr.Documents
    '                Debug.Print doc.Name
                strSQL = "INSERT INTO DBObjects ( ObjectName, ObjectType ) VALUES ( '" & doc.Name & "', '" & Replace(ctr.Name, "Scripts", "Macro") & "' )"
                CurrentDb.Execute strSQL
                Next
            End If
        Next
        dbf.Close
        Set dbf = Nothing
    
    End Function
    After calling the function, the table DBObjects contains the name and the type of all objects (Tables, Queries, Forms, Reports, Macros, Modules) that are present in the source mdb file. If you want to enumerate other types of objects you can modify the EvalString constant. The objects you can enumerate from the Containers collection are: DataAccessPages, Databases, Forms, Modules, Relationships, Reports, Scripts (=Macros), SysRel and Tables. The Tables container includes both Tables and Queries objects, this is why I prefer to enumerate them through the TableDefs and QueryDefs collections.

    From there you can use the Application.DoCmd.CopyObject method to import the objects from the source mdb file into the current database or into another mdb file.
    Have a nice day!

  8. #8
    Join Date
    May 2003
    Posts
    14

    Smile Import

    You Are The Man! I'll review and see what i can work. Will let you know how things go tomorrow.

    Thanks Again!

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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