Run Access code from Excel

DCRAPACCESS
DCRAPACCESS used Ask the Experts™
on
Hi Experts,

I have a access database with a Sub, that i would like to execute from Excel.

I have found this code, that works:
Dim A As Object
Set A = CreateObject("Access.Application")
A.Visible = True
A.OpenCurrentDatabase ("C:\Documents and Settings\rcrill\My
Documents\Ref_Code\ImportExcel.mdb")
A.Run "ImportExcel"<---------------------This Fails
A.Quit

Open in new window


But i my case the database is already open, so is it possible to just tell the already open database to just run the Sub without opening the same database again?
 
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008

Commented:
I don't think your code actually opens the database a second time. I believe the Access application, if open, creates a service that serves the database to the application. In other words, multiple users can open the same database and it will work fine.

Kevin
Most Valuable Expert 2011
Top Expert 2011
Commented:
Try:
Dim A As Object
Set A = GetObject("C:\Documents and Settings\rcrill\My
Documents\Ref_Code\ImportExcel.mdb")
A.Application.Run "ImportExcel"
A.Application.Quit

Open in new window

Top Expert 2008

Commented:
Note that if Access is NOT already open Rory's code will fail. In my opinion it is not worth the effort to try to leverage an existing open Access application instance. Memory just isn't that rare a commodity any more.

If you do still want to pursue this avenue, you will need to test the result of the GetObject function:

Dim A As Object
On Error Resume Next
Set A = GetObject("C:\Documents and Settings\rcrill\My Documents\Ref_Code\ImportExcel.mdb")
On Error GoTo 0
If A Is Nothing Then
    Set A = CreateObject("Access.Application")
    A.Visible = True
    A.OpenCurrentDatabase ("C:\Documents and Settings\rcrill\My Documents\Ref_Code\ImportExcel.mdb")
End If
A.Application.Run "ImportExcel"
A.Application.Quit

Another problem with the above code is that, if Access is already open, you will close it after doing your business. That is not a nice move if the user is doing something else with Access. Better to start a new instance, keep it hidden, do your work, and then close it.

Kevin
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Most Valuable Expert 2011
Top Expert 2011

Commented:
What if the user already has it open exclusively? :)
Top Expert 2008

Commented:
An unusual case that has to be specifically requested (it is not the default mode.) I've never run into that problem in any production systems.

Kevin

Author

Commented:
Great solution!!! Just what i was looking for.

Commented:
You better use an ODBC connection to the database instead of opening it directly for all your applications. BTW: Access is very reliable to use with many users or applications simultaneously.
Most Valuable Expert 2011
Top Expert 2011

Commented:
Actually, it can be set as the default in the options and I know of several users here who have that set. Seems just as easy to me to grab it if it's already open.
Anyhoo, we're getting a little off piste I think. ;)

Author

Commented:
I can't tell if there is a better way to do it, but this was what i was looking for, and it is working great.
Top Expert 2008

Commented:
>Actually, it can be set as the default in the options and I know of several users here who have that set. Seems just as easy to me to grab it if it's already open.

Yes, it can. And, for anyone reading this, a most unfortunate user habit if not required. Access' default behavior is to open files (databases) in a shared mode and this is the desired outcome in any multiple user environment. The only reason that I am aware of for opening with exclusive rights is when a developer or maintenance process requires such rights to the file in order to do things not conducive to a multiple user scenario. And in that case there is good reason NOT to allow another process to access the database.

Kevin

Commented:
I meant: access is NOT very reliable. You can download a express version of SQL Server which will better suit your needs. You can still use Access as a frontend, meaning you can use your forms without almost any changes. Very usefukk this way, and you won't have problem with many users. Also, excel can connect to an SQL Server Database too

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial