Add a record to an SQL Table

[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

 

[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 <--

 

[richardb]

Stefan,

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

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 <--

 

[Stefan Hoffmann]

hi Richard,

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

Are you using a .mbd or .adp?

mfG
--> stefan <--

 

[richardb]

..mdb

hi Richard,

Are you using a .mbd or .adp?

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Richard,

.mdb

So link the table or use ADO.Connection.


mfG
--> stefan <--