Inserting Record into SQL database

[richardb]

I would be very grateful for help in debugging this sub routine which is
supposed to add a record to an SQL table. Running this gives me Error 3219
(Invalid Operation). The error occurs at the line that includes "Set myrs =
..OpenRecordset"

I can take the SELECT string (with values in the two variables) and put it
in a Pass Through query and it writes the new record to my table. However, I
don't understand my error in the following sub routine. Many thanks for the
help...

Public Sub PutChargeBilling(sequence As Long, TrType As Integer)
On Error GoTo PutChargeBilling_Error
Dim mydb As DAO.Database
Dim myq As DAO.QueryDef
Dim myrs As DAO.Recordset
Dim strType As String
Dim sqltext As String, strConnect As String
strType = CStr(TrType)

Set mydb = CurrentDb
Set myq = mydb.CreateQueryDef("")

strConnect = "ODBC;DSN=PPM_700;;Network=DBMSSOCN;
Trusted_Connection=Yes"

sqltext = "INSERT INTO StmtBilling (seq_number, bill_date, TrxType,
ts_user) " & _
"SELECT " & sequence & ", SUBSTRING(CONVERT(char,GETDATE(), 101), 1,
10), '" & strType & "', 'username' ;"

With myq
.Connect = strConnect
.ReturnsRecords = False
.SQL = sqltext
Set myrs = .OpenRecordset
End With

myrs.Close
End
PutChargeBilling_Exit:
myq.Close
Set myq = Nothing: Set myrs = Nothing: Set mydb = Nothing
sqltext = vbNullString: strConnect = vbNullString
Exit Sub

PutChargeBilling_Error:
Select Case Err.Number
Case 3151
MsgBox "Practice Information not found due to ODBC connection
failure." & vbCrLf & _
"Open Edit File Locations and Correct DSN", _
vbInformation, Err.Number & " - " & Error$
Case Else
MsgBox Err.Number & " - " & Err.description & _
" - Sequence " & sequence & ", Type " & strType,
vbInformation, "PutChargeBilling"
End Select
Resume PutChargeBilling_Exit


End Sub

 

[Douglas J. Steele]

You've set ReturnsRecords to False (which is appropriate, since you're doing
an Insert Into). That means there's nothing to put into a recordset!

Replace

Set myrs = .OpenRecordset

with

.Execute, dbFailOnError

 

[richardb]

That was the answer, and was insight I'll keep in mind. I needed to omit the
comma from your example. Thank you. It's not the first time you've helped me.

Richard

 

[Douglas J. Steele]

You're welcome. Sorry about the comma: my mind was on the Execute method of
the Database object.