I need help saving to an Access 2013 table

Hi Experts,
I have an Access 2013 application.  In my application, I open up an Excel spreadsheet, loop through values in a column and try to save those values to an Access table, but i get the following error:
Insert Error

Here is my table in design view:
Table in Design View
Here is my code, which I got from the following url:
 http://stackoverflow.com/questions/5310582/vba-to-import-excel-spreadsheet-into-access-line-by-line
The Author: Fink

My Code:
Option Compare Database

Private Sub Command3_Click()
    Dim xlApp As Object
    Dim xlWrk As Object
    Dim xlSheet As Object
    Dim i As Long
    Dim sql As String
   
    Set xlApp = VBA.CreateObject("Excel.Application")
   
    'toggle visibility for debugging
    xlApp.Visible = False
    
    Set xlWrk = xlApp.Workbooks.Open("C:\ExcelImportFile.xls")
    Set xlSheet = xlWrk.Sheets("Sheet1")
   
    For i = 1 To 10
        sql = "Insert Into tblTestImport (NOTE) VALUES (" & xlSheet.Cells(i, 2).Value & ")"
        DoCmd.RunSQL sql
    Next i
   
    xlWrk.Close
    xlApp.Quit
   
    Set xlSheet = Nothing
    Set xlWrk = Nothing
    Set xlApp = Nothing
End Sub

Open in new window

mainrotorAsked:
Who is Participating?
 
Kelvin SparksConnect With a Mentor Commented:
Try

sql = "Insert Into tblTestImport (NOTE) VALUES ('" & xlSheet.Cells(i, 2).Value & "')"

Note that I have inserted a single quote before and after your double quotes around & xlSheet.Cells(i, 2).Value &

Kelvin
0
 
Krishna VConnect With a Mentor Commented:
Hi,
 I see couple of things in your code.

1) You are using Note a RESERVED work in Access as field name.
2) There is a difference in case of NOTE and Note, it should be a problem but can you please check about that.

Try to change field name to any name other than Note and see if that work.

Thanks,
0
 
mainrotorAuthor Commented:
Kelvin and Krishna V,
I changed NOTE to Notex, and added the single quotes around & xlSheet.Cells(i, 2).Value &.

That worked!  But now every time it tries to save it prompt the following message:

Do you want to append message
How can I stop this from popping up?

thank you,
mrotor
0
 
mainrotorAuthor Commented:
Disregard my last question.  I figured it out.

mrotor
0
 
Krishna VCommented:
Hi,
 Incase you HAVE to use RESERVED words as column names then the column name can be enclosed between [, ] in your query it should work.

You can try and check if the problem happens to be because of Reserved word.

Thanks,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.