Replies

I, too, thought the autonumber-increment method would use the next higher number. But I found duplicates appearing (as described in my previous post) — the newer records were being assigned “old” (archived) numbers even though there were higher active autonumbers — the autonumbering scheme seemed to be “filling holes”. I’ll have to admit that I wasn’t too diligent about repairing/compacting after archiving (removing records from the active table) — that may be part of the problem. Also, and this may be circumstantial, I noticed this problem only after upgrading from Access 97 to 2000.

Autonumber-random is a good idea. With the odds of duplicating an existing record at around 1 in 4 billion, I guess it’s pretty safe (unless you’ve got a fairly big database!). If I were to worry about such odds, I should be buying a lot more Lotto tickets!

I may be mistaken, but I think Access 97 (or is it Excel 97?) may need the 8+3 MS-DOS folder name rather than the civilized one; i.e., try changing “G:New IdeasDummy.xls” to “G:NEWIDE~1Dummy.xls”. (I’m using Office 2000 now, so I can’t check this myself.) It’s possible your MS-DOS folder name is different – check it by viewing the folder’s properties.

Tom

I agree that an autonumber field is a great choice for a primary key. However, I’ve encountered “challenges” with using autoumber primary key fields in the following situation: When I archive records from my “active” table to an “archive” table, I’d like the primary key (the autonumber field) to remain unique across all records in both tables (since it provides the relationship to various other tables). However, once a particular autonumber value is gone from the “active” table, a new record added may be given this value, thus duplicating a primary key in the “archive” table. To avoid this, (based on advice from Charlotte here in Woody’s Lounge), I set up a “master” list of if IDs that are autonumbered. They then relate to an ID field in both the archive and active tables which are not autonumbered. This adds some overhead when adding and deleting records (adding a record to the active table means adding a new record to the master list (with a new and unique-across-both-tables autonumber primary key ID), then actually adding the new record in the active table and copying the master list autonumber value into the field that’s related to the master list ID). Deleting records can be taken care of through referential integrity. It seems like there ought to be a less cumbersome way of doing this — is there???

Regardless, even with this sort of arrangement, I’ve never seen autonumber fields getting re-numbered. Boy, would that be a mess!

Tom

Oops… The close file code should read:

‘ Close the dummy workbook (without saving changes)
appExcel.Workbooks(“C:My DocumentsExcel Workbooksdummy file.xls”).Close False

Ed,

Try this for starters:

Create a new module. Specify the Excel Object Library (find it on the Tools…References list and check it). Then type in the following code:

Option Explicit

Public Function blnExcelFromAccess() as boolean

Dim appExcel As Excel.Application

‘ Link to Excel using automation
Set appExcel = CreateObject(“Excel.Application”)

‘ So you can watch what’s happening…
appExcel.Visible = True

‘ Open your main workbook
appExcel.Workbooks.Open “C:My DocumentsExcel Workbooksmain file.xls”

‘ Calculate (‘read’ values from your dummy file; may not be necessary)
appExcel.Calculate

‘ Close the dummy workbook (without saving changes)
appExcel.Workbooks(“C:My DocumentsExcel Workbooksdummy file.xls”) False

‘ Done with Excel (omit if you want to leave Excel open)
appExcel.Quit

End Function

Save the module, then add a RunCode macro command to your macro routine and specify “=blnExcelFromAccess()” as the argument.

This is very bare bones, but it should get you started. I’ll bet you’ll be “doing it all” in VBA before long!

Tom

Try setting the form (or table) field’s format property to “mm/dd/yy”. If the user types in “7/27” and or , “07/27/01” will be displayed (the current year will be assumed). No input mask required. Of course, this does assume you WANT the current year.

For the form’s On Close event , use VBA (choose Code Builder) and type in the statement:

DoCmd.OpenForm “”

Alternatively, you can use a macro as the On Close event for your form (choose the Macro Builder): Select the “OpenForm” action and specify the Switchboard name as the “Form Name”.

Of course, the Switchboard is a form — its name should be listed on the Forms section of the Database Window.

I tried your suggestions but, alas, to no avail. However, I have found the source of the problem, but can’t explain it.

