Add a record to an SQL Table

R

richardb

Have learned much programming here 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
 
S

Stefan Hoffmann

hi Richard,
Have learned much programming here 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:
You are mixing the recordset and SQL approach.
strConnect = "ODBC;DSN=PPM_700;;" & _
"Network=DBMSSOCN;Trusted_Connection=Yes"
Is this a SQL Server?

The easiest way is to create a linked table and use:

a) CurrentDb.Execute "INSERT INTO linkedTable ..."

or

b) Set rs = CurrentDb.OpenRecordset("linkedTable")
rs.Add
rs![Fields] = Values
rs.Update


mfG
--> stefan <--
 
R

richardb

Stefan,

This would be an SQL database, so the table is not linked.

Stefan Hoffmann said:
hi Richard,
Have learned much programming here 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:
You are mixing the recordset and SQL approach.
strConnect = "ODBC;DSN=PPM_700;;" & _
"Network=DBMSSOCN;Trusted_Connection=Yes"
Is this a SQL Server?

The easiest way is to create a linked table and use:

a) CurrentDb.Execute "INSERT INTO linkedTable ..."

or

b) Set rs = CurrentDb.OpenRecordset("linkedTable")
rs.Add
rs![Fields] = Values
rs.Update


mfG
--> stefan <--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top