Results 1 to 7 of 7

Thread: Run Access Macros From Excel Or Run A Macro In MS-Access From Excel VBA

  1. #1
    Junior Member bobdole22's Avatar
    Join Date
    Aug 2013
    Posts
    7
    Rep Power
    0

    Run Access Macros From Excel Or Run A Macro In MS-Access From Excel VBA

    I am trying to run this code, I got from another thread on this forum that is closed. It is supposed to link to an Access file and run it's macro.I have 2007, so my db files are ".accdb" if that changes anything. Here is the code:

    VB:
    Code:
    Dim strDatabasePath As String 
    Dim appAccess As Access.Application 
     
    strDatabasePath = "C:\Users\zachk\Desktop\Strats 2011.01.accdb" 
    Set appAccess = New Access.Application 
    With appAccess 
        Application.DisplayAlerts = False 
        .OpenCurrentDatabase strDatabasePath 
        .DoCmd.RunMacro "qry_MakeTrustTable_Adhoc" 
        .Quit 
    End With 
    Set appAccess = Nothing
    I am getting the error
    User-defined Type not defined

    on line:

    Set appAccess = New Access.Application

  2. #2
    Junior Member bobdole22's Avatar
    Join Date
    Aug 2013
    Posts
    7
    Rep Power
    0
    Actually, it only does that the second time I run it. The first time it simply reports a Blank MessageBox " ". Doesn't have any info whatsoever. Is it possible I'm getting a warning because we usually runs it with warnings off in Access? Is that possible in Excel.

    Also, even stranger is that I have a MsgBox "Finished" at the end of the sub that doesn't get run. This makes me think that the code doesn't go all the way though.
    "My common sense is tingling!" - Deadpool
    For more: http://marveldcforum.com

  3. #3
    Member rollis13's Avatar
    Join Date
    Nov 2012
    Posts
    36
    Rep Power
    0
    Last edited by rollis13; 08-29-2013 at 03:02 AM.

  4. #4
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    Dear bobdole22,

    Please read the forum rules regarding cross-posting
    http://www.excelfox.com/forum/f25/me...1172/#post5326

    As an answer to your question, you need to set a reference to Microsoft Access 12.0 Object Library

  5. #5
    Junior Member bobdole22's Avatar
    Join Date
    Aug 2013
    Posts
    7
    Rep Power
    0
    Apologiez for the cross-post. Won't happen again. Although even after adding the reference the code still won't run all the way through. It stops right at

    appAccess.DoCmd.RunMacro "qry_MakeTrustTable_Adhoc"

    and displays a blank pop up box. Could it be a warning? It also doesn't do any of the code afterward. I double check the macro name and that is not the problem. He is a screen shot of the pop up I get.
    Attached Images Attached Images
    "My common sense is tingling!" - Deadpool
    For more: http://marveldcforum.com

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Are you sure
    qry_MakeTrustTable_Adhoc exists as a Visual Basic macro within that database? If yes, try this

    Code:
    Dim strDatabasePath As String
    Dim appAccess As Access.Application
     
    strDatabasePath = "C:\Users\zachk\Desktop\Strats 2011.01.accdb"
    Set appAccess = New Access.Application
    With appAccess
        .OpenCurrentDatabase strDatabasePath
        .Run "qry_MakeTrustTable_Adhoc"
        .Quit
    End With
    Set appAccess = Nothing
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Junior Member
    Join Date
    Aug 2019
    Posts
    1
    Rep Power
    0
    Well dear, I can help you out in knowing how to create macros in Access with 3 different ways and how to run it. I have read this article when I was stuck in some problem with my Access macros coding. it’s really very helpful post. So, I thought you might also get some help from it.

    SO TRY IT…!
    3 Quick Ways To Create Macro In MS Access 2010/2013/2016/2019 Database

Similar Threads

  1. Export data from Excel to Access Table (ADO) using VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 4
    Last Post: 02-24-2015, 07:53 PM
  2. Replies: 1
    Last Post: 06-18-2013, 07:46 AM
  3. Replies: 7
    Last Post: 05-09-2013, 11:16 PM
  4. Selecting workbook to run macro
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 2
    Last Post: 08-24-2012, 08:21 PM
  5. Click Run cycle
    By PcMax in forum Excel Help
    Replies: 6
    Last Post: 11-01-2011, 04:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •