SQL error but not when run in query

[Colin Coleman]

I have a strange one here,

I have a routine thhat gererates SQL "on the fly" to duplicate a record (except for the PK filed) in the same table.

Code:
Function CopytoSelf(iContract As Integer) As Integer
On Error GoTo Err_CopytoSelf
Dim StrStoreSQL As String
Dim adoCon As New ADODB.Connection
Dim adoRst As New ADODB.Recordset
Dim SSql As String
Dim sFields As String
Dim iPos As String
Dim iNewRec As Integer

Set adoCon = CurrentProject.Connection
SSql = "SELECT * FROM CONTRACTS ;"
sFields = ExtractFieldNames(SSql, iContract)
iPos = InStr(sFields, ",")
If iPos = 0 Then
CopytoSelf = -1
GoTo Exit_CopytoSelf
End If
sFields = Mid(sFields, iPos + 1, Len(sFields))
StrStoreSQL = "INSERT INTO contracts (" & sFields & ") SELECT " & sFields
StrStoreSQL = StrStoreSQL & " FROM contracts where contract = " & Str(iContract) '& ";"
adoRst.Open StrStoreSQL, adoCon
iNewRec = DMax("[contract]", "contracts", "")
CopytoSelf = iNewRec
Exit_CopytoSelf:
Exit Function
Err_CopytoSelf:
Me.baba.SetFocus
Me.baba.Text = StrStoreSQL
'& vbCrLf & "Created =" & Str(iNewRec)
MsgBox Err.Description, vbExclamation, "Error " & Err.Number & " in Copytoself "

CopytoSelf = -1
GoTo Exit_CopytoSelf
End Function


This generates the following SQL:
INSERT INTO contracts ( zemp, CONTRACT_N, ztype, TITLE, FIRST_NAME, SURNAME, ADD1, ADD2, ADD3, ADD4, ADD5, STORE_SHOP, TOWN, POSITION, START_DATE, CONTINUOUS, STATEMENT_, DOB, LEAVE_DATE, RATE_OF_PA, DAYS_HRS1, DAYS_HRS2, DAYS_HRS3, DAYS_HRS4, SALARY, COMMISSION, COMMISSIO2, MONTH, SEND_ME, AREA_MANAG, LETTER_DAT, SUNDAY_RAT, NOTICE_DUE, TRIAL, ETHNIC_ORI, MARITAL_ST, CHILD_1, CHILD_2, CHILD_3, CHILD_4, CHILD_5, DISABLED, STATUS, ISLOCKED, DAILYRATE, UKONLY, SICKPAY, OTHEREMP, HOURSDATA, CARDATA, HEALTHDATA, PENSIONDATA, PAYDATA, COMMDATA, COVDATA, DUTYDATA, MOBILEDATA, SHOWSICK, SHOWOE, SHOWCAR, SHOWHEALTH, SHOWPENSION, SHOWPAY, SHOWSTAKEHOLDER, SHOWCOMM, SHOWCOV, SHOWMOBILE, ZPAYTYP) SELECT zemp, CONTRACT_N, ztype, TITLE, FIRST_NAME, SURNAME, ADD1, ADD2, ADD3, ADD4, ADD5, STORE_SHOP, TOWN, POSITION, START_DATE, CONTINUOUS, STATEMENT_, DOB, LEAVE_DATE, RATE_OF_PA, DAYS_HRS1, DAYS_HRS2, DAYS_HRS3, DAYS_HRS4, SALARY, COMMISSION, COMMISSIO2, MONTH, SEND_ME, AREA_MANAG, LETTER_DAT, SUNDAY_RAT, NOTICE_DUE, TRIAL, ETHNIC_ORI, MARITAL_ST, CHILD_1, CHILD_2, CHILD_3, CHILD_4, CHILD_5, DISABLED, STATUS, ISLOCKED, DAILYRATE, UKONLY, SICKPAY, OTHEREMP, HOURSDATA, CARDATA, HEALTHDATA, PENSIONDATA, PAYDATA, COMMDATA, COVDATA, DUTYDATA, MOBILEDATA, SHOWSICK, SHOWOE, SHOWCAR, SHOWHEALTH, SHOWPENSION, SHOWPAY, SHOWSTAKEHOLDER, SHOWCOMM, SHOWCOV, SHOWMOBILE, ZPAYTYP FROM contracts where contract = 1061

This when it hits the open recordset line crases with the error:
"SYNTAX ERROR insert into statement"

If i cut and paste this sql directly into a Access Query, it runs and asks permission to insert 1 record as this is undoable...

My questions is, Why does this fail when running from the recordset component ?

Thanks
Colin

 

[WayneRyan]

Colin,

StrStoreSQL = StrStoreSQL & " FROM contracts where contract = " & Str(iContract) '& ";"

The single-quote doesn't show up in the SQL that you list at the bottom.

Wayne

 

[Colin Coleman]

Well done, you are correct, but unfortunatley my post of the SQL was "doctored" purley to see if the semicolon had any effect on the code, so yes sorry...


it works in the Access query either way, but not at all in the ado.recordset method.

why ?

 

[WayneRyan]

Colin,

Oh, you just commented out the semi-colon. Now, I see.

StrStoreSQL = StrStoreSQL & " FROM contracts where contract = " & Str(iContract) '& ";"

In that case, the only error that I can possibly see is that Contract is
not a NUMERIC field, but it sure seems that it is.

Maybe if you could post a sample DB ...

Wayne

 

[Colin Coleman]

Thanks for looking at it, I am a bit lost how to post an example, as my data is seperate from the code, can i just post the db/vb parts or will it just fall apart, the data files are about 100Mb.

The "contract" field is an autonumber field and is also the PK field for the table, hence when coping the old record to the new record i remove the contract field as this gets created automatically.

But i dont see why it works when i paste the sql into a text box on the form, then copy that to an Access query, run it...and it works. But always fails when it does the "adoRst.Open StrStoreSQL, adoCon" instruction

Arggghhhhh.....

All im trying to do is make a new record copy of an old record but with a new contract number (PK), as i guess (and hope) u cant change autonumber fields by hand... then delete the old record after copying it to the archive table.

?

 

[Colin Coleman]

Ok, dont know if this helps but i replaced the open call with this
DoCmd.RunSQL StrStoreSQL, False
and guess what....... it works as well !!!
So does the recordset component parse the sql by a different engine or summit ??
still drivin me crazy !!
??????