Encountering difficulties trying to export/import VB components (1 Viewer)

[Papa_Bear1]

Hi,
I'm trying to build a solution that can pull the pieces out of any specified Access DB (e.g., SQL And VBA for version control code management etc.) and then also the ability to perform the reverse of that, creating an Access DB from those pieces as well (as much as possible.)

I'm encountering a surprising number of hurdles trying to do this. I expected it to be pretty much - cycle through the objects, export the code, then on the reverse, create the objects and populate the pieces with SQL or code. But no - each component/type seems to come with its own set of requirements, with one way to 'export' module code, but another way to 'export' query SQL etc. For example, it seems the *only* way to export VBA code is by establishing a reference to the external DB. This has caused a bit of grief for me in various ways - not the least of which being - when things go wrong - it never enables the "Debug" option. So I have to shut things down and start over - really slowing down development.

My current predicament is that I'm getting an error trying to use the following code to create a module in a newly created external DB:

prjTarget.VBComponents.Import strImportPathFile

I get
"Run-time error '5':
Invalid procedure call or argument

With ONLY the "End" and "Help" buttons enabled - so I cannot debug as usual.
Also, when I do any Reference coding - and try to walk in debug mode - it tells me I can't.

I then tried to achieve this - without "import" by manually creating the module and stuffing the code in it, like the following:

vbcProc.CodeModule.AddFromString strVBA

- but the code I had saved was an 'export' which had the header stuff in it. So, now I will have to strip out that text. Good grief.

So, I then decided to shift gears and see if I can do ALL of this just by using a database object (and not a reference) - but on the 'export' side of it, I've been hunting for the actual VBA itself in those objects and it seems the VBA is just not there. The module and its name and properties are all there - but not the VBA itself.

Very frustrating for this to need to be so circuitous. I must be missing something obvious.

I've seen references here about tools developed by members to provide exactly this capability (I think it was @isladogs ?), and I may end up trying that, but I really didn't want to embark on using someone else's code just yet. Part of this is learning, and I also have found that I can rarely use someone else's code as it never quite fits my requirements. But again - I may go down that road - once I've pulled out my remaining hair. Ha.

Is there a *consistent* way to cycle through Access objects for export/saving and import/restoring from/to an Access DB?

And btw - I'm only really trying to do this for SQL/code behind queries, reports, and forms, along with creation of DDL and data save/restore for a more 'base-level' reconstituting of the tables etc. I've been wondering where Access hides the actual form design info, but that is a bridge too far for me at this point - as I'm running into problems just trying to save/restore code. At some point, I suppose the back-ups of the Access files themselves serve the goal of restoration. But this exercise is meant to support interfacing with code management software - which is much better behaved with text files. So, I'm trying to see what I can do in that vein.

thanks for any help/ideas!

 

[RonPaii]

Hi,
I'm trying to build a solution that can pull the pieces out of any specified Access DB (e.g., SQL And VBA for version control code management etc.) and then also the ability to perform the reverse of that, creating an Access DB from those pieces as well (as much as possible.)

I'm encountering a surprising number of hurdles trying to do this. I expected it to be pretty much - cycle through the objects, export the code, then on the reverse, create the objects and populate the pieces with SQL or code. But no - each component/type seems to come with its own set of requirements, with one way to 'export' module code, but another way to 'export' query SQL etc. For example, it seems the *only* way to export VBA code is by establishing a reference to the external DB. This has caused a bit of grief for me in various ways - not the least of which being - when things go wrong - it never enables the "Debug" option. So I have to shut things down and start over - really slowing down development.

My current predicament is that I'm getting an error trying to use the following code to create a module in a newly created external DB:

prjTarget.VBComponents.Import strImportPathFile

I get
"Run-time error '5':
Invalid procedure call or argument

With ONLY the "End" and "Help" buttons enabled - so I cannot debug as usual.
Also, when I do any Reference coding - and try to walk in debug mode - it tells me I can't.

I then tried to achieve this - without "import" by manually creating the module and stuffing the code in it, like the following:

vbcProc.CodeModule.AddFromString strVBA

