need to add record count column to existing table

Within the access environment, I have a table that I'd like to
-add a column
-place the record number of each record into that column

The autonumber is not working for me because I am importing a .txt file into access using a specification file. The table is created at this time. When these steps are performed by hand access prompts you with a ? to add an index / auto counter field. When I run this via a macro I have not figured out how to have access add this field (didn't find the parameter to have it do this).

This is a daily process and the first step is to delete the table. I've tried to delete all the records and then perform a microsoft recommended solution to attempt to get the counter to start at 0 again but it did not work.

I can't change the source .txt file to add the record count at the start of each record at this time.
The end result is that I need a record count column in a table (300,000 records).

thanks

hjohnson

access 2003 sp2

re: need to add record count column to existing table

Why do you need a record # column?

re: need to add record count column to existing table

Create a DDL query that will add the autonumber like:
tblA needs to be replaced with your table name and IDX with the fieldname you want to use for the counter.

This is the fastest solution. To reset an autonumber after a "Delete * from tblA" you just need a compact and repair of the database, but that's slower.

Nic;o)

re: need to add record count column to existing table

The current manual ETL process counts the records until a specific value is found and then uses that record # for future queries.

hjohnson

re: need to add record count column to existing table

This solution results in the error: File sharing lock count exceeded. Increase MaxLocksPerFile registry entry.

I then found a microsoft resolution that indicated the following would change this setting: In Immediate Window, enter the following codeDAO.DBEngine.SetOption dbmaxlocksperfile
,15000
6. Press the ENTER key to run the line of code.

I ran this multiple times up to 1,000,000 and it made no difference. I didn't feel eager enough to change my registry setting.

I did try to add a compact database command in a macro (which does reset the counter after I removed the records in the table) but Access does not allow the compacting of a database to be initiated from a macro.

The solution I went with was declaring the table from a sql statement that identified the index field. This gets me my counter index but adds a 2nd location that must be updated if the source file format changes.

I'd still like to perform this using sql in access.

hjohnson

re: need to add record count column to existing table

The code works form me, but when you get this message, it looks like you created an endless loop in your macro.

Perhaps better to switch to VBA code. (You can save a macro as code) and post the code here so we can have a look.

Nic;o)

re: need to add record count column to existing table

It now works.
I attempted to perform the microsoft knowledgebase solution of changing the locks temporarily via the immediate window of visual basic but I don't think it took. When I changed the entry in regedit it solved the problem.

thank you very much.

hjohnson

ps. the below resolution from microsoft did not work for me.
http://support.microsoft.com/kb/815281

running the following from the immediate window.
DAO.DBEngine.SetOption dbmaxlocksperfile,15000

re: need to add record count column to existing table

There's no primary key that you can use instead?