Autonumber

[shelby55]

I am using Access 2002. I have a huge table that I imported from a txt file. I couldn't add unique ID upon import because I had many tables to import and they couldn't be appended until after the fact. Now I want to add an autonumber simply as a unique identifier but Access won't do it because it says it doesn't have enough memory which always happens when the table is too big. How can I add this dynamically i.e. create a make table query using this table with the new autonumber column added?

The FAQ indicates that using "alter table mytable add column unique_id autoincrement;" but where is this statement to go?

Thanks for any and all assistance.

Shelby

 

[PHV]

Something like this ?
CurrenDb.Execute "ALTER TABLE myTable ADD COLUMN myID COUNTER(1,1) CONSTRAINT PrimaryKey PRIMARY KEY"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi

And where does the statement you provide go to be executed?

 

[PHV]

I posted VBA code.
You may also consider using the SQL view pane of the query window to write straight DDL SQL code and then execute it by clicking the [!]![/!] button.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

HI

I appreciate your assistance but you're not answering my question: where does this code go? Is it in a query and view SQL code? What kind of query? Somewhere else?

Thanks.

 

[PHV]

Create a brand new select query but choose no table.
In the SQL view pane replace the generated code (SELECT with the ALTER TABLE instruction.
Click the Execute ([!]![/!]) button.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi

Thanks. Got it but now I get an error message indicating "file sharing lock count exceeded. Increase MaxLocksPerFile registry entry".

How do I do that??

Shelby

 

[PHV]

Have a look here:

http://support.microsoft.com/kb/815281

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

HI

Very helpful, thank you. Now i get an error message indicating "not enough space on temporary disk". I have very large hard drives that are almost empty - how do I fix this error?

Thanks.

Shelby