Remove and set composite primary keys with VBA (maybe)

[Stormin]

Hi,

Background:
I have created a table that is going to hold daily stock snapshots used for reporting. The data is 'in flow' and so there are constant incoming and outgoing items from stock, and constant status changes of those stock items for different stages within the process.

e.g. Today's full 'in-stock' list is extracted from an application and then imported to my database. Then tomorrow's full 'in-stock' list is extracted and imported to my database. And so on, with a retention policy of course to clear out old data after a certain period.

This design means I can report on where stock is going, how it flows through the stages day by day, and where there is static stock that hasn't moved. (at least, I hope this is the most appropriate design).


My stock table has a unique identifier for each item, but since many items will appear more than once in the table I also have a [SSDate] field that contains the date of the report it comes from. This field is populated by an update query after the file is imported, using the date from the filename (the file is selected and imported using VBA).

Problem:
I want to set the composite primary keys for this table as [ID] and [SSDate] so that I can uniquely identify each record, and avoid erroneous duplicate records for the same snapshot date. However, since [SSDate] is set AFTER the data is imported, I cannot have this field as a PKey when the data is imported - one of the primary keys will be null and it will not add the records.

One solution I can think of is to do the following in VBA:
1. Remove the 2x composite PKeys from the table
2. Perform the data import
3. Populate [SSDate]
4. Remove any duplicates or nulls across the two future PKeys
5. Apply the 2x composite PKeys

Is it possible to set fields to (composite) PKeys programmatically, and where can I go to learn the code for that?

Additionally, if you have any suggestions for this method of analysing flowing stock, now is the time!

Thanks in advance.

 

[plog]

However, since [SSDate] is set AFTER the data is imported

Why? Both of these values should be going in at the same time. How come you can't set the SSDate value at the same time? Don't you know it? Why not just use the current date the process is run?

 

[Stormin]

That would be ideal of course, however the input file is an Excel file that cannot be edited.

Unless there is a way to add an extra field using Access somewhere in the import procudure? I am using a big chunk of code that I use for all my other imports but copying it to a new function for just this import is easy.
The actual import part of this that I use is:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, strTable, strFilePath, True, strSheetName & "$"

 

[plog]

You should import your data to a temporary table, then base your APPEND query on that temporary table.

 

[Stormin]

Duh! I even have the capability for that already built-in to my import code.

This is why I ask these questions, to weed out the stupid in me.

Thanks