Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    4

    Unanswered: Insert (VBA versus Access Query)

    I am trying to automate one of the queries using vba.

    When I execute the Access query, I get data. Not with the VBA code. Could it be related to the Execute command that I am using? Is there a better way?

    Access Query:
    INSERT INTO Qty_Return_NA_MV (Model, ReceiptQty, SerialNumber, ReceiptDate, ReceiptMonth, SN_Supplier, SN_Source, rptGrouping)
    SELECT [Suppliers-MV].Model,
    dbo_tblPartReceipts.ReceiptQty,
    dbo_tblPartReceipts.SerialNumber,
    dbo_tblPartReceipts.ReceiptDate,
    (DatePart('yyyy',[ReceiptDate])*100)+(DatePart('m',[ReceiptDate])) AS [Receipt Month],
    [Suppliers-MV].SN_Supplier,
    [Suppliers-MV].SN_Source,
    dbo_tblPartReceipts.rptGrouping
    FROM dbo_tblPartReceipts
    INNER JOIN [Suppliers-MV] ON dbo_tblPartReceipts.PartNumber = [Suppliers-MV].PART_NUM
    WHERE (((dbo_tblPartReceipts.ReceiptDate) Between #04/01/2004# And #04/30/2004#)
    AND ((dbo_tblPartReceipts.CntryCd) Like '*USA*' Or (dbo_tblPartReceipts.CntryCd) Like '*CAN*'))
    --------------------------------
    VBA (Access) Code:
    Dim conDatabase As ADODB.Connection
    Set conDatabase = Application.CurrentProject.Connection
    sql = "INSERT INTO Qty_Return_NA_MV (Model, ReceiptQty, SerialNumber, ReceiptDate, ReceiptMonth, SN_Supplier, SN_Source, rptGrouping) "
    sql = "SELECT [Suppliers-MV].Model, "
    sql = sql + "dbo_tblPartReceipts.ReceiptQty, "
    sql = sql + "dbo_tblPartReceipts.SerialNumber, "
    sql = sql + "dbo_tblPartReceipts.ReceiptDate, "
    sql = sql + "(DatePart('yyyy',[ReceiptDate])*100)+(DatePart('m',[ReceiptDate])) AS [Receipt Month], "
    sql = sql + "[Suppliers-MV].SN_Supplier, "
    sql = sql + "[Suppliers-MV].SN_Source, "
    sql = sql + "dbo_tblPartReceipts.rptGrouping "
    sql = sql + "FROM dbo_tblPartReceipts "
    sql = sql + "INNER JOIN [Suppliers-MV] ON dbo_tblPartReceipts.PartNumber = [Suppliers-MV].PART_NUM "
    sql = sql + "WHERE (((dbo_tblPartReceipts.ReceiptDate) Between #" & dateStart & "# And #" & dateEnd & "#) "
    sql = sql + "AND ((dbo_tblPartReceipts.CntryCd) Like '*USA*' Or (dbo_tblPartReceipts.CntryCd) Like '*CAN*'))"
    conDatabase.Execute sql
    conDatabase.Close
    Set conDatabase = Nothing

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Why use an ado connection when you can simply use

    Currentdb.execute sql

    Also the "proper" way to connect two strings (concatinate) is to use & instead of +

    Thus:
    sql = sql + "dbo_tblPartReceipts.ReceiptQty, "
    would be
    sql = sql & "dbo_tblPartReceipts.ReceiptQty, "

    Then to resolve your problem:
    sql = "INSERT INTO Qty_Return_NA_MV (Model, ReceiptQty, SerialNumber, ReceiptDate, ReceiptMonth, SN_Supplier, SN_Source, rptGrouping) "
    sql = "SELECT [Suppliers-MV].Model, "

    You are not concatinating your Insert into....
    change the select line to
    sql = sql & "SELECT [Suppliers-MV].Model, "
    and it should work

    Regards

    P.S. Just some advise, if you want the last day of a month in code start from the first date of the next eg. 01-may-2004 and subtract 1 to get the last day of last month. This works regardless of 28, 29,30 or 31 days in a month.
    Last edited by namliam; 06-04-04 at 03:10.

  3. #3
    Join Date
    Jun 2004
    Posts
    4
    Thanks Namliam - Those are very good suggestions to start with.

    Here is the latest update per your suggestions and although adding the concat symbol was pretty obvious, the query still does not save any rows.

    My objective is to Join 2 tables (Table1 and Table2) and save the results into Table3. Perhaps I could simplify my code a but more.

    Any other ideas???

    Latest:
    sql = "INSERT INTO Qty_Return_NA_MV (Model, ReceiptQty, SerialNumber, ReceiptDate, ReceiptMonth, SN_Supplier, SN_Source, rptGrouping) "
    sql = sql & "SELECT [Suppliers-MV].Model, "
    sql = sql & "dbo_tblPartReceipts.ReceiptQty, "
    sql = sql & "dbo_tblPartReceipts.SerialNumber, "
    sql = sql & "dbo_tblPartReceipts.ReceiptDate, "
    sql = sql & "(DatePart('yyyy',[ReceiptDate])*100)+(DatePart('m',[ReceiptDate])) AS [Receipt Month], "
    sql = sql & "[Suppliers-MV].SN_Supplier, "
    sql = sql & "[Suppliers-MV].SN_Source, "
    sql = sql & "dbo_tblPartReceipts.rptGrouping "
    sql = sql & "FROM dbo_tblPartReceipts "
    sql = sql & "INNER JOIN [Suppliers-MV] ON dbo_tblPartReceipts.PartNumber = [Suppliers-MV].PART_NUM "
    sql = sql & "WHERE (((dbo_tblPartReceipts.ReceiptDate) Between #" & dateStart & "# And #" & dateEnd & "#) "
    sql = sql & "AND ((dbo_tblPartReceipts.CntryCd) Like '*USA*' Or (dbo_tblPartReceipts.CntryCd) Like '*CAN*'))"

    CurrentDb.Execute sql

  4. #4
    Join Date
    Jun 2004
    Posts
    4
    All is well now. After further research, I found out that the database did not have data for the date range I selected.

    Thanks for your help

Posting Permissions

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