Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

I need help saving to an Access 2013 table

Posted on 2014-12-09
5
Medium Priority
?
413 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 2

Assisted Solution

by:Krishna V
Krishna V earned 1000 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 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What we learned in Webroot's webinar on multi-vector protection.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

604 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