Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Posts
    10

    Unanswered: Batch Append queries using code

    I want to run run an Append query as a batch using details stored in a table. One field refers to the table to be appended to and the other is the source table. Here is a sample of the code below.

    On Local Error GoTo AppendBatch_Err
    Dim dbM As Database, tblB As Recordset, qdfApp As QueryDef
    Dim strSql As String, strParam As String, strIN As String, strS As String, fldA As Field, fldB As Field
    Set dbM = CurrentDb()
    Set tblB = dbM.OpenRecordset("tblBatch", dbOpenTable)
    Set fldA = tblB("ImportedName")
    Set fldB = tblB("TableApp")

    DoCmd.Hourglass True
    tblB.MoveFirst
    Do Until tblB.EOF
    strParam = "PARAMETERS fldA TEXT, fldB TEXT;"
    strIN = " INSERT INTO " & (fldB) & " SELECT " & (fldA) & " FROM " & (fldA) & ";"
    strSql = strParam & strIN

    Set qdfApp = dbM.CreateQueryDef("", strSql)
    qdfApp.Parameters(0) = fldA
    qdfApp.Parameters(1) = fldB

    qdfApp.Execute
    tblB.MoveNext
    Loop
    tblB.Close
    AppendBatch_End:
    DoCmd.Hourglass False
    Exit Function

    AppendBatch_Err:
    MsgBox Err.Description
    Resume AppendBatch_End


    End Function

    It comes back with an error message of to few parameters. Hope some one can help.
    Terry

  2. #2
    Join Date
    Jan 2002
    Location
    UK
    Posts
    67
    I think you forgot to specify the table name for the table you want to insert the data into.

    you strIN shoud look like:

    tbl_APPENDTO = "TABLE NAME TO APPEND THE DATA TO"

    strIN = " INSERT INTO [" & (tbl_APPENDTO) & "] " & (fldB) & " SELECT " & (fldA) & " FROM " & (fldA) & ";"

    Hope this helps........

  3. #3
    Join Date
    Feb 2002
    Posts
    10

    Batch Append queries using code

    I have tried xzone's solution it now come up with the error, Number of query values and destination fields not the same. In the previous code the value of the destination table = (fldB). It will not allow me to put the wildcard * value in the strSQL string.
    Terry

Posting Permissions

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