Syntax error near ')'

[beanlucy]

I'm receiving the above error when running this code, can anyone tell me why it's not working?


Set rstBook = GetRecordsetFromSQL("INSERT INTO jmr101.evttblPlacement ( perID, evtID, evtRoleAtEvent, evtNumPlaces, pmtDateAdded, pupYearGroup ) " & _
" VALUES (" & rstImportThese![ID] & "," & _
intEvtID & "," & _
"'" & "203" & "'" & _
",1,GETDATE(), " & rstImportThese![YearGroupID] & " ) ", adOpenForwardOnly, adLockOptimistic)

ThrowawayRecordset rstBook

 

[DCrake]

Are you trying to use SQL syntax commands in Access?
Which ) is it referring to?

David

 

[beanlucy]

I'm afraid I'm not sure which ) it's referring to - is there an easy way to find that out?

Sorry not to be more informative but this was written for us sometime ago and I'm not that familiar with VBA yet.

 

[namliam]

GetRecordsetFromSQL I dont know that function? self writen?

You cannot "get recordset" from a Insert into statement.

 

[beanlucy]

The function is defined here:

' get a recordset object based on a query
Public Function GetRecordsetFromSQL(strSQL As String, CursorType As ADODB.CursorTypeEnum, LockType As ADODB.LockTypeEnum) As ADODB.Recordset
On Error GoTo Errorhandler
'AddErrorHandlerComment "GetRecordsetFromSQL(" & strSQL & ", " & CursorType & ", " & LockType & ")"
'MsgBox strSQL
Dim cnConnection As ADODB.Connection
Dim rstRecordset As New ADODB.Recordset
Set cnConnection = CurrentProject.Connection
'MsgBox Len(strSQL)
rstRecordset.Open strSQL, cnConnection, CursorType, LockType, adCmdText
Set GetRecordsetFromSQL = rstRecordset
Exit_Function:
'Set GetRecordsetFromSQL = Nothing
Exit Function
Errorhandler:
MsgBox "Error in GetRecordsetFromSQL function: " & vbCrLf & "Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description
Resume Exit_Function
End Function

 

[namliam]

exactly... it tries to open a recordset (get recordset) you cannot open a "Insert into" query.... you have to execute it

 

[beanlucy]

This method works in previous areas of the code though so I can't work out why this is any different, for example:

Set rstUpdate = GetRecordsetFromSQL("INSERT INTO outtblPeople (perType, perStatus, perSurname, perFirstName, perTitle, perGender, perDateAdded, perSource, perBatchNum, perOldID) " & _
"VALUES (" & perTypePupil & ", " & outStatusActive & "," & _
SQLStringWithNull(rstImportThese![Sname]) & "," & _
SQLStringWithNull(rstImportThese![Fname]) & "," & _
SQLStringWithNull(rstImportThese![TITLE]) & "," & _
Nz(rstImportThese!GenderID, 180) & _
", GETDATE()," & _
outSchemeEvents & _
"," & intBatchNum & "," & rstImportThese![ID] & ") ", adOpenForwardOnly, adLockOptimistic)

ThrowawayRecordset rstUpdate

 

[boblarson]

There is no reason to use the GetRecordsetFromSQL with an Action query. You simply execute it:

CurrentDb.Execute "YourSQLStringHere", dbSeeChanges ' for use with SQL Server if that is what you are linked to.

 

[namliam]

Insert into doesnt create a recordset therefor it should not work. I must admitt I am not 100% sure on this so if you say it works... Then I guess it does...

Even after re-writing this into a more readable thing:

mySQL = ""
mySQL = mySQL & " INSERT INTO jmr101.evttblPlacement ( perID, evtID, evtRoleAtEvent, evtNumPlaces, pmtDateAdded, pupYearGroup ) "
mySQL = mySQL & " VALUES (" & rstImportThese![ID] & ","
mySQL = mySQL & intEvtID & "," 
mySQL = mySQL & "'" & "203" & "'" 
mySQL = mySQL & ",1"
mySQL = mySQL & ",GETDATE()"
mySQL = mySQL & "," & rstImportThese![YearGroupID] & " ) "

I dont see a problem, only problem there could be -which is handled in your other code I think- are null values which could throw a problem into the statement.

I suggest you make sure to handle nulls and see if that is the problem, otherwize try inserting a Debug.Print to write the statement to the debug window and see what is wrong.

 

[WayneRyan]

It sure seems like T-SQL syntax as opposed to JET.

The whole date thing looks bad to me.

mySQL = mySQL & ",GETDATE()"

GetDatate is a SQL Server native function.
There are no "#" delimiters surrounding the date.

I'd paste the contents of mySQL into a query and get a more definitive
definition of the error.

Maybe a sample DB?

Wayne

 

[namliam]

GetDate() is SQL Server = Now(), this code is SQL Server compliant... The only problem I can see is... OpenRecordset, you cannot open the recordset of an action query as far as I know.

But the OP seems to be sure that it is working elsewhere, thus I am lost.