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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 Architect / Systems AnalystCommented:
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

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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Andy BrownDeveloperAuthor Commented:
This solution worked beautifully.

Nice work everyone - thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.