Table 'items1' already exists. How to upload data in this table "items1"

Question

1.00/5 (1 vote)

See more:

VB.NET

Private Sub Button11_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button11.Click
           Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\POSdata.mdb; Jet OLEDB:Database Password=pos1234")

           AccessConn.Open()

       Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Items1] FROM [Excel 8.0;DATABASE=C:\Book1.xls;HDR=NO;IMEX=1].[Sheet1$]", AccessConn)

           AccessCommand.ExecuteNonQuery()
           AccessConn.Close()
           MsgBox("OK")
   End Sub

What I have tried:

Hi! If I change " SELECT * INTO [Sheet1] " then when click this button11 this code is ok but when is saved to new table in access. It need to save data in " Items1 " but when I tried this code " SELECT * INTO [Items1] " then is showing this problem for me:

Table 'Items1' already exists.

How to solve this problem to upload data in table " Items1 " and no in to a new table ?
Please help :) Thanks

Posted 11-May-16 2:03am

ionMEMBER

Updated 11-May-16 2:23am

Add a Solution

2 solutions

Solution 2

You could issue a

SQL

drop table Items1

within a Try-Catch block - ignore the error if it's saying the table does not exist. Not very elegant but it will work most of the time.

There is a "hidden" table that you could query

SELECT COUNT(*) as t from MsysObjects WHERE type in (1,4,5,6) AND name = 'Items1'

Drop the table if t is > 0.

Or, create the table explicitly (you will need to know up front what is on the sheet) and swap that query to be

SQL

INSERT INTO Items1 SELECT * FROM [Excel 8.0;DATABASE=C:\Book1.xls;HDR=NO;IMEX=1].[Sheet1$]

There will be issues with the last approach if the users move any columns around (in my experience, they will do this)

Comment - these approaches are intended to work for when the table does not exist at all (first run of program) AND for subsequent runs. The first two suggestions assume that you do not want to retain any data from previous runs. The last suggestion will retain data from previous runs (and is subject to the vagaries of users moving columns around)

Posted 11-May-16 2:23am

CHill60

Updated 11-May-16 2:26am

v2

Comments

ionMEMBER 11-May-16 8:33am

#Chill60
I tried this: INSERT INTO Items1 SELECT * FROM [Excel 8.0;DATABASE=C:\Book1.xls;HDR=NO;IMEX=1].[Sheet1$]

Problem: The INSERT INTO statement contains the following unknown field name: 'F1'. Make sure you have typed the name correctly, and try the operation again.

CHill60 11-May-16 8:35am

As I said - you will need to know up front what is on the sheet and have already created the table accordingly

ionMEMBER 11-May-16 8:57am

Again I not give solution. Please can I help me to send a link with a simple project? please...
thank you!

CHill60 11-May-16 9:20am

I strongly suggest that you remove your email from this open forum otherwise you could end up being the target of spam.
I will only answer questions via this site, not by private email.

ionMEMBER 11-May-16 9:20am

ok

ionMEMBER 11-May-16 9:25am

So can not you help me?!. ok

CHill60 11-May-16 9:28am

You have a sample project ... your own. I've shown you three different ways to get around the problem. I'm not sure what else to show you.
Do you want to retain the values in the table if the program is run again?

ionMEMBER 11-May-16 9:31am

I tried this: INSERT INTO Items1 SELECT * FROM [Excel 8.0;DATABASE=C:\Book1.xls;HDR=NO;IMEX=1].[Sheet1$]

Problem: The INSERT INTO statement contains the following unknown field name: 'F1'. Make sure you have typed the name correctly, and try the operation again.

WHAT SHOULD I DO?

CHill60 11-May-16 10:29am

So you have created a table called Items1 in access and your spreadsheet only contains data in columns A,B,C,D,E. Physically delete column F from your spreadsheet - if it previously had data, or has any formatting then your code will attempt to import it (known issue with excel).

Something you should NOT do is shout at people who are trying to help - all capitals is considered shouting. You should also avoid downvoting their solution if you want further help - it does not make me inclined to spend any more of my time on this. As you have accepted RyanDev's solution, if the solution I've given in this comment doesn't work it might be worth asking them if they can help

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Accepted Answer · 2016-05-11T02:16:00

Solution 1

SELECT * INTO will create the table. If the table already exists just use

SQL

INSERT INTO tableName 
SELECT ...

Posted 11-May-16 2:16am

ZurdoDev

Comments

ionMEMBER 11-May-16 8:19am

Syntax error in INSERT INTO statement.
Please can you explain me to the long way? thanks

ZurdoDev 11-May-16 8:21am

This is something very easily looked up, faster than waiting for us to respond.

INSERT INTO tableName (field1, field2, field3)
SELECT field1, field2, field3
FROM sometable
WHERE someCondition

OR
INSERT INTO tableName (field1, field2, field3)
VALUES (@value1, @value2, @value3)

ionMEMBER 11-May-16 8:23am

ok. thank you very much ! :D :)

ZurdoDev 11-May-16 8:25am

You're welcome. If you get syntax errors just work through them. You'll learn more that way. But if you do get completely stuck, open a new question and we'll gladly help.