Solved

INSERT INTO tablename gives error

Posted on 2011-03-05
7
359 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

776 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