none
Loop through forms of another Access file from 1st Access file RRS feed

  • Question

  • I have an Access file (say db1) that has code to find another access file (say db2). I have code in db1 that can find db2. With that Ineed to loop through the forms in db2 and list their names, create and modified date for each form and enter that in a table in db1. I would use AllForms to do this but am having trouble setting up the db2 in the db1 code that CurrentProject can be used with db2 so that I can use AllForms. I realize that I can use currentdb and DAO but then the modified date would be the same as the create date where with CurrentProject I will get the real modified date.

    Thanks

    Monday, November 14, 2016 3:04 AM

Answers

  • Hi StuKH,

    use the code mentioned below.

    Option Compare Database
    Option Explicit
    
    Sub demo()
    Dim currdb As Dao.Database
    Set currdb = Application.CurrentDb
    Dim accapp As Access.Application
    
    Set accapp = New Access.Application
    
    accapp.OpenCurrentDatabase ("C:\Users\v-padee\Desktop\Database2.mdb")
    accapp.Visible = False
    
        Dim objAccObj As AccessObject
        Dim objTest As Object
        
        Set objTest = accapp.CurrentProject
        For Each objAccObj In objTest.AllForms
            Debug.Print objAccObj.Name & "|" & objAccObj.DateCreated & "|" & objAccObj.DateModified
            currdb.Execute "insert into Formdata values('" & objAccObj.Name & "','" & objAccObj.DateCreated & "','" & objAccObj.DateModified & "');"
        Next objAccObj
    
    End Sub
    

    currdb is my current database(Database-1).

    I open another database (Database-2) using accapp.

    then I fetch the data from database 2 and insert in database 1.

    output is mentioned below.

    Note: here for demo I insert the date as text but you can add #date# in query to insert it as date.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 15, 2016 7:32 AM
    Moderator

All replies

  • Hi StuKH,

    use the code mentioned below.

    Option Compare Database
    Option Explicit
    
    Sub demo()
    Dim currdb As Dao.Database
    Set currdb = Application.CurrentDb
    Dim accapp As Access.Application
    
    Set accapp = New Access.Application
    
    accapp.OpenCurrentDatabase ("C:\Users\v-padee\Desktop\Database2.mdb")
    accapp.Visible = False
    
        Dim objAccObj As AccessObject
        Dim objTest As Object
        
        Set objTest = accapp.CurrentProject
        For Each objAccObj In objTest.AllForms
            Debug.Print objAccObj.Name & "|" & objAccObj.DateCreated & "|" & objAccObj.DateModified
            currdb.Execute "insert into Formdata values('" & objAccObj.Name & "','" & objAccObj.DateCreated & "','" & objAccObj.DateModified & "');"
        Next objAccObj
    
    End Sub
    

    currdb is my current database(Database-1).

    I open another database (Database-2) using accapp.

    then I fetch the data from database 2 and insert in database 1.

    output is mentioned below.

    Note: here for demo I insert the date as text but you can add #date# in query to insert it as date.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 15, 2016 7:32 AM
    Moderator
  • Deepak,

    Thank you. I had tunnel vision and wasn't seeing the "Set objTest = accapp.CurrentProject" step.

    Worked great

    Saturday, November 19, 2016 7:55 PM
  • Nice one Deepak.

    MY BOOK

    Monday, November 21, 2016 7:17 PM