• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

INSERT INTO tablename gives error

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?
  • 3
  • 3
1 Solution
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Here is the general syntax:

FROM Table1;

Not sure about you @ symbols ...

Agent909Author Commented:
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 (" & _
                    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

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
What is this:



Are you sure this in Access SQL ?
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Agent909Author Commented:
yes, I'm using it in Access SQL. The OleDbCommand has .Parameters and .AddWithValue.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Sorry ... not really familiar with OleDb ...

Someone will be along ...

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

Agent909Author Commented:
Thank you!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now