Assign Primary Key 2

[fredka]

I have been importing data from a CRM database into Access to run some more complex reports than the CRM database will allow - The problem is that I don't want to import ALL of the data each week, only that data that has changed.

The export feature of the CRM allows me to do this.

However, I need to figure out the best way of updating my access tables. I am thinking that I need to create a new table with the updated data that I am importing and then append my old tables to the new data. I have a primary field that I will use as the primary key.

If I don't do it this way, it won't overwrite my new data due to conflicts with the primary key.

That being said, how do I change a field to the primary key via VBA?

This is what I have so far:

'rename current tables
DoCmd.Rename "Accounts", acTable, "AccountsOLD"
DoCmd.Rename "Opportunities", acTable, "OpportunitiesOLD"
DoCmd.Rename "Opportunity Products", acTable, "OpportunityProductsOLD"

'Import new files from 'Siebel Import Files' folder
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Accounts", _
"C:\Documents and Settings\fkampf\My Documents\Siebel Database\Siebel Import Files\Accounts.xls", True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Opportunities", _
"C:\Documents and Settings\fkampf\My Documents\Siebel Database\Siebel Import Files\Opportunities.xls", True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Opportunity Products", _
"C:\Documents and Settings\fkampf\My Documents\Siebel Database\Siebel Import Files\Opportunity Products.xls", True

'delete current tables (old data)
DoCmd.DeleteObject acTable, "AccountsOLD"
DoCmd.DeleteObject acTable, "OpportunitiesOLD"
DoCmd.DeleteObject acTable, "Opportunity ProductsOLD"
MsgBox "DONE!"

Any help would be appreciated!!! thanks!!! Fred

 

[ByteMyzer]

To create a Primary key index on a table with no primary key, you can use a statement like the following:

DBEngine(0)(0).Execute "CREATE UNIQUE INDEX PrimaryKey ON [[b][i]MyTable[/i][/b]] ([[b][i]FieldName[/i][/b]])"

If you wish to create a primary key index on more than one field, you can supply the field names, separated by commas, within the parentheses:

DBEngine(0)(0).Execute "CREATE UNIQUE INDEX PrimaryKey ON [[b][i]MyTable[/i][/b]] ([[b][i]Field1[/i][/b]], [[b][i]Field2[/i][/b]])"

 

[fredka]

So this just goes right in my code just like you have it? (obviously replacing my field and table names?)

Thanks!!!

 

[JerryKlmns]

fredka

You could update and append in one statement.

UPDATE myTable As T RIGHT JOIN NewDataTable As D On
      T.PrimaryKeyField = D.PrimaryKeyField
SET T.PrimaryKeyField = D.PrimaryKeyField,
    T.Field1 = D.Field1,
    T.Field2 = D.Field2,
....
    T.Fieldn = D.Fieldn;

as long as PrimaryKeyField is not an Autonumber

 

[fredka]

I will give it a go today - thanks!!!!