Solved

INSERT INTO tablename gives error

Posted on 2011-03-05
7
362 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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 84

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

679 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