Importing Question

[CCIDBMNG]

I am importing an excel spreadsheet into a table with VBA Code. This works fine. The problem I'm having is the spreadsheet is a running spreadsheet that we keep adding to and I can append the new data because of my primary key having no dups. My question is, is there a way to append the new data to the table and any old data place into a seperate table so I can see if anything has changed since the last time I imported the file.

 

[dcx693]

Do you really want to append new data to your existing table? Or do you mean that you want to replace the existing data? I'm confused because you say you still want to retain a copy of the old data for comparison.

 

[CCIDBMNG]

I am already appending new data. I want to take the data which doesn't append because it's already in there place it in another table and compare it with the main table to see if anything has changed.

 

[dcx693]

...So you want to make a copy of the old data, append new data to the old data, then compare the old data to the new data with the appends? Then you want to do a comparison to find the records that exist in both tables, but that changed?

 

[CCIDBMNG]

Ok, we receive a file from a client with all approved orders everytime there's a new order they add it to the spreadsheet. When they install an order they update the spreadsheet with a date in the install field. Right now I import the data and because of my no dups primary key it only imports the new data which is fine but I also need to update the table with the new install dates so I was wondering if there was a way to import the orders that don't import into the table into a seperate table so I can update the install dates. Does that make more sence. I don't want to delete the whole table each time I receive a file because we update the table with our own things once we are paid so I don't want to delete that stuff.

 

[dcx693]

OK, I think I get it.

You are adding new records to your orders table, but the changed records don't get incorporated because your primary key doesn't allow it.

Now, would you want to incorporate the changes into the table if you could? Or do you really want to copy records to another table if they already exist in your table?

If you want to incorporate the changes, you can with an update query. If you want to just copy them to another table, you can do that too. In the latter case, create a query that look for records that exist in both tables (the primary keys would match) where the install field is not equal.

 

[CCIDBMNG]

But wouldn't I need to place them into a table to create the update query? That's what I'm confused about. How would I do it. If I place them into a table to do that how do I import the ones that have matching primary keys?

 

[dcx693]

If I place them into a table to do that how do I import the ones that have matching primary keys?

To clarify, you can update the values of the records with matching primary keys, you cannot import the records that have matching primary keys, else that would violate the structure.

 

[CCIDBMNG]

I understand that, what I'm confused about is how to import records that have matching primary keys in the main table to a serperate table? Should I just import the whole thing?

 

[dcx693]

OK, to import records that have matching primary key in the main table to a separate table...

That separate table can be a pre-existing table (in which case you'd be appending to that table) or you can create a new table (using a make table query).

Set up a select query with your main table and the table with the new data. Join them on the primary key. Pull down the install field from the new data table. In the criteria line for that field, put something like:
<> [MainTable].[InstallField]
That way, only those records with matching primary keys and where the install fields are not equal will be selected. Then change the query into a make table or append query, depending on how you want to work it.

Is there the possibility that the [InstallField] in the main table could be Null?

 

[CCIDBMNG]

Yes, and I do understand how to do all that. I was just wondering if in my code where I import the spreadsheet into the table that I can tell it to instead of deleting the dup orders to place them in the new table.

 

[dcx693]

Well, after you import the data into your main table (I'm guessing you're using some TransferSpreadsheet command?), you will have to actually import or link the xls file so you can query it to copy the dupes into another table.

 

[CCIDBMNG]

Thanks both of you for all your help.