I have a SELECT query that does a search criteria then it takes those values and places them into an Archive table. So inside a loop while going through the recordset (rstRecSet) I insert the values into that Archive table.

My INSERT looks like this:

SQLInsert = "INSERT INTO Archive (PLAYERNUMB, LNAME, FNAME) " & _
" VALUES (" & rstRecSet("PLAYERNUMB") & ", '" & rstRecSet("LNAME") & "', '" & rstRecSet("FNAME") & "');"

but I get the following error when I hit a single quote in a persons name:

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''O'CONNELL', 'AMY');'.

It works perfectly fine except for when I hit the a name with an single quote in it.

I tried a few approaches to this (wrapped them with double quotes, used double single qutoes), but still nothing.

I know what my problem is, but just can't find the right solution. Any ideas?

the double single quotes will work

what's of more concern is the loop driving a single-row insert

why not run an INSERT SELECT and insert all the rows at once?

Ahh k, never realized you can do that with one statement. Thanks.

As far as the INSERT

' ' " & rstRecSet("LNAME") & " ' '

That should do the trick (used spaces so it isn't hard on the eyes) ?

with the INSERT SELECT, your're not going to be using rstRecSet

Thanks for the help, much appreciated. But one more question to bug you.

SQLInsert = " INSERT " & _
" INTO Archive " & _
" (PLAYERNUMB, LNAME, FNAME) " & _
" SELECT PLAYERNUMB, LNAME, FNAME " & _
" FROM Player " & _
" WHERE ACTIVE = FALSE;"

If I do a response.write ""&SQLInsert&""

INSERT INTO Archive (PLAYERNUMB, LNAME, FNAME) SELECT PLAYERNUMB, LNAME, FNAME FROM Player WHERE ACTIVE = FALSE;

No loops needed?

Sorry about this, never done an INSERT .. SELECT... before.

EDIT: Ok, tried the actual query right inside MS Access and it works great. So I just answered my own question there, no loops needed at all. Still not working properly in the ASP page, so I'm going to have to keep trying different approaches. Kind of curious though, cause when I did it in MS Access I got a promprt asking if I want to continue with the APPEND query. Wonder if ASP sees that prompt when I run the query or if it just ignores it.

Ok I got it, turns out that I'm just an idiot thats all.

Thanks ever so much for your help, much appreciated. Just need to fire off a query to delete the records that were moved in the original table to the archive table and I'll be set.

Thanks again.

my pleasure