Update Table data from Import

[mosh]

Hello All,

I currently have a macro that imports data from a spreadsheet and then a query that adds the data into the main table.

But when I want to import new data it deletes the old data out of the table and inserts new data. How can I adjust the query so that it "updates" the new data into the table instead of deleting and then adding?

Another problem is empty records, is there a way of importing data where field 1 has data?

Any help would be great.

Thanks.
________
Herbal Vaporizers

 

[Michael J Ross]

>>But when I want to import new data it deletes the old data out of the table and inserts new data. How can I adjust the query so that it "updates" the new data into the table instead of deleting and then adding?<<

Use an append query, rather than a make table query which I'm guessing you are using at the moment.

 

[mosh]

>>But when I want to import new data it deletes the old data out of the table and inserts new data. How can I adjust the query so that it "updates" the new data into the table instead of deleting and then adding?<<

Use an append query, rather than a make table query which I'm guessing you are using at the moment.

I am using an Append query, but it seems to remove contents of the table.
________
Aero space grinder review

 

[mosh]

anymore suggestions?
________
Web shows

 

[Michael J Ross]

Sorry no, can you post your query design or SQL?

 

[mosh]

Here it is:


INSERT INTO SD42_For_Export ( Site_No, MinOfInvoice_Number, Invoice_Date, [Account Type], Status_Description, Gas_Start_Date, [Time to 1st Bill] )
SELECT SD42_For_Import.Site_No, SD42_For_Import.MinOfInvoice_Number, SD42_For_Import.Invoice_Date, SD42_For_Import.[Account Type], SD42_For_Import.Status_Description, SD42_For_Import.Gas_Start_Date, SD42_For_Import.[Time to 1st Bill]
FROM SD42_For_Import;
________
COLORADO DISPENSARY

 

[Michael J Ross]

Does SD42_For_Import hold only the data you want to add to the main table i.e are records you have already uploaded to the main table deleted from SD42_For_Import after they have been uploaded?

If not you will have to add some criteria to pick only those records you want to append.

 

[mosh]

hey,

just a quick rundown on what happens:

Data is imported from a spreadsheet into SD42_For_Import, the data is then appended into SD42_For_Export.

What I want it to do is to keep existing records that are appeneded from SD_For_Import. Currently data from export is deleted for some reason.

Hope this helps.
________
Wellbutrin Lawsuit Settlements

 

[Michael J Ross]

Sorry not sure I can help it seems reasonable what you are doing. If you run this SQL in an select query does it select only those records you want to append, or does it also select records that have already exist in SD42_For_Export ?
SELECT SD42_For_Import.Site_No, SD42_For_Import.MinOfInvoice_Number, SD42_For_Import.Invoice_Date, SD42_For_Import.[Account Type], SD42_For_Import.Status_Description, SD42_For_Import.Gas_Start_Date, SD42_For_Import.[Time to 1st Bill]
FROM SD42_For_Import;

 

[mosh]

anyone else?
________
VAPORIZER VOLCANO

 

[gemma-the-husky]

are you using access

instead of a macro, just load the import table, then design an append query, and see if running that directly works. if so either run the query or cut and paste the sql into code rather than a macro

 

[mosh]

thats right, i'm using access.

how can I convert from a macro to a module and then create a cmd button?

When I manually run the query it inserts the records without deleting the exisiting ones in the other table.
________
E-CIGS

 

[gemma-the-husky]

if the manual query works, the problem is somewhere in your macro. I
dont use macros at all, but there is a "convert to code command" somewhere that does converts a macro to code automatically, puts it in a module and adds in error trapping loops. The name of the module is not important. In the module access will create a sub or function from your module

If you have a form, add a button to it, and in the onclick event put the name of the new sub that the macro will have created.