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")
Do Until tblB.EOF
strParam = "PARAMETERS fldA TEXT, fldB TEXT;"
strIN = " INSERT INTO " & (fldB) & " SELECT " & (fldA) & " FROM " & (fldA) & ";"
strSql = strParam & strIN
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.