How to insert several fields into a table using ADO when two of the fields contain commas

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

It is how I created the recordset and I thought maybe it would have something to do with the answer. Just trying to be through — , Oct 08 '13 at 20:54
Sorry, I'm lost. I don't understand how you're using ADO.Net and Access VBA together. It may help to show us code so we can see how you're attempting to add data from the recordset to the table. (BTW, the downvote was not from me.) — HansUp, Oct 08 '13 at 20:58
I added the code that I used both to create the recordset and the attempted insertion to the table. I am not worried about the downvote perhaps the question wasn't too clear without the code — , Oct 08 '13 at 21:05
I still don't see any sign of ADO.Net in that code. But what happens? Is there an error? Does it not throw an error but doesn't add anything to the table either? — HansUp, Oct 08 '13 at 21:10
What happens with this code is that the insert into fails because two of the fields have commas in them. The exact error is "Run-time error '3075': Syntax error (missing operator) in query expression 'Jenkins MD'. " It then highlights this line'DoCmd.RunSQL "INSERT INTO tblNoNotes (Provider, Facility, TicketNumber, Charges, FinancialClass, CPT, CPTDescription, PatientFullName, DateOfEntry) SELECT " & Rs.Fields(x).Value & ""' — , Oct 08 '13 at 21:15
Sorry I forgot to answer part of your question: It does not load the table either — , Oct 08 '13 at 21:18

HansUp · Accepted Answer · 2013-10-11T19:08:55.940

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.

+1 for the "Git 'er done" approach, and for "RBAR" (I'll be stealing that expression...). — Gord Thompson, Oct 08 '13 at 21:47
I can't claim credit for that one, Gord, but I like it too. I thought there was a Wikipedia article about RBAR, but all I can find now is [this](http://en.wikipedia.org/wiki/List_of_acronyms:_R#RB) — HansUp, Oct 08 '13 at 21:52
Thank you Hans! This is a great solution that works perfectly!! — , Oct 09 '13 at 14:25

HK1 · Answer 2 · 2013-10-09T02:39:50.780

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.

Good points, Harlan. If he wants to stick to `INSERT` statements, I think he should use a parameter query as a `DAO.QueryDef`, supply the parameter values, and execute the `QueryDef`. That way the quoting change would go away. — HansUp, Oct 09 '13 at 01:46
@HansUp Is it possible to supply values to DAO parameter queries in code? I can't say I've ever seen this done. I was under the impression for some reason that you have to use ADO if you want real Parameter Queries. — HK1, Oct 09 '13 at 02:18
Sure. See [this SO answer](http://stackoverflow.com/a/15772749/77335) for an example. You don't actually need the explicit `PARAMETERS` clause; it can still work without one. — HansUp, Oct 09 '13 at 02:26

How to insert several fields into a table using ADO when two of the fields contain commas

2 Answers2

Linked