Solved

I need help saving to an Access 2013 table

Posted on 2014-12-09
5
404 Views
Last Modified: 2014-12-10
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

0
Comment
Question by:mainrotor
  • 2
  • 2
5 Comments
 
LVL 2

Assisted Solution

by:Krishna V
Krishna V earned 250 total points
ID: 40490618
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
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 250 total points
ID: 40490619
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
 

Author Comment

by:mainrotor
ID: 40490623
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
 

Author Comment

by:mainrotor
ID: 40490624
Disregard my last question.  I figured it out.

mrotor
0
 
LVL 2

Expert Comment

by:Krishna V
ID: 40490727
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

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question