- but the code I had saved was an 'export' which had the header stuff in it. So, now I will have to strip out that text. Good grief.

So, I then decided to shift gears and see if I can do ALL of this just by using a database object (and not a reference) - but on the 'export' side of it, I've been hunting for the actual VBA itself in those objects and it seems the VBA is just not there. The module and its name and properties are all there - but not the VBA itself.

Very frustrating for this to need to be so circuitous. I must be missing something obvious.

I've seen references here about tools developed by members to provide exactly this capability (I think it was @isladogs ?), and I may end up trying that, but I really didn't want to embark on using someone else's code just yet. Part of this is learning, and I also have found that I can rarely use someone else's code as it never quite fits my requirements. But again - I may go down that road - once I've pulled out my remaining hair. Ha.

Is there a *consistent* way to cycle through Access objects for export/saving and import/restoring from/to an Access DB?

And btw - I'm only really trying to do this for SQL/code behind queries, reports, and forms, along with creation of DDL and data save/restore for a more 'base-level' reconstituting of the tables etc. I've been wondering where Access hides the actual form design info, but that is a bridge too far for me at this point - as I'm running into problems just trying to save/restore code. At some point, I suppose the back-ups of the Access files themselves serve the goal of restoration. But this exercise is meant to support interfacing with code management software - which is much better behaved with text files. So, I'm trying to see what I can do in that vein.

thanks for any help/ideas!

I use the joyfullservice add-in to do exactly what you describe. It is open source so you can look at the source code to see how they handle the problem. Note it exports the form/report and code to separate files.

See joyfullservice on github. https://github.com/joyfullservice/msaccess-vcs-addin

 

[arnelgp]

google SaveAsText/LoadFromText method of Application object.

 

[Papa_Bear1]

I use the joyfullservice add-in to do exactly what you describe. It is open source so you can look at the source code to see how they handle the problem. Note it exports the form/report and code to separate files.

See joyfullservice on github. https://github.com/joyfullservice/msaccess-vcs-addin

Excellent - will give that a look!

After just a quick initial look - it looks like I can't use that add-in, as it requires some things to be configured in a way that I cannot control. Bummer.

Also, again - just based on a quick look - but it sure looks like that since they've created an add-in to Access itself - the workflow for reconstitution may be done from within a new Access DB itself? If so, then they would not necessarily be addressing how to manipulate the objects in an external DB - although one would assume the method would be the same, just with an external database "dbExt." kind of object reference in front of it. So, I'll try to keep digging.

Thanks!

 

[Papa_Bear1]

google SaveAsText/LoadFromText method of Application object.

Also excellent. Will see what I can find.
Thanks!

 

[arnelgp]

export all objects:

Public Sub ExportAllObjects()

    Dim obj As AccessObject
    Dim dbPath As String
    
    dbPath = CurrentProject.Path & "\TextExport\"
    
    'Create folders if they don't exist
    CreateFolderIfMissing dbPath
    CreateFolderIfMissing dbPath & "Tables\"
    CreateFolderIfMissing dbPath & "Queries\"
    CreateFolderIfMissing dbPath & "Forms\"
    CreateFolderIfMissing dbPath & "Reports\"
    CreateFolderIfMissing dbPath & "Modules\"
    CreateFolderIfMissing dbPath & "Macros\"

    'Tables (skip system & temp tables)
    For Each obj In CurrentData.AllTables
        If Left(obj.Name, 4) <> "MSys" Then
            Application.SaveAsText acTable, obj.Name, dbPath & "Tables\" & obj.Name & ".txt"
        End If
    Next obj

    'Queries
    For Each obj In CurrentData.AllQueries
        Application.SaveAsText acQuery, obj.Name, dbPath & "Queries\" & obj.Name & ".txt"
    Next obj

    'Forms
    For Each obj In CurrentProject.AllForms
        Application.SaveAsText acForm, obj.Name, dbPath & "Forms\" & obj.Name & ".txt"
    Next obj

    'Reports
    For Each obj In CurrentProject.AllReports
        Application.SaveAsText acReport, obj.Name, dbPath & "Reports\" & obj.Name & ".txt"
    Next obj

    'Modules
    For Each obj In CurrentProject.AllModules
        Application.SaveAsText acModule, obj.Name, dbPath & "Modules\" & obj.Name & ".txt"
    Next obj

    'Macros
    For Each obj In CurrentProject.AllMacros
        Application.SaveAsText acMacro, obj.Name, dbPath & "Macros\" & obj.Name & ".txt"
    Next obj

    MsgBox "Export complete!"

