Link to home
Start Free TrialLog in
Avatar of mainrotor
mainrotor

asked on

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:
User generated image

Here is my table in design view:
User generated image
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

SOLUTION
Avatar of Krishna V
Krishna V
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mainrotor
mainrotor

ASKER

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:

User generated image
How can I stop this from popping up?

thank you,
mrotor
Disregard my last question.  I figured it out.

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