Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running Access from Excel (Excel 2000)

    Is it possible to write a VBA line in Excel to open a form in Access and then run a querry in Access ? If so, can someone give me a line(s) ... Many thanks. !

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Running Access from Excel (Excel 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> TQ2

    <font color=red>WHY?</font color=red>

    MS-Excel has its own Query, that can attach to MS-Access and get the data. If you want to run the query, simply check the Get External Data option from the Data menu and see if it fits the bill.

    Alternativly, you can have ADO, DAO, SQL or what have you of the Data object model help you out.

    Check the Get External Data first, and then lets talk afterwards.

    Now for answering your question directly, Yes it is also possible to control Access from within an Excel project via VBA. You need to do is reference the Access Object library with something like:

    Dim AccessApp as Object
    Set AccessApp = CreateObject("Access.application"), it might be MSAccess.application, but I am not sure.
    AccessApp .Visible = True

    Then you have Access as your running, with focus, application and thus you can run the form.

    Now how do you get the form's input or the Query results back to MS-Excel? You have to define Global variables that the Access App will know about, and that is why you should simply use MS-Excel for this type deal.

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Access from Excel (Excel 2000)

    Wassim,

    Thanks. The reason I want to open the Access is that I've built around 20 crosstab querries in Access and also linked these queries to the Excel sheets. However, these are all time definitive query, so I want to be able to click on Excel, open the Access form, input the data and run the queries (some are make table queries), the update the data back to Excel.

    BTW, I tried your command, it does not seem to work. Is there something else I need to do to run Access from Excel ? Many thanks for your help.

    TQ

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Access from Excel (Excel 2000)

    The short answer is "yes". This is a well-worn topic in the Lounge. Have you tried running a Search eiher on the Access Board or here on the Excel Board?
    Gre

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Access from Excel (Excel 2000)

    The CreateObject and following instructions are meant for controlling Access in code from Excel. If you want to open a database interactively, just open it through a hyperlink. You can put a hyperlink in a cell, or use code:

    ActiveWorkbook.FollowHyperlink "C:AccessMyDatabase.mdb"

Posting Permissions

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