DELETE ID from many tables

[editolis]

Hi all,
I am importing data from XL file and then I create 50 new tables.
Each table automatically creates ID.
I am using ACCESS 2007 and EXCEL 2007.
For some reason I do not want the ID.
My question is this:
How can I AUTO - delete the ID from each table.
I do not want to do it “by hand” in each one table.
Can you suggest me any simple solution? Maybe qry or VBA.
Thank you in advance.

 

[Dennisk]

if you are referring to an auto id, either ignore it, or go into table design and delete it.

In my opinion all M$ software does too much second guessing especially when it comes to indexing and auto numbers. When you created the table it will have prompted you to have M$ Access automaticcally create a Primary Key for you, if you click yes it creates an auto number.

 

[editolis]

I Try this but does not working:

Dim db As Database

db.TableDefs![tblName].Fields.Delete "ID"
db.Close
Set db = Nothing

Set db = CurrentDb()
db.TableDefs![tblName].Refresh
Set db = Nothing

Any other suggestions?

 

[boblarson]

You need to use the ALTER TABLE SQL to remove fields.

Dim strSQL As String 

strSQL = "ALTER TABLE YourTableNameHere DROP COLUMN ID"

CurrentDb.Execute strSQL, dbFailOnError

 

[editolis]

You need to use the ALTER TABLE SQL to remove fields.

Dim strSQL As String 

strSQL = "ALTER TABLE YourTableNameHere DROP COLUMN ID"

CurrentDb.Execute strSQL, dbFailOnError

Dear BOB,

Thank you for your reply.

That give me the error Run-Time error '3280'

Is working perfect for other COLUMNS. Not for the ID.

 

[boblarson]

Hmm - you might need to drop any indexes first.

 

[editolis]

Hmm - you might need to drop any indexes first.

Dear BOB,

I Try this but i have error:

drop index TableName.ID

What i am doing wrong?

 

[boblarson]

For Access it would be:

DROP INDEX PrimaryKey ON YourTableNameHere

 

[gemma-the-husky]

why on earth are you creating 50 (exactly 50?) new tables

that just cant be right.

what info is on your import file?

 

[editolis]

why on earth are you creating 50 (exactly 50?) new tables

that just cant be right.

what info is on your import file?

I Am importing RANKINK tables from football teams
all over the world.

 

[boblarson]

Oh, and a good reference for you is:
http://www.w3schools.com/SQL/sql_drop.asp

 

[editolis]

Dear Bob,

Here is the code but i get error:

Dim strSQL As String

DROP INDEX ID ON tblBrazil

strSQL = "ALTER TABLE tblBrazil DROP COLUMN ID"

CurrentDb.Execute strSQL, dbFailOnError

I Think you are close to the solution.

 

[boblarson]

Dear Bob,

Here is the code but i get error:

Dim strSQL As String

DROP INDEX ID ON tblBrazil

strSQL = "ALTER TABLE tblBrazil DROP COLUMN ID"

CurrentDb.Execute strSQL, dbFailOnError

I Think you are close to the solution.

1. there would be no spaces in the index name.

2. If you didn't add the index yourself and let Access add the "primary key" then use my code EXACTLY as shown as it adds the index with the name PrimaryKey:

Dim strSQL As String

[COLOR=Red]DROP [COLOR="Blue"]PrimaryKey[/COLOR] ON tblBrazil[/COLOR]

strSQL = "ALTER TABLE tblBrazil DROP COLUMN ID"

CurrentDb.Execute strSQL, dbFailOnError

 

[editolis]

The code give me this error:

DROP ID ON tblBrazil

Compile error:

expected: end of statement

=============================

Dear Bob thank you for your help.

We can try again tomorrow.

 

[boblarson]

The code give me this error:

DROP ID ON tblBrazil

Compile error:

expected: end of statement

=============================

Dear Bob thank you for your help.

We can try again tomorrow.

Why did you use this code:
DROP ID ON tblBrazil

I said use what I gave you EXACTLY AS WRITTEN

DROP PrimaryKey On tblBrazil

Do not change a word of it, leave the words PrimaryKey exactly as written.

 

[editolis]

The code give me again this error:

DROP PrimaryKey ON tblBrazil

Compile error:

expected: end of statement.

 

[boblarson]

oops I think we needed:

Dim strSQL As String

strSQL = "DROP PrimaryKey ON tblBrazil"

CurrentDb.Execute strSQL, dbFailOnError

strSQL = "ALTER TABLE tblBrazil DROP COLUMN ID"

CurrentDb.Execute strSQL, dbFailOnError

 

[editolis]

Syntax error in DROP TABLE or DROP INDEX. (Error 3295)
You entered an SQL statement that has an invalid DROP statement.
Possible causes:

• A reserved word or argument name is misspelled or missing.
• Punctuation is incorrect.

 

[boblarson]

Well, I guess tomorrow it is...

 

[editolis]

Here in Athens the time now is 1.20 midnight.

Goodnight.

Thank you