Results 1 to 3 of 3

Thread: [RESOLVED] Calling Access from Excel

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Sep 2006

    Resolved [RESOLVED] Calling Access from Excel

    Three part question.

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

    Excel 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
        Set myAccess = Nothing
        Debug.Print oStr
    End Sub

    Access 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)?

    Last edited by caffeine; May 31st, 2015 at 06:30 PM. Reason: Thought of something I forgot to ask.

  2. #2
    Join Date
    Oct 2010

    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.
    You may also want to specify acQuitSaveNone on the Quit command.
    myAccess.Quit acQuitSaveNone
    For your second question, make Access Visible.
    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.
    Dim outstr As String
    outstr = myAccess.Run("testFunc", "hi")
    Last edited by TnTinMN; Jun 1st, 2015 at 05:32 PM. Reason: typo

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Sep 2006

    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


Click Here to Expand Forum to Full Width