-
May 31st, 2015, 06:27 PM
#1
Thread Starter
Addicted Member
[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!
Last edited by caffeine; May 31st, 2015 at 06:30 PM.
Reason: Thought of something I forgot to ask.
-
May 31st, 2015, 11:03 PM
#2
Re: Calling Access from Excel
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")
Last edited by TnTinMN; Jun 1st, 2015 at 05:32 PM.
Reason: typo
-
Jun 1st, 2015, 07:58 AM
#3
Thread Starter
Addicted Member
Re: Calling Access from Excel
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!!
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|