Moving a .BAS file into an external database

I need to create a .BAS file which is pushed into an external Access .mdb file.  Any suggestions?
Andy BrownDeveloperAsked:
Who is Participating?
If you would like to use the LoadFromText() you should create the file with SaveAsText()

Public Sub PushModule(strModule As String, strDestinationDB As String)
    Dim appDestination As Access.Application
    Dim strPath As String
    strPath = CurrentProject.Path
    'Save the module to a text file
    SaveAsText acModule, strModule, strPath & "\" & strModule & ".mdl"
    'Create an instance of Access to work with the destination database
    Set appDestination = New Access.Application
    With appDestination
        'Open the destination database and load the text file (module)
        .OpenCurrentDatabase strDestinationDB
        .LoadFromText acModule, strModule, strPath & "\" & strModule & ".mdl"
        'Close the database and quit the Access instance
    End With
End Sub

By using this method you will be left with a text file with the name of your module and the .mdl extension (just my preference) in the folder that contains the host database.
Are your trying to take a module from one db and make a copy of it into another db?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
If you open up a given module ... then File>>Export File ...

the Windows dialog box comes up ... and the SaveAs is already set to .BAS

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Andy BrownDeveloperAuthor Commented:
Thanks guys.  

I create the VBA on the fly as it is to do with a security feature.  Historically, I have created the.BAS file, imported it into the open database (running the function) and then exported it to the destination.  When I run this function it may upgrade 40+ databases (all with different VBA scripts) and it seems to be really slow.

If I could send the .BAS file into the destination .mdb database, i'm sure it would speed things up.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well, on the flip side ... you can then Import that .bas file into the other mdb.  Not sure how this can be automated, which is what you are after, right ?

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Does this .bas file change over time, or is it static? If it's static, you could just include a module that created your module - although I'm not sure how well that would work.

You could also use Application.LoadFromText(acModule, "YourModuleName", "full path to the module.txt file")

Note that LoadFromText expects the incoming file to be in a specific format, so be aware of that.
Andy BrownDeveloperAuthor Commented:
Hi, unfortunately it isn't static, which is why i dont want to keep deleting and importing .BAS files to the main system.

I'm going to give the Application.LoadFromText function a try in the morning - that might work.

I'll keep you posted and come back to you.

Thanks as always.
From the host database, you can use the DoCmd.TransferDatabase method ...

Public Sub FooBar(strModule As String, strDestinationDB As String)

    DoCmd.TransferDatabase acExport, "Microsoft Access", strDestinationDB, _
            acModule, strModule, strModule

End Sub

Andy BrownDeveloperAuthor Commented:
This solution worked beautifully.

Nice work everyone - thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.