• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

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
  • 2
  • 2
1 Solution
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
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.

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now