469,356 Members | 1,994 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,356 developers. It's quick & easy.

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
Mar 23 '07 #1
7 8179
Rabbit
12,516 Expert Mod 8TB
Why do you need a record # column?
Mar 23 '07 #2
nico5038
3,080 Expert 2GB
Create a DDL query that will add the autonumber like:
Expand|Select|Wrap|Line Numbers
  1. alter table tblA add column IDX counter
  2.  
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)
Mar 24 '07 #3
Why do you need a record # column?
The current manual ETL process counts the records until a specific value is found and then uses that record # for future queries.

hjohnson
Mar 26 '07 #4
Create a DDL query that will add the autonumber like:
Expand|Select|Wrap|Line Numbers
  1. alter table tblA add column IDX counter
  2.  
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)
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
Mar 26 '07 #5
nico5038
3,080 Expert 2GB
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)
Mar 26 '07 #6
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)
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
Mar 27 '07 #7
Rabbit
12,516 Expert Mod 8TB
The current manual ETL process counts the records until a specific value is found and then uses that record # for future queries.

hjohnson
There's no primary key that you can use instead?
Mar 27 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Henry Stockbridge | last post: by
1 post views Thread by rsbutterfly16 via AccessMonster.com | last post: by
2 posts views Thread by Pramod Kadur | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.