Write Access Form Data to SQL Server using ADO

[bconner]

Hi all,
This is my first time trying to set up Access Front End with SQL Server Back End. How can I write the Data on my Access form to a Linked SQL Server table? I tried the below code but get runtime error '3622'

Private Sub Cmd_Coding_Save_And_Enter_New_Record_Click()
Dim Rs As Recordset
 
Set Rs = CurrentDb.OpenRecordset("Tbl_Coding", dbOpenDynaset)
Rs.AddNew
Rs![Date Of QC] = Coding_txt_Date_Of_QC
Rs![Auditor] = Coding_cmb_Auditor
Rs![Coder] = Coding_cmb_Coder
Rs![Date Of Entry] = Coding_txt_Date_Of_Entry
Rs![Accession] = Coding_txt_Accession
Rs![Coding Error Name] = Coding_cmb_Coding_Error_Name
Rs![Number Of Errors] = Nz(Coding_txt_Number_Of_Errors, 0)
Rs![QC Trained] = Coding_cmb_QC_Trained
Rs![Error Fixed] = Coding_cmb_Error_Fixed
Rs![Comments] = Coding_txt_Comments
Rs.Update
Rs.Close
Set Rs = Nothing
 
'After Update Clear the Selected Coding Form fields
Coding_cmb_Auditor = Null
Coding_cmb_Coder = Null
Coding_txt_Date_Of_Entry = Null
Coding_txt_Accession = Null
Coding_cmb_Coding_Error_Name = Null
Coding_cmb_QC_Trained = Null
Coding_cmb_Error_Fixed = Null
Coding_txt_Comments = Null
 
End Sub

 

[boblarson]

First off - make sure that you declare the recordset properly.

Dim Rs As Recordset


Should be either

Dim Rs As DAO.Recordset

or

Dim Rs As ADODB.Recordset

depending on which method you want to use. With a linked table it is fine to use DAO and I would and you also need to if you are using the CurrentDb.OpenRecordset method as that is DAO.

 

[bconner]

Thanks Bob, I went with the DAO option you suggested since it didn't require much alteration to my code. I want to learn more about ADO and how to write data to SQL Server using it, can you recomend any good sources?