[RESOLVED] Calling Access from Excel

Three part question.

First question
I have an Excel/VBA sub that calls Access/VBA sub:

Excel code:

Code:

---

Sub testAccessSub()
    Dim myAccess As Access.Application, oStr As String

    Set myAccess = New Access.Application
    myAccess.OpenCurrentDatabase Worksheets("main").Range("database")

    myAccess.Run "testSub", "test string", oStr
    myAccess.Quit
    Set myAccess = Nothing
   
    Debug.Print oStr
End Sub

---

Access code:

Code:

---

Public Sub testSub(iString As String, oString As String)
    oString = iString & "_ack"
End Sub

---

It seems to work (the output string is correct), but twice now after running it, Excel crashed a few seconds after the Sub finished.

In my experience, when a program crashes at a strange time like that, it's usually indicative of a memory problem. Maybe a dynamic variable that's unallocated. Maybe overwriting an allocated variable's memory. This is the first time I'm playing around with calling another application like this, so I'm wondering... can someone please tell me if I'm doing it correctly? Any problem with this code? Is it necessary to set myAccess to Nothing? I would think Excel's garbage collector handles that when the sub goes out of scope.


Second question
Is there a way to debug the Access side in the VBE? (Is there any way to set a breakpoint in the Access code?)



Third question
The above code demonstrates input and output parameters for a Sub. Is there a way of calling an Access VBA *function* (as opposed to a Sub)?

Thanks!

I really do not have any idea why Excel would be crashing. I would suggest that you explicitly close the DB though.

Code:

---

myAccess.CloseCurrentDatabase

---

You may also want to specify acQuitSaveNone on the Quit command.

Code:

---

myAccess.Quit acQuitSaveNone

---

For your second question, make Access Visible.

Code:

---

myAccess.Visible = True
Stop ' this sets a debug breakpoint

---

Then manually switch over to Access and open the VBE editor and set your breakpoints and switch back to the Excel VBE and step through the code. When you hit the Run command you should be switched over to the Access VBE.

For your third question, the answer is "Yes". The "Run" command is a Function that returns a variant type.

Code:

---

Dim outstr As String
outstr = myAccess.Run("testFunc", "hi")

---

Wow! There is a LOT of really terrific knowledge packed in your reply.

You kind of blew my mind with VBA's "stop". I never knew about that statement. That's pretty cool!

You've just increased my VBA knowledge hugely. Thanks!!