Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2002
    Posts
    5

    Unanswered: Calling Access Query from Excel VB

    Can someone tell me if they know how to call a query in Access from Excel via VB code? I believe I need to create objects or something...getting a little confused :-(



    Thanks

  2. #2
    Join Date
    Jun 2002
    Posts
    7

    Re: Calling Access Query from Excel VB

    Originally posted by lasw10
    Can someone tell me if they know how to call a query in Access from Excel via VB code? I believe I need to create objects or something...getting a little confused :-(

    Thanks
    Confused me with your wording,if you want to do what i think you do why use vb at all. If you want to get query into Excel from Access then this is easy. Use the "Get External data" feature in data and follow the wizards. if its from excel into the db then why not try the transferspreadsheet command in macros. Hope this helps.

  3. #3
    Join Date
    Sep 2002
    Posts
    5

    Re: Calling Access Query from Excel VB

    Thanks for your help Fud - the reason I want to do this via VB is I want to automatically update a table in Access from Excel (without having to go into Access at all and no need for manual intervention as this could be set up as an Auto_Open Macro...) - if you're interested I solved it with the following:


    Sub run_vb_other_application()

    Dim obj1 As AccessObject
    Dim strPath As String
    Dim strDBName As String

    Set appaccess1 = New Access.Application
    strPath = "R:\current\Finance\ABT\"
    strFile = "Process.mdb"
    strDBName = strPath & strFile
    appaccess1.OpenCurrentDatabase strDBName

    DoCmd.OpenQuery ("Query1")

    DoCmd.TransferSpreadsheet acImport, 8, "DEPT_FILTERED_DATA", "R:\current\Finance\ABT\Process2.xls", True, "A1:L35000"

    End Sub

    Worked a treat...




    Originally posted by Fud
    Confused me with your wording,if you want to do what i think you do why use vb at all. If you want to get query into Excel from Access then this is easy. Use the "Get External data" feature in data and follow the wizards. if its from excel into the db then why not try the transferspreadsheet command in macros. Hope this helps.

Posting Permissions

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