Access 2007 - APPEND QUERY IN VBA - Runtime Error 3129

[sw1085]

Hi everyone

I am new to this forum and I hoped someone could help me correct my code below as I am currently getting an error message of Run-time error 3129?
Sub TRFDATATO()
Dim tName As String
Dim strSQL As String
tName = "tblDatato & tblDatafrom.CO_CODE " & _
strSQL = "INSERT INTO " & tName & _
"SELECT SUN_DB, ACCNT_CODE, [NET AMOUNT], DESCRIPTN, JRNAL_LINE FROM " & _


"(SELECT SUN_DB, ACCNT_CODE, [NET AMOUNT], DESCRIPTN, 1 AS JRNAL_LINE " & _
"FROM tblDatafrom" & _
"WHERE [Ready] = True And Null<>[ACCNT_CODE] And Null<>[NET AMOUNT]" & _
"UNION ALL" & _
"SELECT SUN_DB, SUPP_CODE, [GROSS AMOUNT], DESCRIPTN, 2 AS JRNAL_LINE" & _
"FROM tblDatafrom" & _
"WHERE [Ready] = True And Null<>[SUPP_CODE] And Null<> [GROSS AMOUNT]" & _
"UNION ALL" & _
"SELECT SUN_DB, 271 AS ACCNT_CODE, VAT, DESCRIPTN, 3 AS JRNAL_LINE" & _
"FROM tblDatafrom" & _
"WHERE [Ready] = True AND Null<>ACCNT_CODE)"
DoCmd.RunSQL strSQL
End Sub

Thanks again

SW

[June7]

The value "tblDatato & tblDatafrom.CO_CODE " that the variable tName is set to doesn't make sense.

strSQL = "INSERT INTO tblDatato " & _

Need separating space at the end of each line before the quotation mark.

Can't use Null as criteria, use Is Null.
"WHERE [Ready] = True AND Not ACCNT_CODE Is Null)"

[sw1085]

The value "tblDatato & tblDatafrom.CO_CODE " that the variable tName is set to doesn't make sense.

The records to transfer would be based on the tblDatatoXXX - the XXX is a field from the table I am transferring from called CO_CODE. Does the above not achieve the concatenating of the two?

strSQL = "INSERT INTO tblDatato " & _

Need separating space at the end of each line before the quotation mark.

Thanks will correct.

Can't use Null as criteria, use Is Null.
"WHERE [Ready] = True AND Not ACCNT_CODE Is Null)"

Thanks will correct this too.

[June7]

Anything within quote marks will be interpreted by the SQL engine as a literal string. So it will look for a table named "tblDatato & tblDatafrom.CO_CODE " and fails not only because there isn't such a table but even if there were, there are spaces in the string. Names with spaces must be enclosed in [].

Must concatenate variables.

"INSERT INTO tblDatato" & tblDatafrom.CO_CODE & " " & _

Where are you running this code - behind a form? Is tblDatafrom.CO_CODE a field in the form's RecordSource?

[sw1085]

Hi - Now I get an error 424 - Object required.

The code sits under a button on a form.

Here the full code:

Private Sub Command156_Click()
Dim tName As String
Dim strSQL As String
tName = "tblSunDatabase" & tblTest.SUN_DB & " " & _
strSQL = "INSERT INTO " & tName & _
"SELECT SUN_DB, ACCNT_CODE, [NET AMOUNT], DESCRIPTN, JRNAL_LINE FROM " & _
"(SELECT SUN_DB, ACCNT_CODE, [NET AMOUNT], DESCRIPTN, 1 AS JRNAL_LINE " & _
"FROM tblTest" & _
"WHERE tblTest!Ready = True And Not [ACCNT_CODE] is Null And Not [NET AMOUNT] is Null" & _
"UNION ALL" & _
"SELECT SUN_DB, SUPP_CODE, [GROSS AMOUNT], DESCRIPTN, 2 AS JRNAL_LINE" & _
"FROM tblTest" & _
"WHERE tblTest!Ready = True And Not [SUPP_CODE] is Null And Not [GROSS AMOUNT] is Null" & _
"UNION ALL" & _
"SELECT SUN_DB, 271 AS ACCNT_CODE, VAT, DESCRIPTN, 3 AS JRNAL_LINE" & _
"FROM tblTest" & _
"WHERE tblTest!Ready = True AND Not ACCNT_CODE is Null)"
DoCmd.RunSQL strSQL
End Sub

Thanks again for your help.

SW

Anything within quote marks will be interpreted by the SQL engine as a literal string. So it will look for a table named "tblDatato & tblDatafrom.CO_CODE " and fails not only because there isn't such a table but even if there were, there are spaces in the string. Names with spaces must be enclosed in [].

Must concatenate variables.

"INSERT INTO tblDatato" & tblDatafrom.CO_CODE & " " & _

