Error Message when trying to execute a SQL Stored Procedure from f

[Guest]

The error message I get is:

Run-time error '-2147217900 (80040e14)':
INSERT failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'

Here is the code I am using:
Dim strname As String
Dim lngcount As Long
Dim lngDl As Long
Dim TableName As String
Dim UserID As String
Dim SpecJob As String
Dim JobDesc As String
Dim JobCity As String
Dim chrJobState As String
Dim Modifiedby As String
Dim CompNo As String
Dim Server As String
Dim rs1 As ADODB.Recordset
Dim cn1 As ADODB.Connection

TableName = "job"
SpecJob = Job
UserID = [Forms]![Link]![LinkSpecialty]![User]
JobDesc = [Forms]![Link]![LinkSpecialty]![JobDesc]
chrJobState = "IL"
Modifiedby = txtuser
CompNo = "01"
Server = "BARRFILE1"

Set cn1 = New ADODB.Connection
cn1.ConnectionString = "ODBC;DATABASE=JCS;DSN=JCSDEVSERV;
UID=sa;PWD=2techy4u!"
cn1.CommandTimeout = 600
cn1.Open
Set rs1 = cn1.Execute("InsertJob " & "'" & TableName & "','" &
SpecJob _
& "','" & UserID & "','" & JobDesc
& "','" & JobCity _
& "','" & chrJobState & "','" &
Modifiedby & "','" _
& CompNo & "','" & Server & "'")

rs1.Close
cn1.Close
Set rs1 = Nothing
Set cn1 = Nothing

I know there is a way to fix it is SQL but it is not my stored procedure so
I do not want to mess with it. Is there are way to resolve the issue from
the Access?

Thanks in advance

 

[Michel Walsh]

Hi,


Debug.print your statement rather than executing it, then cut and paste it
in MS SQL Analyzer (or other tool at your disposal) and see if the sproc
works, or not. The sproc itself may throw an error, due to the actual data
you pass to it, while the syntax is valid. The string that represent your
SQL data may also be more talkative, such as if there is a NULL value among
the argument... that generates

something, , something


while you may need

something, NULL, something



Hoping it may help,
Vanderghast, Access MVP