Easy updates on tables

[deeda67]

I am trying to come up with a way to update tables every week from two databases. I have one database for a satellite office and one for corporate. Each office has BOTH databases so as to keep up with their own data (we are not on a network). Each office will have to export their Project Table data to the other and then upload (update) the corresponding database. So:

ABC Division exports their project data from their Project Table once a week and sends to DEF Division and vice versa. Then ABC takes the exported Data from DEF and updates their database for DEF and vice versa.

Field names and additional field names will not happen; however, the data in those fields may be updated OR there may be new records added. So basically, I guess I need to delete the old and put in the new every week but make it VERY easy for the end users (who know nothing about Access). The tables that would be updated are related to other tables.

Does anyone have any suggestions where to get started? I've tried an Append query and that only adds records, not looks for changes in those records.

 

[The_Doc_Man]

Here is your problem in a nutshell.

If you want to add records that weren't there before, you use an APPEND query. If you want to modify records that WERE there before, you are looking at an UPDATE query. You can do one or the other but not both - at the same time.

OK, the way to approach this is to import your data into a table that will be deleted when you are done with it. After you import the sheet into your temp table, add a Yes/No field to it. If you have not done so already, impose a key on the temp table that matches the key on the main table. Do an update query on the temp table's yes/no field to show whether that record exists in the main table.

Now, for all the temp records that are NOT in the main table, append them. For all the temp records that ARE in the main table, update the values based on a JOIN of the temp table to the main tabke across the common key values.

 

[pbaldy]

You can do one or the other but not both - at the same time.

As an FYI you can, under certain circumstances, do both:

http://support.microsoft.com/kb/208870/en-us

 

[deeda67]

Thanks. I know I'm having a brain fart but I'm getting really frustrated. I can't get either append or update queries to work. and these directions are working the same way. It will either pull too many occurances of the same record or pull the right number of records but not all the information. Maybe I'm just tired at this point.

What i have is Table A (main table) and Table B (table to pull new or updated records from). So it is set up like this:

Field: Project Number
Table: Table A
Update To: [Table B].[Project Number]

It seems when it does pull up the right number of records, it is only pulling from the Table A because the updated information is not in the preview of the query. I have the two tables joined by Project Number (and I've tried ID but that's an Autonumber in the main table). And have chosen All records from Table B and only those records from Table A where the joined fields are equal. I've checked to make sure these tables are set up EXACTLY the same and they are.

Thanks for your help!!!!

 

[deeda67]

NEVERMIND! HOORAY IT WORKED!!! I just closed it and walked away for a sec and it worked. Thank you both.

 

[billyr]

I have the same problem as deeda67; but, I need to automate the table transmission process. I am curious what method you (deeda67) use to transmit the tables back and forth? Any suggestions would be greatly appreciated.

 

[deeda67]

I've gotten it to upload a specific excel spreadsheet when you click a button. I was working on getting it to import the spreadsheet and then update and append (but I got sidetracked on EXPORTING to a spreadsheet). Basically, I created a command button to automate the importing and added another for the Update/Append query to run, so when the user opens the database, a form pops up with choices, then they just click. I would like to write a macro to get both things to work in one click - but bigger fish to fry. I need to figure out the EXPORTING to a specifically named spreadsheet. I haven't been able to get that to work but that's on my agenda today. Is that what you need too?

As far as importing, I did something like this in my command button code:

Private Sub Command9_Click()

Dim importfile As String
importfile = "File path and name of file"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "name of file", importfile, True

So, you see, I need it to export to the same file name so that when the other office goes to import, its correct. I hate not being on a network - Stone ages!!!

 

[billyr]

Thanks deeda67. I was looking for a magic bullet to avoid passing the updates through a spreadsheet; but, that seems to be the right way to do them. I will likely import the spreadsheet into a transaction table and work from that. I already have 2 boolean "flags" in my tables and will use them to distinguish update or append and whether or not a record has been processed. (Just in case my code finds something wrong with the data.) I already have a utility to delete records in a "vartablename" with one or the other flag true.
Do your remote offices email the spreadsheets back and forth and, if so, have you automated that? I would be interested in that code.

 

[deeda67]

Thanks deeda67. I was looking for a magic bullet to avoid passing the updates through a spreadsheet; but, that seems to be the right way to do them. I will likely import the spreadsheet into a transaction table and work from that. I already have 2 boolean "flags" in my tables and will use them to distinguish update or append and whether or not a record has been processed. (Just in case my code finds something wrong with the data.) I already have a utility to delete records in a "vartablename" with one or the other flag true.
Do your remote offices email the spreadsheets back and forth and, if so, have you automated that? I would be interested in that code.

LOL, no. The reason all this started was when I started here (6 months ago), they were having to input the SAME information in 4 spreadsheets in order to create a new project. That is not even the 4 spreadsheets it took for the satellite office to do theirs. They would simply send their spreadsheets up, and we would add to ours and we would send ours there and they would update theirs (cut and paste). I'm trying to make it easier on those of us who actually do the work, so even though it seems the same thing is being done, it is still automated and there are less chances for mistakes to happen.