Where are you running this code - behind a form? Is tblDatafrom.CO_CODE a field in the form's RecordSource?

[June7]

The missing object is possibly tblTest.SUN_DB. Is this a field of form's RecordSource? What value should be retrieved by this reference?

tName = "tblSunDatabase" & tblTest.SUN_DB
strSQL = "INSERT INTO [" & tName & "] " & _

Still missing separator space at end of each line before the quote mark.

[sw1085]

Hi, thanks for the reply.

My data is stored in "tblTest" as follows:

SUN_DB ACCNT_CODE SUPP_CODE NAMOUNT GAMOUNT VAT JRNAL_REF JRNAL_LINE READY
ABC 440A10 275X75 120 100 20 YES
CDE 123B12 275X60 1200 1000 200 YES

I want to do the following in VBA code:

1. Insert into another table which is based on tblSundatabaseXXX. XXX is the field value from the above records. So in the first example I would first copy to tblSunDatabaseABC and for the second records I would copy to tblSunDatabaseCDE.

2. I would copy the first records in the following way:

ACCNT_CODE would go to the ACCNT_CODE in the the relevant tblSundatabaseXXX along with GAMOUNT which would link to AMOUNT in the relevant tblSundatabaseXXX and JRNAL_REF as 1 and WHERE the READY field is yes.

SUPP_CODE would go to the ACCNT_CODE in the the relevant tblSundatabaseXXX along with NAMOUNT which would link to AMOUNT in the relevant tblSundatabaseXXX and JRNAL_REF as 2 and WHERE the READY field is yes.

The number "271" would go to the ACCNT_CODE in the the relevant tblSundatabaseXXX along with VAT which would link to AMOUNT in the relevant tblSundatabaseXXX and JRNAL_REF as 3 and WHERE the READY field is yes.

The above may help give you some background to be able to assist me.

Thanks again for your patience.

Hi - Now I get an error 424 - Object required.

The code sits under a button on a form.

Here the full code:

Private Sub Command156_Click()
Dim tName As String
Dim strSQL As String
tName = "tblSunDatabase" & tblTest.SUN_DB & " " & _
strSQL = "INSERT INTO " & tName & _
"SELECT SUN_DB, ACCNT_CODE, [NET AMOUNT], DESCRIPTN, JRNAL_LINE FROM " & _
"(SELECT SUN_DB, ACCNT_CODE, [NET AMOUNT], DESCRIPTN, 1 AS JRNAL_LINE " & _
"FROM tblTest" & _
"WHERE tblTest!Ready = True And Not [ACCNT_CODE] is Null And Not [NET AMOUNT] is Null" & _
"UNION ALL" & _
"SELECT SUN_DB, SUPP_CODE, [GROSS AMOUNT], DESCRIPTN, 2 AS JRNAL_LINE" & _
"FROM tblTest" & _
"WHERE tblTest!Ready = True And Not [SUPP_CODE] is Null And Not [GROSS AMOUNT] is Null" & _
"UNION ALL" & _
"SELECT SUN_DB, 271 AS ACCNT_CODE, VAT, DESCRIPTN, 3 AS JRNAL_LINE" & _
"FROM tblTest" & _
"WHERE tblTest!Ready = True AND Not ACCNT_CODE is Null)"
DoCmd.RunSQL strSQL
End Sub

Thanks again for your help.

SW

[sw1085]

Hi guys:

Could someone please help modify the code below so that the INSERT INTO can be based on a dynamic table name. The beginning of the table name will always be tblSundatabase but the last three letters will be based on the field in the table of the records I am appending to this table. The field is tblTest.SUN_DB.

The code works well per the below though so just need this part added and I'm almost there.

Thanks very much for your help.

SW

