Open Access 2007 from Excel 2007 vba

Is there Excel 2007 vba code that will open an Access 2007 database and then trigger a saved import to the Access DB (from a different Excel SS)?
JSSeniorAsked:
Who is Participating?
 
ScriptAddictCommented:
I tested this code today.  The comment marker was wrong, but I wanted to test it on a macro so I used a slightly different command then used for calling a proceedure in access.  Hopefully this will help you get where you need to go!

Merry Christmas!

-SA

Public Sub ProcedureInAccess()
'this VBA code is run in Excel
Dim acApp As Object
Dim db As Object
Set acApp = CreateObject("Access.Application")
acApp.OpenCurrentDatabase ("F:\path\databasename.mdb")
Set db = acApp
acApp.docmd.runmacro "NameOfYourMacroAsItAppearsInAccess"
acApp.Quit
Set acApp = Nothing

End Sub

Open in new window

1
 
ScriptAddictCommented:
THis code block should allow you to open an access database:
Public Sub ProcedureInAccess()

‘this VBA code is run in Excel
Dim acApp As Object
Dim db As Object
Set acApp = CreateObject("Access.Application")
acApp.OpenCurrentDatabase ("C:\MyDatabaseName.mdb")
Set db = acApp
acApp.Run "NameOfProcedureInAccess"
acApp.Quit
Set acApp = Nothing

End Sub 

Open in new window


Then just have a proceedure in access (you likely already have one)  that you call using the above code.  GL!
0
 
ScriptAddictCommented:
If you have any problems

Set db = acApp
Not: Set db = CurrentDb
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
JSSeniorAuthor Commented:
Hi sorry for the delayed reply.  Im getting a run time error 7866....MS cant open because its missing or opened exclusively or it is not an ADP file.

It's Access 2007 any idea's?
0
 
ScriptAddictCommented:
I'm not sure about that error code, it sounded like you didn't have the path and file name correct.  Did you include the file extention?  the .mdb or .accdb or w/e?

-SA
0
 
JSSeniorAuthor Commented:
Access 2007 basic file extention ACCDB
0
 
RayneCommented:
awesome guys, this also worked for me. Thank you and cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.