Open Access Database from Excel, and then run an Access Module 2

[RichF01]

Hi people

Thanks in advance

From an Excel macro, I am trying to open an Access database and then run a module within the database.

I can get Access opened in a couple of ways, but can not open a specific file.

Dim Retval
Dim fileval

Retval = Shell("C:\Program Files\Microsoft Office\Office\MSACCESS.EXE", vbMaximizedFocus)
[COLOR=red]fileval.getfile ("filepath\filename") [/color]

OR

Dim axsAPP As Object

Set axsAPP = CreateObject("Access.Application")

axsAPP.Visible = True
axsAPP.database.Open ("filepath\filename")
axsAPP.Quit
'Activate Access File
[COLOR=red]'Open db.Filename:="filepath\filename"[/color]

The bits in red are the code I am unsure of.

Unfortunately, I don't know enough VBA for Access to turn it round and do it from Access.

I have no idea where to start in actually calling the macro in the database once the file is opened.

All help welcome and appreciated

Thanks
Rich

 

[Trevil]

Using your first example, change:
Retval = Shell("C:\Program Files\Microsoft Office\Office\MSACCESS.EXE", ...

to:

Retval = Shell("C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" & " " & chr(34) & "C:\path\file" & chr(34),

Code: Where the vision is often rudely introduced to reality!

 

[Molby]

I use the following to run an Access macro within a database. If there is a specific Sub that you have created that want to run, then you can set that to run using RunCode within the Access Macro.

DatabaseName$ = "C:\Database.mdb"
Set acApp = CreateObject("Access.Application")
acApp.Visible = True
acApp.OpenCurrentDatabase (DatabaseName$)
acApp.DoCmd.RunMacro "YourMacroName"

 

[RichF01]

Molby

You absolute star. Works perfectly. That pretty much finishes my project so you have just made me a very happy man

Thanks a lot!

Rich