append query with visual basic

[PC User]

I'm trying to execute an append query (qapdCopy&PasteProperties) with
visual basic, but I get an error.
Run-time error ''
Syntex error in INSERT INTO statement.
================================================
Private Function Duplicate()

Dim strInsert As String, strFrom As String
Dim sstrWhere As String, Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb

strInsert = "INSERT qapdCopy&PasteProperties.* "
strFrom = "FROM qapdCopy&PasteProperties "
strWhere = "WHERE qapdCopy&PasteProperties.ChemicalID = " & [Forms]!
[frmMain]![ctlGenericSubform].[Form]![ChemicalID]
strSQL = strInsert & strFrom & strWhere

db.Execute strSQL, dbFailOnError

End Function
================================================

I'm not familiar with putting an append query into visual basic. Can
someone help?

Thanks,
PC

 

[Allen Browne]

Simplest way to do this is to use the query design window to mock up a query
that selects from the table you want, and inserts into the table you want.
Use any old value as the criteria under ChemicalID, just so you get to see
the WHERE clause. Then switch to SQL View for an example of what you need.

Suggestions:
1. You are missing the word INTO in the INSERT clause.

2. You are missing the SELECT clause.

3. If ChemicalID is a Text field (not a Number field), you need more quotes.
http://allenbrowne.com/casu-17.html

4. If it is Number, test to make sure it's not null.

5. If it still fails, Debug.Print the sql string. Then open the Immediate
Window (Ctrl+G) and compare what you generated with the mock query above.

6. I'm not sure about the use of the wildcard to duplicate all fields in the
same table. If there is a primary key in the table (as there should be),
this will fail.

You will end up with something like this

strSql = "INSERT UNTO qapdCopy&PasteProperties.* " & vbCrLf & _
"SELECT qapdCopy&PasteProperties.* " & vbCrLf & _
"FROM qapdCopy&PasteProperties " & vbCrLf & _
"WHERE qapdCopy&PasteProperties.ChemicalID = " & _
Nz([Forms]![frmMain]![ctlGenericSubform].[Form]![ChemicalID],0)
Debug.Print strSql

Finally, a little utility to help you copy the SQL statement from the mock
query into your VBA code:
http://allenbrowne.com/ser-71.html

 

[Jeanette Cunningham]

Hi,
the syntax posted is not quite right.
To help with the syntax, create an append query in the query designer. Make
it a simple append query.
Switch to sql view and see how access does the SQL.

You will find that it starts with INSERT INTO followed by the table name,
followed by, in brackets, the name of each field you wish to append to.
You can also look up help on queries and select append queries.
If you have further questions, post back here.

Jeanette Cunningham

 

[John Spencer]

In addition to what others have said, I think that your query name
aAPDCopy&PasteProperties will cause problems since it contains an ampersand
(&). The Ampersand could be interpreted as the concatenation operator. Try
surround the query name with square brackets -[aAPDCopy&PasteProperties] -
in all cases where you are using it.

strInsert = "INSERT INTO [qapdCopy&PasteProperties].* "
strFrom = " SELECT * FROM [qapdCopy&PasteProperties] "
strWhere = " WHERE [qapdCopy&PasteProperties].ChemicalID = " &
[Forms]![frmMain]![ctlGenericSubform].[Form]![ChemicalID]

This whole query looks suspicious to me. What are you trying to do? If you
are trying to duplicate an existing record, I think you will get an error if
the record has a primary key (which all tables should have). By definition
you cannot have duplicate values in a primary key.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[PC User]

Thanks to all of you for your suggestions. To explain my query a
little more, I didn't post it, because the SQL is long. There a lot
of fields; however, I did design it to append all fields except the
primary key field. I left the append parameter for the key field out,
but the key field is still in it for the query to identify which
record that I want to duplicate. The key field is ChemicalID. I've
seen VB code for SQL statements use the name of the query instead of
the lenghtly SQL code in a large query, so that's what I was trying to
do in my code.

The code is intended to grab the ChemicalID of the current record and
copy all fields, except the key field, and paste them into a new
record. Sometimes I get different concentrations of the same chemical
or the waste of the same chemical; so I don't want to manualy copy all
the data into the new fields to add the properties of the same
chemical at a different concentration. After this functioncopies all
the fields, then all I need to change is the name of the chemical
indicating a different concentration. I've seen the need for this in
other parts of my application too.

I put brackets arround my query's name and ran the code. This is the
result of Debug.Print strSQL:

INSERT INTO [qapdCopy&PasteProperties].* FROM
[qapdCopy&PasteProperties] WHERE [qapdCopy&PasteProperties].ChemicalID
= 243

In Allen's utility, my SQL code is very large. However, I like the
utility.

Thanks to all. I'll use your suggestions and get back to you.

PC