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
    Posts
    86
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

    SELECT * FROM Tabl1 WHERE TTT=Q;

    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")
    '
    WritePeremToBD
    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"
    oCmd.Execute
    Conn.Close
    Set Conn = Nothing
    MsgBox "OK!"
    End Sub


    Please help!
    Could you make for me an example

  2. #2
    Board Regular
    Join Date
    Jul 2010
    Posts
    464
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    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
  •