How to insert several fields into a table using AD

Question

I have an ado created recordset in access 2010 it returns 9 different fields from a stored procedure on sql server 2008 r2.

I am trying to use this recordset (which does populate) to insert all of the records into a table that matches the output. My issue is that two of the fields are name fields that have commas in them. For example Smith, Joseph-- I need to insert that comma into the appropriate field. Right now it throws an error because of the comma in the field.

Here is the code that I am using:

Option Compare Database

'Executes the filtering routine
Private Sub cmdApplyFilter_Click()

'If txtStartDate.Value And txtEndDate.Value Is Not Null Then
'    QuickFilter
'Else
'    DefaultRun
'End If
QuickFilter
'********** Filter as you type **********

'Private Sub txtFilter_Change()
'   QuickFilter
'End Sub


End Sub

'Perform the actual filtering on the subform
Private Sub QuickFilter()

Dim Sql As String
Dim filter As String

If txtStartDate = vbNullString Then
    'Reset the filter if the textbox is empty
    'This will be the default sql statement to fill the subreport
    SubForm.Form.FilterOn = False
Else
    'Some common substitutions that users may have already inserted as wildchars
    filter = Replace(txtStartDate, "%", "*")
    filter = Replace("*" & filter & "*", "**", "*")
    'Construct the filter for the sql statement
    '/*********** GROUP BY GOES HERE ***********/

    'Assign the filter to the subform
    'SubForm.Form.filter = Sql
    'SubFomr.Form.FilterOn = True
End If
End Sub


Private Sub Form_Load()
   'Sets up the connection with the sql server database retrieves the stored procedure,       executes it and puts the result set into a table
   Dim Conn As ADODB.Connection
   Dim Cmd As ADODB.Command
   Dim Rs As ADODB.Recordset
   Dim rs1 As ADODB.Recordset
   Dim Connect As String
   Dim filter As String


   Connect = "Provider =SQLNCLI10; Data Source=10.50.50.140; Initial Catalog=CCVG; User Id = oe; Password = Orth03c0; "

   'Establish the connection with sql server
   Set Conn = New ADODB.Connection
   Conn.ConnectionString = Connect
  Conn.Open

   'Open the recorset
   Set Cmd = New ADODB.Command
   Cmd.ActiveConnection = Conn
   Cmd.CommandText = "dbo.cusGenNoNotesReport"
   Cmd.CommandType = adCmdStoredProc

   Set Rs = Cmd.Execute()








   Dim x As Integer

   If Not Rs.BOF And Not Rs.EOF Then
    If Not Rs.BOF Then Rs.MoveFirst
    Do Until Rs.EOF
        For x = 0 To Rs.Fields.Count - 1
            MsgBox Rs.Fields(x)
            'DoCmd.RunSQL "INSERT INTO tblNoNotes (Provider, Facility, TicketNumber,       Charges, FinancialClass, CPT, CPTDescription, PatientFullName, DateOfEntry) SELECT " & Rs.Fields(x).Value & ""
        Next x
            Rs.MoveNext
    Loop
End If


   'Process results from recordset, then close it.
   'DoCmd.RunSQL "INSERT INTO tblNoNotes (Provider, Facility, TicketNumber, Charges,     FinancialClass, CPT, CPTDescription, PatientFullName, DateOfEntry) VALUES (""" & Rs![Provider] & """,""" & Rs![Facility] & """ & Rs![TicketNumber] & """, """ & Rs![Charges] & """, """ & Rs![FinancialClass] & """, """ & Rs![CPT] & """, """ & Rs![CPTDescription] & """, """ & Rs![PatientFullName] & """, """ & Rs![DateOfEntry] & """ )"

   Rs.Open
   Rs.Close
   Conn.Close
   Set Rs = Nothing
   Set Cmd = Nothing
   Set Conn = Nothing


End Sub

Answer 1

You have an ADO Recordset, Rs, which contains data you want to add to your Access table. Instead of trying to fix the INSERT statement to add each row, it should be easier to open a DAO Recordset for the destination table and store the values from each ADO row by adding a new row the the DAO Recordset. Although this is still a RBAR (row by agonizing row) approach, it should be significantly faster than building and executing an INSERT statement for each row.

First of all, make sure to add Option Explicit to your module's Declarations section.

Option Compare Database
Option Explicit

Then use this code to append the ADO Recordset data to your table.

Dim db As DAO.Database
Dim rsDao As DAO.Recordset
Set db = CurrentDb
Set rsDao = db.OpenRecordset("tblNoNotes", _
    dbOpenTable, dbAppendOnly + dbFailOnError)

Do While Not Rs.EOF
    rsDao.AddNew
    rsDao!Provider.Value = Rs!Provider.Value
    rsDao!Facility.Value = Rs!Facility.Value
    rsDao!TicketNumber.Value = Rs!TicketNumber.Value
    rsDao!Charges.Value = Rs!Charges.Value
    rsDao!FinancialClass.Value = Rs!FinancialClass.Value
    rsDao!CPT.Value = Rs!CPT.Value
    rsDao!CPTDescription.Value = Rs!CPTDescription.Value
    rsDao!PatientFullName.Value = Rs!PatientFullName.Value
    rsDao!DateOfEntry.Value = Rs!DateOfEntry.Value
    rsDao.Update
    Rs.MoveNext
Loop

rsDao.Close
Set rsDao = Nothing
Set db = Nothing

Note this approach means you needn't worry about whether PatientFullName contains a comma, or apostrophe ... or struggle with properly quoting field values to produce a valid INSERT statement. You simply store the value from one recordset field to the appropriate field in another recordset.

Answer 2

I think the real problem you're complaining about here is that your data in the ADO Recordset has quotes (sometimes called apostrophes) in it. Anytime quotes could possibly exist in your data you will need to check for and escape them before using the data in an SQL Statement. You will need to know this not only for inserts but also for performing filtering and creating WHERE statements as well. For example:

Replace(Rs![PatientFullName], "'", "''")

A simpler way to do this is to make your own little function. The "PQ" stands for pad quotes. You can name it whatever you want.

PQ(rs![PatientFullName])

Public Function PQ(s as String) as String
    PQ = Replace(s, "'", "''")
End Function

But I also agree with HansUp that it's much easier to use recordsets for inserts. I basically never use SQL Insert statements anymore, except for places where I have no option such as SQL Server T-SQL.

Be aware that if you do want to use insert statements, you should consider using the following:

CurrentDb.Execute "INSERT INTO Statement Goes Here", dbFailOnError

This is considered to be a more robust solution than DoCmd.RunSQL, mostly because it runs in the context of the underlying Database Engine instead of the Access interface. Using CurrentDb.Execute prevents you from having to use DoCmd.SetWarning statements to turn off warnings.