Add record to SQL table

[rmbennett]

Have learned much Access to SQL programming from forums but don't yet know how to add a record to an SQL table from Access. Here is my attempt, but am really guessing after a certain point and would appreciate help:

Public Function InsertEpisode(PatientID As String, RefPlanNo As Integer)

On Error GoTo ErrorHandler
Dim mydb As DAO.Database
Dim myq As DAO.QueryDef
Dim myrs As DAO.Recordset
Dim sqltext As String, strConnect As String, strField As String
Set mydb = CurrentDb
Set myq = mydb.CreateQueryDef("")

sqltext = "INSERT INTO Episode (PatUniqueID, FinanceClass, " & _
"primary_complaint, RefPlanNumber, epsd_date, ts_user) " & _
"SELECT '" & PatientID & "', FinanceClass, " & _
"'Created by Scan', CONVERT(varchar, " & RefPlanNo & "), GetDate(),
" & _
"'Import' FROM Patient WHERE PatUniqueID = '" & PatientID & "' ;"

strConnect = "ODBC;DSN=PPM_700;;" & _
"Network=DBMSSOCN;Trusted_Connection=Yes"
With myq
.Connect = strConnect
.SQL = sqltext
Set myrs = .OpenRecordset
End With

With myrs <------HERE I really don't know!!!
.AddNew
.Update
.Close
End With

InsertEpisode_Exit:
Set myrs = Nothing
myq.Close
Set myq = Nothing: Set mydb = Nothing
Exit Function

ErrorHandler:
MsgBox Err.Number & " - " & Err.Description, vbCritical, "Error in
InsertEpisode"
Resume InsertEpisode_Exit

End Function

 

[realvilla]

Maybe I'm missing something, but why don't you just link the SQL (episode?) table using ODBC (File / Get External Data / Link) and append into it that way? You could still use code to update it. You don't need the myrs variable for the recordset. Just run the SQL string for the append query using docmd.runSQL. Your WHERE clause will assure that only one record is inserted. You may want to turn warnings off for the append. I hope this helps.