Importing Microsoft Excel data

[Nev_the_B]

Hope someone can point me in the right direction

I have designed a DB for my company which is holds data on our clients. Field offices have a version for their respective area and a master version (which holds all data) is kept at the head office. I have implemented a feature so that if someone in a field office updates an address, for example, of a client they can email, via a button on a form, that update (in the form of an .xls attachment) to head office so everyone has the same info at the same time. However I've hit a snag when we import the information. Using an import macro it seems to work fine untill I look at the actual data it's imported and I find that it simply copies the data into the table. I was hoping that it would overwrite where there have been changes. Is there a way to do this at all?

I'm fairly new to Access, so please be gentle!

 

[Parker]

Hope someone can point me in the right direction

I have designed a DB for my company which is holds data on our clients. Field offices have a version for their respective area and a master version (which holds all data) is kept at the head office. I have implemented a feature so that if someone in a field office updates an address, for example, of a client they can email, via a button on a form, that update (in the form of an .xls attachment) to head office so everyone has the same info at the same time. However I've hit a snag when we import the information. Using an import macro it seems to work fine untill I look at the actual data it's imported and I find that it simply copies the data into the table. I was hoping that it would overwrite where there have been changes. Is there a way to do this at all?

I'm fairly new to Access, so please be gentle!

I think you need to use an update query.

 

[Nev_the_B]

Eh!?!

Thanks for replying.

Like I said I'm still fairly new, and am trying to grasp the concepts. I can see how you can use the update query, for example, to increase a range of values by a percentage. However I'm not sure how I use it for the purposes described above?

Any more pointers would be greatly appreciated.

 

[neileg]

Using the query design view, create a query and add in both your existing table and the table that holds the new data. Link the relevant ID fields between the tables. Add the fields of your existing table to the grid. Change the query type to Update. In the 'Update To' row, enter the name of the field in the imported data that holds the corresponding data. You will have to include the table name if the field names are the same in each table. That's it.

 

[The_Doc_Man]

Since you suggest that you are new at the game, I'll be a bit more explicit in my suggestion rather than just giving you an overview.

1. Don't import your Excel to the table you want. Import it to a new table that is, in essence, a throwaway table. Design the table ahead of time but don't populate it.

2. Build three new queries.

2.1 Erase the throwaway table's contents.
2.2 Update records in your master table where the key data in the master matches corresponding key data in the throwaway table.
2.3 Append records to your master for records in the throwaway table that do NOT correspond to any records in your master table.

3. Write a macro to perform the operation.

3.1 Erase the throwaway table
3.2 Import the spreadsheet to the existing but empty throwaway table
3.3 Run the Update query
3.4 Run the Append query

Steps 3.3 and 3.4 could be reversed if you had reason to do so.

Now, when you get more comfortable with VBA, you could change this from a Macro to event code behind a control button on a form somewhere, such as a control panel form. The reason is that you cannot trap errors in a macro so cannot respond correctly to bad data. So get away from the macro as soon as you can.

 

[Nev_the_B]

Cheers! But....

Thanks very much to you both for your clarity. Works like a charm.

On a slightly different subject however I now see potentially another problem. I'm developing this db out of a need to ensure everyone has the same info at the same time, a very important need in our particular field of work.

However I see that there may be a problem when, for example, the main office has a new client which they want to send via email update (in the form of a xls attachment)to a field office, but the field office also has an update, like a change of address, to send to the head office at the same time. Wouldn't we loose some records if the field office updated their db before sending over their own update (hope this makes sense!). Since they don't have experience in Access and only use the database through forms I want to make this whole process as idiot proof as possible.

Has anyone else had this problem?