Results 1 to 2 of 2

Thread: Run the “ready” query in ACCESS from VBA Excel with the parameter
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2007
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default Run the “ready” query in ACCESS from VBA Excel with the parameter

    There is an "external" ready query in Access with name " ZZ1 " with a single SQL line:


    Q – external parameter.
    If I run this query from Access, the system asks me the value Q, and I enter, for example, “A1”, and the query works correctly. Now I want to run the same query, but from the VBA EXCEL....
    When I look at the Internet Examples I found this code, but it doesn’t work:

    Public Con, rs As Object
    Public Г±onStrRead As String
    Function WritePeremToBD() As Boolean
    PathToDB = ThisWorkbook.Path & "" & "DDD.mdb" ' ГЇГіГІГј ГЄ ГЃГ„
    Г±onStrRead = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source= " + PathToDB + _
    ";Mode=ReadWrite;Jet OLEDB:System database=;Jet OLEDB:Registry Path=;Jet OLEDB:Database Password="
    End Function

    Sub sp_ExecuteSQL()
    Dim strConn As String
    Dim CCon, oCmd, oPara As Object
    Set CCon = CreateObject("ADODB.Connection")
    Set oCmd = CreateObject("ADODB.Command")
    Set oPara = CreateObject("ADODB.Parameter")
    CCon.Open Г±onStrRead
    Set oCmd = CreateObject("adodb.command")
    oCmd.ActiveConnection = CCon
    oCmd.CommandText = "ZZZ1"
    Set oPara = oCmd.CreateParameter("QQQ", adVarChar, adParamInput, 10)
    oCmd.Parameters.Append oPara
    oCmd.Parameters(0) = "A1"
    Set Conn = Nothing
    MsgBox "OK!"
    End Sub

    Please help!
    Could you make for me an example

  2. #2
    Board Regular
    Join Date
    Jul 2010
    Post Thanks / Like
    3 Post(s)
    0 Thread(s)

    Default Re: Run the “ready” query in ACCESS from VBA Excel with the parameter

    What do you mean it doesn't work?

    what would you like to happen? The original query is a select query - if that runs you wouldn't see anything. Are you trying to write something back to the database? or are you trying to write the results to Excel?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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