Solved

INSERT INTO tablename gives error

Posted on 2011-03-05
7
368 Views
Last Modified: 2012-05-11
I have two out of five of my tables in a MS Access database not allowing an INSERT INTO statement.  It gives me the error:

 The Insert Into statement contains the following unknown field name:  CurrMonthName.

My SQL statement goes something like this:
"Insert Into DataTable (CurrMonthName,CurrYear) Values (@CurrMonthName,@CurrYear);

I've tried deleting the table and re-creating it.  I removed the CurrMonthName field in the statement so it's just asking for CurrYear, and I still get the same error, except of course it says the unknown field name = CurrYear.  I've also tried inserting the data directly from an Append Query in MS Access, and that won't work either.

Any ideas of what I'm doing wrong?
0
Comment
Question by:Agent909
[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
  • 3
  • 3
7 Comments
 
LVL 75
ID: 35044540
Here is the general syntax:

INSERT INTO Table2 ( FIELD1, FIELD2 )
SELECT Table1.FIELD1, Table1.FIELD2
FROM Table1;

Not sure about you @ symbols ...

mx
0
 
LVL 1

Author Comment

by:Agent909
ID: 35044616
This is my code, and it works for my other forms:
I will try the code you posted, and get back to you.  Thank you!


str = "INSERT INTO DataTraffic " & _
                    "(CurrMonthName,CurrYear,StoreName,Traffic) " & _
                    "VALUES (" & _
                    "@CurrMonthName,@CurrYear,@StoreName,@Traffic)"
                    myCommand = New OleDbCommand(str, myConnection)
                    myCommand.Parameters.AddWithValue("@CurrMonthName", obj3.CurrMonthName)
                    myCommand.Parameters.AddWithValue("@CurrYear", obj3.CurrYear)
                    myCommand.Parameters.AddWithValue("@StoreName", obj3.StoreName)
                    myCommand.Parameters.AddWithValue("@Traffic", obj3.Traffic)

Open in new window

0
 
LVL 75
ID: 35044622
What is this:

myCommand.Parameters.AddWithValue

?

Are you sure this in Access SQL ?
0
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!

 
LVL 1

Author Comment

by:Agent909
ID: 35044665
yes, I'm using it in Access SQL. The OleDbCommand has .Parameters and .AddWithValue.
0
 
LVL 75
ID: 35044675
Sorry ... not really familiar with OleDb ...

Someone will be along ...

mx
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 35044924
The syntax you're working with is more SQL Server or Oracle based (think Stored Procedures). Access doesn't work that way.

To run your Insert statement, just issue it directly:

sSQL = "Insert Into DataTable (CurrMonthName,CurrYear) Values ('" & obj3.CurrMonthName & "','" & obj3.CurrYear & "')"
YourConnection.Execute sSQL

If CurrYear is Numeric, you don't need the single quotes around those values.

0
 
LVL 1

Author Closing Comment

by:Agent909
ID: 35044941
Thank you!
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

617 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