End Sub


Private Sub CreateFolderIfMissing(folderPath As String)
    If Dir(folderPath, vbDirectory) = "" Then
        MkDir folderPath
    End If
End Sub

import all objects from .txt

Public Sub ImportAllObjects()

    Dim dbPath As String
    
    dbPath = CurrentProject.Path & "\TextExport\"

    ImportFolder acTable, dbPath & "Tables\"
    ImportFolder acQuery, dbPath & "Queries\"
    ImportFolder acForm, dbPath & "Forms\"
    ImportFolder acReport, dbPath & "Reports\"
    ImportFolder acModule, dbPath & "Modules\"
    ImportFolder acMacro, dbPath & "Macros\"

    MsgBox "Import complete!"

End Sub


Private Sub ImportFolder(objType As AcObjectType, folderPath As String)

    Dim fileName As String
    Dim objectName As String
    
    fileName = Dir(folderPath & "*.txt")
    
    Do While fileName <> ""
    
        objectName = Left(fileName, Len(fileName) - 4)
        
        'Delete existing object (avoid duplicate error)
        On Error Resume Next
        DoCmd.DeleteObject objType, objectName
        On Error GoTo 0
        
        Application.LoadFromText objType, objectName, folderPath & fileName
        
        fileName = Dir
    Loop

End Sub

 

[Papa_Bear1]

export all objects:

Public Sub ExportAllObjects()

    Dim obj As AccessObject
    Dim dbPath As String
   
    dbPath = CurrentProject.Path & "\TextExport\"
   
    'Create folders if they don't exist
    CreateFolderIfMissing dbPath
    CreateFolderIfMissing dbPath & "Tables\"
    CreateFolderIfMissing dbPath & "Queries\"
    CreateFolderIfMissing dbPath & "Forms\"
    CreateFolderIfMissing dbPath & "Reports\"
    CreateFolderIfMissing dbPath & "Modules\"
    CreateFolderIfMissing dbPath & "Macros\"

    'Tables (skip system & temp tables)
    For Each obj In CurrentData.AllTables
        If Left(obj.Name, 4) <> "MSys" Then
            Application.SaveAsText acTable, obj.Name, dbPath & "Tables\" & obj.Name & ".txt"
        End If
    Next obj

    'Queries
    For Each obj In CurrentData.AllQueries
        Application.SaveAsText acQuery, obj.Name, dbPath & "Queries\" & obj.Name & ".txt"
    Next obj

    'Forms
    For Each obj In CurrentProject.AllForms
        Application.SaveAsText acForm, obj.Name, dbPath & "Forms\" & obj.Name & ".txt"
    Next obj

    'Reports
    For Each obj In CurrentProject.AllReports
        Application.SaveAsText acReport, obj.Name, dbPath & "Reports\" & obj.Name & ".txt"
    Next obj

    'Modules
    For Each obj In CurrentProject.AllModules
        Application.SaveAsText acModule, obj.Name, dbPath & "Modules\" & obj.Name & ".txt"
    Next obj

    'Macros
    For Each obj In CurrentProject.AllMacros
        Application.SaveAsText acMacro, obj.Name, dbPath & "Macros\" & obj.Name & ".txt"
    Next obj

    MsgBox "Export complete!"

End Sub


Private Sub CreateFolderIfMissing(folderPath As String)
    If Dir(folderPath, vbDirectory) = "" Then
        MkDir folderPath
    End If
End Sub

import all objects from .txt