I created a bare-bones database that reproduced the behavior. The key seems to be: if you have a VBA function call in your query that is not record-contents-dependent (in principle, not a good coding practice anyway, it seems), this behavior seems to appear.

On one computer the function is called once for every record in the table. On the other computer, the function is called only once (it appears to be “smart enough” to know that there is no by-record dependency). If the table is big and/or the function call eats up time, then the performance between the two can be VERY different. In my “real” database, I observed about a 40x difference. Both computers are running the same versions/builds of Access 2000 and VBA. What gives?

Lo and behold, I discovered that one of the computers was running Windows 98 and the other — WIndows 98 SE. The SE computer was running the query “smartly” and fast. I got our IT folks to install SE on the other machine and they now both behave the same way.

Is it plausible that the version of WIndows would affect the way Access processes queries? Seems odd to me…

Here’s the database I used to check this behavior:

tblTestTable:
Simple 1-field (named lngValue) with, say, 1000 records. Structure, etc. of this table doesn’t really matter.

Query1:
SELECT tblTestTable.lngValue, blnLogic() AS Logic
FROM tblTestTable;

Module:
Option Compare Database
Option Explicit
Option Base 1

Public lngTestCount As Long

Public Function blnLogic() As Boolean
blnLogic = True
lngTestCount = lngTestCount + 1
End Function

Public Function blnTimeQueryTest() As Boolean
Dim rst As Recordset
lngTestCount = 0
Set rst = CurrentDb.OpenRecordset(“Query1”, dbOpenDynaset)
MsgBox “blnLogic() called ” & lngTestCount & ” times.”, _
vbInformation + vbOKOnly, “Query Timer”
rst.Close
blnTimeQueryTest = True
End Funct

Execute blnTimeQueryTest() from the VBA immediate window.

Curiously, if the blnLogic() function is changed to return the long value of the counter, the output of the query shows “1” for every record, indicating the function was still called only once. To me, this is not intuitive behavior (even though it does run faster!). I would have expected the value to increment, record-by-record, as the query is evaluated.

Tom Bushaw

Jon,

The “serious problems” Charlotte warns of might be related to her “multiuser” qualification. Envision User 1 getting ready to add a record. The new manual key has been calculated (e.g., DMAX+1) but the record has not yet been added. User 2, meanwhile, is doing the same thing. User 2’s manual key gets calculated to the same value as User 1’s (since User 1’s record hasn’t been added yet). Now, whoever adds their record first “wins”; the other runs into problems…

It doesn’t appear that this particular problem would arise in a single user environment but I wonder if there are other ones that would?

Tom

Jon –

Good ideas. Thanks.

Regarding Option 2: If I’m using link tables that use the key values as the linking field this option could present problems. I suppose VBA code could be written to update link table references when records get moved from table to table (and new autonumber keys get assigned), but that seems like it would be a bit of a headache.

Option 3 seems like the way to go, but I need to be sure to check both tables when establishing a new unique number (e.g. DMAX+1 on the union query result). Right?

Any better ideas out there?

Tom Bushaw
Access 2000 (9.0.4402 SR-1)

I was about to post a new question but then found this thread that is real close to my problem. I have an “active” table and “archive” table with identical structures (including autonumber keys indexed with no duplicates). When “archiving” a record, I copy it from the active table to the archive table using an append query something like:

INSERT INTO ArchiveTable SELECT ActiveTable.* FROM ActiveTable WHERE (condition that defines a record that needs to be archived);

and then delete those same records from the active table.

This keeps the active table smaller (better performance, presumably). When I need to query all records (e.g., searches on both active and archive records), I query a union of the two tables (hence, the primary keys need to remain unique for the relationships to be properly maintained).

The problem is (as has been pointed out in this thread), the deletion step leaves autonumber “holes” in the active table. When new records are added, the autonumber key assigned may, in fact, duplicate one of the keys of the records that was previously moved over to the archive table. Later, when I try to archive one of these “new” records, a conflict arises because of duplicate key values (this could also occur if I tried to “unarchive” a record; i.e. move it back to the active table).

So… the question is: What is a better way (i.e., a way that actually works!) for maintaining an active-archive table pair while maintaining autonumber uniqueness across BOTH tables?

Tom Bushaw
Access 2000 (9.0.4402 SR-1)