VB 6.0 to call MS Access Macro

[Hosacans]

Hi all,

I'm trying to make an .exe using VB 6.0 to call an MS Access macro. I found the following code on the web.

Set your project references to MS Access x.x object type library and try running this code:

Public Sub RunAccessMacro(strDB As String, strMacro As String)
'================================================================
Dim AccessDB As Access.Application

Set AccessDB = New Access.Application
With AccessDB
.OpenCurrentDatabase strDB
.DoCmd.RunMacro strMacro, 1
'.Visible = True 'you decide
.CloseCurrentDatabase
End With
Set AccessDB = Nothing

End Sub

Private Sub btnRunMacro_Click()
'================================

RunAccessMacro App.Path & "\nwind.mdb", "Suppliers"

End Sub

NOTE: the following line "... App.Path & "\nwind.mdb", "Suppliers" will have to be replaced with YOUR database path and YOUR macro
name.

this works great, and i was able to get an test .exe to work.

but the purpose of this is for me to send this .exe off to users that do NOT have MS Access installed on their desktop.

so when i try this .exe on an VB and MS Access free machine, i get an "Runtime error 429, ActiveX component can't create object"

Does anyone know what this error means?

I have other VB .exe that retrieve data records in MS Access databases, and paste them on Excel.. and they all work properly on VB and MS Access free machines.

So what's so different for calling a MS Access macro?

please let me know
Thanks so much
Hosackies

 

[Swi]

I would post this in the following forum since it is a VB6 question.

forum222

You are still trying to use Access with the above code. What does this Macro exactly do?

Swi

 

[ClulessChris]

The Comment line at the top of the code you posted states that a refrence to MS Access is required.
When running this on a machine with out MS Access this refrence will be missing and the code will throw the error.

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[genomon]

Exactly what Chris said - there is a FAQ explaining this in the VB 5 & 6 forum.

"Before you criticize someone, you should walk a mile in their shoes.
That way, when you criticize them, you're a mile away and you have their shoes."

 

[ClulessChris]

genomon, thanks man you got my back

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[Hosacans]

thanks for the tips.

Hosackies

 

[Hosacans]

I've checked several FAQs and threads on this topic in the VB6 forum... from what i gathered, calling a MS Access Macro is NOT possible without MS Access installed in the client computer- without referencing any Access Object library in VB...

the macro i set up in MS Access does several text file imports, queries that formats the data, calculations, and finally an excel file export.

i suspect that ADODB will not do much for me because ADODB connections only manages modifying record sets in a database.

Am i right on these speculations or there is a way to do it?

thanks
Hosackies

 

[genomon]

If you do not install Access on the client machine, no luck. Period.

"Before you criticize someone, you should walk a mile in their shoes.
That way, when you criticize them, you're a mile away and you have their shoes."

 

[JoeAtWork]

You could eventually do all of that in VB, but it would be more code because you don't have the benefit of the pre-built methods in Access. For example, if you had a reference to the Access library, the text import could probably be done with one or two Access method calls (I don't know which ones). But without Access, you would need to open the text file, probably with file system methods, read through the lines and copy them to the Access tables via ADO.

The Excel file could only be created if Excel were on the client machine.