Public Sub ImportAllObjects()

    Dim dbPath As String
   
    dbPath = CurrentProject.Path & "\TextExport\"

    ImportFolder acTable, dbPath & "Tables\"
    ImportFolder acQuery, dbPath & "Queries\"
    ImportFolder acForm, dbPath & "Forms\"
    ImportFolder acReport, dbPath & "Reports\"
    ImportFolder acModule, dbPath & "Modules\"
    ImportFolder acMacro, dbPath & "Macros\"

    MsgBox "Import complete!"

End Sub


Private Sub ImportFolder(objType As AcObjectType, folderPath As String)

    Dim fileName As String
    Dim objectName As String
   
    fileName = Dir(folderPath & "*.txt")
   
    Do While fileName <> ""
   
        objectName = Left(fileName, Len(fileName) - 4)
       
        'Delete existing object (avoid duplicate error)
        On Error Resume Next
        DoCmd.DeleteObject objType, objectName
        On Error GoTo 0
       
        Application.LoadFromText objType, objectName, folderPath & fileName
       
        fileName = Dir
    Loop

End Sub

Well!
That is how I thought it should be!
(For some reason - when I first explored this approach - I thought I only found some of the object types but not all.
I'm quite curious what is within each .txt file, so I'm anxious to give this a whirl.
Thanks!!

 

[MajP]

FYI the SaveAsText loadFromText is one of the best, and at times the only method to fix database corruption caused by corrupt objects (not at the table level). Keep that code in your toolkit.

 

[Papa_Bear1]

Well!
That is how I thought it should be!
(For some reason - when I first explored this approach - I thought I only found some of the object types but not all.
I'm quite curious what is within each .txt file, so I'm anxious to give this a whirl.
Thanks!!

So - upon trying to use this approach - but tacking on the idea that the "application" is not local, but an external database, I'm getting:

Run-time error '2847':
The Object Type argument for the action or method is blank or invalid.

This doesn't make any sense to me since the Object Type argument is "acTable", for which there should be no confusion.

My extension to the command looks something like this:

    'Establish connection to the external DB/file.
    Set appExt = CreateObject("Access.Application")
    appExt.OpenCurrentDatabase (strTargetDBPathFile)
...
Create folder stuff
...
    'Tables (skip system & temp tables)
    For Each obj In appExt.CurrentData.AllTables
        If Left(obj.Name, 4) <> "MSys" Then
            'Application.SaveAsText acTable, obj.Name, strSavedFilesPath & "Tables\" & obj.Name & ".txt"
            appExt.SaveAsText acTable, obj.Name, strSavedFilesPath & "Tables\" & obj.Name & ".txt"
        End If
    Next obj

You can see I just tried to swap out "Application" with my external application reference.

Can this not be done in an external system?

 

[Papa_Bear1]

A couple other thoughts:
> It seems like if this were to be leveraged to repair a damaged/corrupted file - then it would HAVE to be done remotely/externally, unless you could at least open the corrupted file and insert this code etc.
> From what I've been able to find, there are really just two ways to make the connection to an external DB: 1) Use this approach to 'Open' it - which I really don't want to do, and 2) Set a Reference to it, which has its other issues that I've already mentioned.

 

[Imb]

Hi,
I'm trying to build a solution that can pull the pieces out of any specified Access DB (e.g., SQL And VBA for version control code management etc.) and then also the ability to perform the reverse of that, creating an Access DB from those pieces as well (as much as possible.)

The problem with forms is that the controls are bound to the fields in the table. But a control is much more than just to store data, it should reflect how the user wants the control to behave, e.g. with BeforeUPdate and AfterUpdate events.
This makes the form very static, and not sharable between applications.

With "Late Control Binding" you can alter this behaviour. This means that when you Enter a control, a reference is made to a meta data record, that contains ALL information about the control, independant on which form this "control" is placed, including how to bind.
The form itself has a number of hidden form-controls, that are tuned dependant on the item/entity, and thus can be reused for any table/query. The "tuning" is with processes that can be shared in a linked library database, and thus available in all applications.
With this approach I have automatically build over 100 applications, just by filling the metadata tables.

 

[Papa_Bear1]

A couple other thoughts:
> It seems like if this were to be leveraged to repair a damaged/corrupted file - then it would HAVE to be done remotely/externally, unless you could at least open the corrupted file and insert this code etc.
> From what I've been able to find, there are really just two ways to make the connection to an external DB: 1) Use this approach to 'Open' it - which I really don't want to do, and 2) Set a Reference to it, which has its other issues that I've already mentioned.

