Solved

I need help saving to an Access 2013 table

Posted on 2014-12-09
5
399 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now