Accessing a Macro in Access from VB6.0

I can currently access a table from Access with VB but can figure out how to execute the macro in ACCESS from VB6.0.  The Macro will then import data from a txt file into the correct table. Here is the code I currently have.  Please help.  Like I said, this will get the table I need, but I need to access the Macro instead of the table.

Option Explicit
Private dbDatabase As Database
Private dbTable As Recordset
Private dbWorkspace As Workspace

Private Sub cmdImport_Click()

Set dbWorkspace = DBEngine.Workspaces(0)
Set dbDatabase = dbWorkspace.OpenDatabase("I:\My Folder\Provider Member Filter\Kelsey-Seybold.mdb")
Set dbTable = dbDatabase.OpenRecordset("Caremark", dbOpenTable)

End Sub
Who is Participating?
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.

This might be a simple solution, but why not run the macro using VBA?

If you've already got the db open the following should work.

If you haven't already done so, set a Reference to the Microsoft Access 8.0 Object Library.

Run this code:


jwellisAuthor Commented:
RichW, I tried that and it gave me the following error:
(Run-time error'2486':
You can't carry out this action at the present time. @You tried to run a macro or used the DoCmd object in Visual Basic to carry out an actionl.  However, Microsoft Access is performing another activity that prevents this action from being carried out now.  For example, no actions on a form can be carried out while Microsoft Access is repainting a control or calculating an expression.  @Carry out the action late.@1@@1. )

It seemed to work fine when I had ACCESS open but gives me this error if I close ACCESS.
Let VB launch Access for you and close it for you when it is done.  Does this sub help you?

Sub YourSubName()

' You can comment this out
Debug.Print "Start Test Report"

' This module opens Access, executes a macro within the access database to ouput a report, and  closes the Access database.

Dim thereport As New Access.Application

'Open the Access database and run the macro to output the report

thereport.OpenCurrentDatabase ("path name here.mdb")
thereport.DoCmd.RunMacro ("macro name here")

' Close the Access database

' You can comment this out
Debug.Print "End Test Report"

' Uncomment this if you want messagebox notification of completion
'message = "Macro Complete"
'MsgBox message

End Sub

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
I forgot to mention that as RichW indicated, you do have to set a reference in your project to the Microsoft Access library....
jwellisAuthor Commented:
Thanks ZLeader, works like a champ.
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
Visual Basic Classic

From novice to tech pro — start learning today.