Insert a record using ADO 1

[HomeGrowth]

I try to insert a record into a table (only 2 fields). At the cn.Execute, I have this run-time error ‘-2146217900(80040e14)’: Syntax error in INSERT INTO Statement.

I think that I am missing something in function, can you help me out?

Function CreateRefNo(sReportNumber As String, sRefNo As String) As Boolean

Dim sSql As String
Dim cn As ADODB.Connection

Set cn = CurrentProject.Connection ' This establishes the connection

sSql = "INSERT INTO tbl(2)ReferenceReportNo ( [ReportNumber], [RefReportNumber] ) VALUES ('" & sReportNumber & "','" & sRefNo & "');"

cn.Execute (sSql)

Exit_CreateRefNo:
cn.Close
Exit Function

Err_CreateRefNo:
MsgBox Err.Number & " ' " & Err.Description
Resume Exit_CreateRefNo

End Function

 

[PHV]

You may try this:
sSql = "INSERT INTO [!][[/!]tbl(2)ReferenceReportNo[!]][/!] ( [ReportNumber], [RefReportNumber] ) VALUES ('" & sReportNumber & "','" & sRefNo & "');"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HomeGrowth]

Right on. I can go to sleep now. Thanks!

 

[Andrzejek]

If your table is not going to change, you may skip ( [ReportNumber], [RefReportNumber] ) part in your INSERT SQL.

sSql = "INSERT INTO [tbl(2)ReferenceReportNo] VALUES ('" & sReportNumber & "','" & sRefNo & "');"

Have fun.

---- Andy