Auto-generated Date Field or Multiple Primary Keys?

[Houstonwolf]

Hello. I've had much luck posting for help in Excel. This is my first post referencing Access.

A web developer and I are working on an online ocean-going container information system. I am responsible for the database and he is responsible for the web form and the hosting.

The source of the data is a continually updated Excel spreadsheet from a vendor. New containers are added, old (delivered) containers are dropped from each new report. Most entries in each new spreadsheet will be duplicates, however, because of the lead time getting a container across the ocean and half-way across the country. I've been able to import each new spreadsheet into my database, but it occurs to us now that ultimately, a unique container ID will eventually make its way back into the system.

MY first thought was to create multiple primary keys: the container ID and the part ID (SKU), because that actually would be primary. That combination will never happen more than once. I couldn't get it to work, so I adopted only the container ID field as the primary.

The web developer has suggested we create an auto-generated date field that, with each new import, will stamp the new entries with the import date. After 9 months, the entries could be purged. That's his preference in order to keep the DB from growing too large. I think 9 months is sufficient to ensure a duplicate container doesn't make it back into the system and is purged because the container ID conflicts with an existing entry.

I'm willing to do either. I just can't figure out how to do either one. Any advice on which approach is better and how I could do that to an existing table with over 3000 current entries?

 

[rconverse]

To create multiple primary keys, in design view of your table hold down control and click on the fields that you wish to have as primary keys and then click on the primary key button. That sounds like the simplest solution.

If you add a date field in your table you could run an update query every time you import the file. Are you importing manually (via file | get external data) or are you using the transferspeadsheet command in VB?

In sql the update query would look like this. It will only update records where the date is null, which should be your new records.

UPDATE tblNameSET tblName.Field1= IIf(IsNull([Field1]),Date());

HTH
Roger

 

[Houstonwolf]

To create multiple primary keys, in design view of your table hold down control and click on the fields that you wish to have as primary keys and then click on the primary key button. That sounds like the simplest solution.

If you add a date field in your table you could run an update query every time you import the file. Are you importing manually (via file | get external data) or are you using the transferspeadsheet command in VB?

In sql the update query would look like this. It will only update records where the date is null, which should be your new records.

UPDATE tblNameSET tblName.Field1= IIf(IsNull([Field1]),Date());

HTH
Roger

Thank you for replying so quickly, Roger. It's actually a macro that was created by someone in another office who has more experience than I do. The macro uses the TransferSpreadsheet action to import two spreadsheets that have different data. Both those files are in the same directory.

I will try the multiple primary key process again. I'm pretty sure what you described is what I did, but the macro would never import the data and the gentleman who created the macro suggested it would not work if I used multiple primary keys. For awhile, I had no primary key at all.

 

[rconverse]

If you are using a macro in Excel and the transfer spreadsheet command, then you are strictly importing and overwriting the same table every time you create this process. I suspect there is another table that the imported data appends to.

The transferSpreadsheet command imports a file into the database. At that point, there are no primary keys assigned. Even if today you imported and then assigned primary keys to your table. Tomorrow as soon as you hit import, yesterday's tables are gone along with the primary keys. Again, I suspect that you are then moving the newly imported table to a different table and that is where you have your primary key(s) set.

So if this is running via a macro, how does the macro get launched? Are there any forms for your DB?

 

[Houstonwolf]

If you are using a macro in Excel and the transfer spreadsheet command, then you are strictly importing and overwriting the same table every time you create this process. I suspect there is another table that the imported data appends to.

The transferSpreadsheet command imports a file into the database. At that point, there are no primary keys assigned. Even if today you imported and then assigned primary keys to your table. Tomorrow as soon as you hit import, yesterday's tables are gone along with the primary keys. Again, I suspect that you are then moving the newly imported table to a different table and that is where you have your primary key(s) set.

So if this is running via a macro, how does the macro get launched? Are there any forms for your DB?

Gosh, I hope it's not simply overwriting the table each time! I know that the warning I receive each time the macro runs is that 'n records were deleted due to primary key violations'. The table is getting larger with each import. I wish I knew how to explain better what the macro is doing.

I know from looking at the macro in design view that it: SetWarning, TransferSpreadsheet (Transfer Type Import), TransferSpreadsheet (a different spreadsheet), MsgBox

I did find out though, that I had a few null entries in one of the fields I had set to primary. I deleted those and now have multiple primary keys in my test table.

So your help (and patience) has been well-received! I now need to learn how to do the update query to add the dates into new entries, but I guess that can wait for another day.