Creating 3,000,000 consecutively numbered records 1

[MacroScope]

I am creating a brand new database in 2007. Its function is to track, cradle to grave, an initial share offering of a company for 3,000,000 shares. I need to actually create all 3,000,000 records before beginning, and then assign them as they are sold to buyers.

How can I create 3,000,000 consecutively numbered records? This will be the Primary ID field for that table.

As always, all help is appreciated.

 

[MacroScope]

I'd like to add that once those 3,000,000 records are created I want to make it almost impossible to delete any of them. Obviously they are permanent, and they must be protected from deletion. Any suggestions on how to accomplish this?

If the shares are split then it could go to 6,000,000 or even 12,000,000 records eventually, so I need the ability to create new ones, and it is conceivable that a reverse split could happen at some point, with 6,000,000 records reverting back to 3,000,000, (hence the reason for the qualifier "almost impossible to delete"), so what I need is a very tightly controlled ability to add or delete records once established.

 

[dhookom]

You can create a table [tbl09] with a single numeric field [num] and 10 records with values 0 - 9.
Add this one table into a query many times to create a SQL statement like following to create 1,000,000 records

SELECT [tbl09].[Num]+([tbl09_1].[Num]*10)+([tbl09_2].[Num]*100)+([tbl09_3].[Num]*1000)+([tbl09_4].[Num]*10000)+([tbl09_5].[Num]*100000) AS Numbers
FROM tbl09, tbl09 AS tbl09_1, tbl09 AS tbl09_2, tbl09 AS tbl09_3, tbl09 AS tbl09_4, tbl09 AS tbl09_5
ORDER BY [tbl09].[Num]+([tbl09_1].[Num]*10)+([tbl09_2].[Num]*100)+([tbl09_3].[Num]*1000)+([tbl09_4].[Num]*10000)+([tbl09_5].[Num]*100000);

You will need to add one more instance of the table.

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

Very interesting how you accomplished that. I ran it and created a million records in less than ten seconds. Can you explain how you got the single table to be seen as 5 separate tables?

I added on what I thought would be the proper syntax to get it to count to 3 million, which was +([tbl09_6].[Num]*3) before the semicolon, but it comes up asking me what tbl09_6 is. I've never seen things done this way. I'd appreciate it if you'd explain how it sort of automatically creates duplicate tables, and at the same time what I'm doing wrong to get it to count to 3 million.

Thanks.

 

[dhookom]

Each separate instance of the tbl09 fills a units, tens, hundreds, thousands,...
Look at the design view and consider using only to copies of the table to get 0 to 99.

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

I looked it over more closely and I see now how you got it to see the same table as multiple tables. I added on that syntax that I thought would make it work but it's been running for 15 mins so far. It's definitely going to be a whole lot more than 3 million when I'm done.

I realized that I can just change this to a Make Table query and create the first million. Then change the output name and create two more tables. Then run an update query to add 1,000,000 to each value in the second table, and append it to the first, and do it again for the third table adding 2,000,000 to each value and appending.

I really appreciate the help. Excellent work.

 

[MacroScope]

I did what I suggested using Make Table and Append queries and within 5 minutes I have a table with 3 million records. Super cool, Duane. I really appreciate the help.

Yer a genius!

Do you have suggestions on how I can prevent any of these records from being deleted? They have to be permanent records, and the only time that new ones will be created is if there's a stock split.

 

[dhookom]

Upload the records into SQL Server and don't allow deletions. Otherwise only allow editing in forms and don't allow deletions.

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

I'm not going to be using SQL initially.

 

[MichaelRed]

Ms Access 'Security' is !at best! a vexing, trying and unstable art form. Since you seem to be attempting to constructing a robust and secure system, the advice already given seems appropiate.

Proceeding with the apparent intent to compromise to appease the lesser god of security, please look into the users level security available in MS Access, Hiding the table from users (indeed, hiding the database window from users as well as hiding hte table within that window!).

And, finally (from me), expect no further encouragment in this direction.




MichaelRed

 

[dhookom]

users level security available in MS Access" limits the developer to older versions as it is no longer supported. As Michael suggests there are some measures that can be used but they aren't very robust or secure.

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

I've been looking at the user level options and see that it's no longer available. I've created my own user level security, and I'm refining it now.

Essentially it starts with an autoexec that locks the navigation pane and opens a form in modal view with right-click disabled, so that it can only be closed programatically. I have only two controls on that screen, username and password, and I've written routines that will do a DLookup and match the password entered with one saved in the table using the StrComp function. It works very well in identifying passwords, and is case sensitive.

If the password matches, then it closes the password entry form and allows the user in. It does another DLookup to find the permission level assigned to that employee, and based on that one of several switchboard forms opens. That switchboard screen then allows the user to open whatever items are designated for that level. It may open forms in Read-only mode, for example.

This is actually quite effective, and while it's not perfect it does allow some measure of user control.

The problem I'm having with it that perhaps you can suggest a solution to is to identify the user in a web-based environment in Access 2010. Unlike a network, there's no permanent user name that follows the user everywhere s/he goes. I think using a TempVar is one possible alternative, but I can't seem to come up with a way to assign that on login and have it follow that user through web movements.

Any ideas or suggestions would be appreciated.