Private Sub Command156_Click()
Dim strSql As String
strSql = "INSERT INTO tblSundatabaseABC " & vbCrLf & _
"SELECT SUN_DB AS SUN_DB, ACCNT_CODE AS ACCNT_CODE, NAMOUNT AS AMOUNT, DESCRIPTN AS DESCRIPTN, JRNAL_LINE AS JRNAL_LINE, JRNAL_NO AS JRNAL_NO, JRNAL_TYPE AS JRNAL_TYPE, ANAL_T3 AS ANAL_T3, ANAL_T8 AS ANAL_T8 " & vbCrLf & _
"FROM (SELECT SUN_DB, ACCNT_CODE, NAMOUNT, DESCRIPTN, 1 AS JRNAL_LINE, JRNAL_NO, JRNAL_TYPE, ANAL_T3, ANAL_T8 " & vbCrLf & _
"FROM tblTest  " & vbCrLf & _
"WHERE [Ready] = True AND [ACCNT_CODE] IS NOT NULL AND NAMOUNT IS NOT NULL  " & vbCrLf & _
"UNION ALL  " & vbCrLf & _
"SELECT SUN_DB, SUPP_CODE, GAMOUNT, DESCRIPTN, 2 AS JRNAL_LINE, JRNAL_NO, JRNAL_TYPE, """" AS ANAL_T3, """" AS ANAL_T8 " & vbCrLf & _
"FROM tblTest  " & vbCrLf & _
"WHERE [Ready] = True AND [SUPP_CODE] IS NOT NULL AND GAMOUNT IS NOT NULL  " & vbCrLf & _
"UNION ALL  " & vbCrLf & _
"SELECT SUN_DB, 271 AS ACCNT_CODE, VAT, DESCRIPTN, 3 AS JRNAL_LINE, JRNAL_NO, JRNAL_TYPE, ANAL_T3, ANAL_T8 " & vbCrLf & _
"FROM tblTest  " & vbCrLf & _
"WHERE [Ready] = True AND VAT IS NOT NULL)  AS [%$##@_Alias];"
Debug.Print strSql
DoCmd.SetWarnings False
DoCmd.RunSQL (strSql)
DoCmd.SetWarnings True
End Sub

[June7]

How is the query to know which record the value should come from? Are you trying to insert records to more than one destination table in one action?

The value could be obtained by reference to a control on the form. The destination could be only one table.

This is a continuation of your other thread and I have merged them.

[sw1085]

Yes that is what I am trying to do. The field called "SUN_DB" matches the last three letters on the database table.

So if i have a record with SUN_DB code of ABC then it would go to the ABC table and if I have a record with CDE then that would go the table ending CDE.

That's what I am struggling to get working. But the code works well to enter into one table.

Thanks for your time.

[June7]

An action sql (INSERT, UPDATE, DELETE, DROP, etc) can have only one destination table or query. ALL records from the nested SELECT UNION statement will be inserted into one destination.

If you want to INSERT records to various tables, need to run the SQL inside a loop that will change the destination table 'XXX' suffix in each loop. The records selected for the INSERT will have to be filtered to only those relevant to the table determined in each loop.

Beginning to sound like you have multiple tables where should only be one.

The vbCrLf is totally unnecessary.

[sw1085]

We do have multiple tables but I cannot change those. If I could do any of this in an easier way I would. The tables where the records from tblTest are being transferred to are for our accounting system which has its own front end. We're creating this transaction entry database as an add-on for it.

Can you help with the SQL loop to achieve what I'm trying to do? Alternatively I guess the easiest solution would be to create two append queries but it would really be best to get the thing looping through each entry and working out which table it should go to.

Please let me know if this is something you can help with. I'd be happy to post by sample db.

SW

An action sql (INSERT, UPDATE, DELETE, DROP, etc) can have only one destination table or query. ALL records from the nested SELECT UNION statement will be inserted into one destination.

If you want to INSERT records to various tables, need to run the SQL inside a loop that will change the destination table 'XXX' suffix in each loop. The records selected for the INSERT will have to be filtered to only those relevant to the table determined in each loop.

Beginning to sound like you have multiple tables where should only be one.

The vbCrLf is totally unnecessary.

[June7]

You want to loop through the records of the UNION query? This gets more complicated. Must open a recordset. Something like:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
strSQL = "SELECT your UNION sql here;"
rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic
While Not rs.EOF
CurrentDb.Execute "INSERT INTO tblSundatabase" & SUN_DB & "(SUN_DB, ACCNT_CODE, NAMOUNT, DESCRIPTN, JRNAL_LINE, JRNAL_NO, JRNAL_TYPE, ANAL_T3, ANAL_T8) " & _
"VALUES('" & rs!SUN_DB & "', '" & rs!ACCNT_CODE & "', '" & rs!NAMOUNT & "', '" & rs!DESCRIPTN & "', '" & rs!JRNAL_LINE & "', '" & rs!JRNAL_NO & "', '" & rs!JRNAL_TYPE & "', '" & rs!ANAL_T3 & ", '" & rs!ANAL_T8 & "')"
rs.MoveNext
Wend

Assumes all fields are text type.

It just finally hit me you are building a UNION query in VBA. Will be interesting if code works. The only time I tried to programmatically work with a UNION query failed. VBA would not work with a UNION query I built as an Access saved query. Nor would it work with any query that had UNION query anywhere in the sequence of queries. I had to rewrite the VBA procedure to not rely on UNION.

It is not necessary to use the As alias in the first UNION line if you are not changing the fieldname and also don't need the As alias in the subsequent UNION lines.

[sw1085]

Thanks for coming back to me:

What would I put in here: strSQL = "SELECT your UNION sql here;"

[June7]

Use the nested SELECT ... part of the INSERT SQL you show in your earlier posts. I just didn't want to repeat it all.