Run Access Sub from excel

[Auto]

hi.

i am having a module in access named "GlobalFunctions"
in that module i have alot of sub's and functions.
there is a public sub named "ImportOldData"



i need to run this sub from my excel application, i have created a code in vba to do it, but somehow it generates an error number '2485' that mydatabase cant find my module named 'GlobalFunctions'

i tried so many diffrent ways, but no luck, maybe anyone here can help.

here is my code.

Dim appAccess As Object

strDatabasePath = "C:\Test.accde"
Set appAccess = GetObject(, "Access.Application")

With appAccess
.Visible = True
.DoCmd.RunMacro "GlobalFunctions.ImportOldData"
End With
Set appAccess = Nothing

[June7]

This is code I use in Access VBA to run VBA procedure in another Access db.

Dim A As New Access.Application
Set A = CreateObject("Access.Application")
A.Visible = False
A.OpenCurrentDatabase "filepath\filename.accdb", False
A.Run "GetPaverData"
Set A = Nothing

[ssanfu]

As you see from June's code, your code creates an instance of Access, but fails to open a database.

I did find two sites that might help you. I think the 2nd link has more info...
See:
http://accessprogrammer.blogspot.com...procedure.html
http://www.mrexcel.com/forum/excel-q...ule-excel.html

[Auto]

This is code I use in Access VBA to run VBA procedure in another Access db.

Dim A As New Access.Application
Set A = CreateObject("Access.Application")
A.Visible = False
A.OpenCurrentDatabase "filepath\filename.accdb", False
A.Run "GetPaverData"
Set A = Nothing

thanks for your reply, i tried that too, but it dont work, it opens up the db, and it throws me an error that my db cant find that procedure.

[Auto]

As you see from June's code, your code creates an instance of Access, but fails to open a database.

I did find two sites that might help you. I think the 2nd link has more info...
See:
http://accessprogrammer.blogspot.com...procedure.html
http://www.mrexcel.com/forum/excel-q...ule-excel.html

thanks for your reply, i went to both links but it dont help me, the error still comes up no matter what i change in the code, in regards that you say it fails to open the db, it dont fail, it opens it and it tries to run the macro but it gets back from access that this macro dont exist, thats how i understand the error msg, might be i am wrong, but i tried to do it with creatObject as well with GetObject, when its createObject it opens the db and sends me the error msg as well as if its GetObject and the db is open.

[June7]

Show your attempted code.

Is the macro Embedded or a general macro?

My code runs a VBA procedure in a general module, not a macro.

[Auto]

just wondering, the db which had the module is a accde file, could it be that you cant call a macro from an accde file?

[Auto]

Show your attempted code.

Is the macro Embedded or a general macro?

My code runs a VBA procedure in a general module, not a macro.

my macro sits in a module called GlobalFunctions and in that module i have a public sub named ImportOldData.
i am not sure what you mean an Embedded, you mean a private sub? or a sub within a form or so?

[Auto]

Show your attempted code.
.

here is what i tried last.

Public Sub ProcedureInAccess()

Dim acApp As Object
Dim db As Object
Set acApp = CreateObject("Access.Application")
acApp.OpenCurrentDatabase ("C:\Test.accde")
acApp.Run "ImportOldData"


Set acApp = Nothing


End Sub

here is my sub from the access db

Public Sub ImportOldData()Dim OldDb As String
OldDb = Form_Main.Version.Caption - 0.1


DoCmd.SetWarnings False
DoCmd.TransferDatabase acImport, "Microsoft Access", "c:\TestOld.accde", acTable, "Customers", "Customers", False
DoCmd.TransferDatabase acImport, "Microsoft Access", "c:\TestOld.accde", acTable, "Orders", "Orders", False
DoCmd.SetWarnings True


End Sub

[June7]

Macros and VBA code are very different in Access. If your procedures in Access are entirely VBA then you are not calling a macro.

Even in Excel, when you create a macro, really creating a VBA procedure.

You are trying to call a procedure in an accde file. If I understand accde correctly, it is a compiled executable program. The original VBA code modules do not exist in accde. http://office.microsoft.com/en-us/ac...x#BMextensions

However, might be able to call a general macro that is in an accde. I've never used accde file.

[Auto]

Macros and VBA code are very different in Access. If your procedures in Access are entirely VBA then you are not calling a macro.

Even in Excel, when you create a macro, really creating a VBA procedure.

You are trying to call a procedure in an accde file. If I understand accde correctly, it is a compiled executable program. The original VBA code modules do not exist in accde. http://office.microsoft.com/en-us/ac...x#BMextensions

However, might be able to call a general macro that is in an accde. I've never used accde file.

thanks june7, thats exactly what i thought the problem is.

now i really have no experience with macros in access, i do all my work in vba since this is my first language
what is the limitations of macros in access 2007? can i do the same stuff as i do in my sub? and where do i need to hook up that macro?

is there any workaround what i can do? maybe manipulating with excel vba and send all functions from excel to access, would that work?

[June7]

I haven't used macros much. I know they can call a VBA function procedure. No, they cannot do everything that VBA can do.

I don't know if accde loses macros (if they are somehow compiled like the VBA).

Suggest you do some research and study on macros. Two basic types - embedded (alternative to [Event Procedure]) and general standalone. Build a standalone macro, just to open a message box maybe, in the original accdb file then publish as accde. See if the Excel code can call it.

[Auto]

hi june7.

i got it work, and here is the workaround.

first of all, we were right with that, you cannot call a vba function or sub from an accde file.

so what i did is, i created a macro as you mentioned before, and i called the function from the macro, and i named the macro a friendly name, then i call the macro from the excel vba, and now it works.

THANK YOU ALL FOR HELPING ME TO SOLVE MY ISSUE!
Cheers.