OK --- Did some more hunting...
Found this discussion...
https://www.tek-tips.com/threads/exporting-an-access-form-to-a-text-file-vb-file.989746/

And it turns out - someone else happened to notice that this approach worked for everything *except* tables, for them.

So, after commenting out the tables section in my code, it worked GREAT!

Not sure why this would work for some folks and not others (for tables) - but - tables are the least of my concern here as I already have code that documents them, creating the DDL etc. It would have been very clean to not have to handle one component differently, but this handles the tough stuff (like all aspects of forms, reports etc.), so -- no worries!

Therefore, I think this is resolved, and I REALLY appreciate the ability to transform all aspects of these objects into text --- so cool!!
(I'm quite intrigued by all the spaghetti that is behind each query. Wow.)

Thanks EVERYONE for the help on this one!

 

[isladogs]

SaveAsText / LoadFromText are indeed very powerful tools for exporting / importing all objects EXCEPT tables.
However, neither can be used for tables (apart from one little known exception).

See my article for more details and an alternative solution for tables.

Save As Text / Load From Text

This article outlines the use of two very useful but hidden and undocumented items in Access. SaveAsText is used to export database objects to text files either as a backup or to transfer to another database. LoadFromText can then be used to retrieve those objects from text files. The article...

isladogs.co.uk

 

[Papa_Bear1]

The problem with forms is that the controls are bound to the fields in the table. But a control is much more than just to store data, it should reflect how the user wants the control to behave, e.g. with BeforeUPdate and AfterUpdate events.
This makes the form very static, and not sharable between applications.

With "Late Control Binding" you can alter this behaviour. This means that when you Enter a control, a reference is made to a meta data record, that contains ALL information about the control, independant on which form this "control" is placed, including how to bind.
The form itself has a number of hidden form-controls, that are tuned dependant on the item/entity, and thus can be reused for any table/query. The "tuning" is with processes that can be shared in a linked library database, and thus available in all applications.
With this approach I have automatically build over 100 applications, just by filling the metadata tables.

Very interesting!
Sounds like you are using these concepts both inside and outside of MS Access?
Regardless, using data to drive UIs is something I've seen, and used minimally, but yes - very powerful!

 

[Papa_Bear1]

SaveAsText / LoadFromText are indeed very powerful tools for exporting / importing all objects EXCEPT tables.
However, neither can be used for tables (apart from one little known exception).

See my article for more details and an alternative solution for tables.

Save As Text / Load From Text

This article outlines the use of two very useful but hidden and undocumented items in Access. SaveAsText is used to export database objects to text files either as a backup or to transfer to another database. LoadFromText can then be used to retrieve those objects from text files. The article...

isladogs.co.uk

I will take a look at that - thanks!

I do wonder how that code segment exists, with tables in it, if it is known to not work with tables. But - again - no worries. Just happy to have turned the corner on this and hopefully the mostly-difficult stuff is behind now!

thanks again!

 

[MajP]

> It seems like if this were to be leveraged to repair a damaged/corrupted file - then it would HAVE to be done remotely/externally, unless you could at least open the corrupted file and insert this code etc

It is a good tool but not a panacea. If the code project is completely corrupted then you are likely out of luck. This technique is good when the database still opens, but you cannot open or use a specific form or report. Most of the time a decompile and recompile will work, but in a few cases even that will not save the form/report and this technique can help.

 

[isladogs]

I will take a look at that - thanks!

I do wonder how that code segment exists, with tables in it, if it is known to not work with tables. But - again - no worries. Just happy to have turned the corner on this and hopefully the mostly-difficult stuff is behind now!

thanks again!

Possibly the code provided earlier was generated using AI. Either way, the tables section will not work for any database.