Import Excel into a table, overwrite the data of the fields imported from Excel in Ta

[bharatmvs]

HI,

I have a table, which contains many fields, around 90. Out of these 90 Fields, around 40 will be imported from an Excel sheet, which has same headers as the 40 field names in my table, in which they have to get copied.

So when I import data, it copies them as new records, instead what I am looking at is, the records in Excel and in Access table has a Unique Key, so when I import data, matching with this unique key, the fields should be overwritten into those records from Excel, instead of adding new records.

E.g:

Say I have 2 records in my access:

ID(auto generated) Fab_name Issue_ID Location Remarks Data_1 Data_2 Data_3
1 Fab1 193 NY Bug OC DC MC
2 Fab2 641 DRS Error AC KC FC

Now when I am importing data from Excel with the following records:

Fab_name Issue_ID Location Remarks
Fab1 193 NY - GRM Solved
Fab2 641 DRS - SGP Forwarded
So when I import the above data into my Access from Excel, based on Issue ID, it should update the values of different fields that are imported from Excel, leaving other fields (data_1, Data_2, Data_3) as it is.

And I need this to be written as a macro, so that user just has to choose the file, which he has to import.

Thank You,
Bharat

 

[Cronk]

You say you want to import the values but really want to update. Correct?

If so, link to the Excell spreadsheet and write an update query with the Access table and the rows in the spreadsheet linked on the unique (key) field.

 

[bharatmvs]

You say you want to import the values but really want to update. Correct?

If so, link to the Excell spreadsheet and write an update query with the Access table and the rows in the spreadsheet linked on the unique (key) field.

Yeah, I want to update it. Thanks. But I cant link with Excel, as my data source keeps changing and also I will need the facility to make changes in access itself.

What I am planning actually is, the user generally updates information in Excel, but he should have the feasibility to make changes in access, and uncheck the option to auto update that particular record when imported from Excel.

Please let me know if I am not clear, so that I will explain with an Example. I will have to accept the fact that I am not good at explanations.

 

[Cronk]

You wrote in your first post
"So when I import data, it copies them as new records...."

Even if the Excel source is changing, the easiest is to copy the spreadsheet to a temporary folder, rename it to the same name each time, and link to this. Then use an update query.

 

[Frothingslosh]

If you don't mind using VBA rather than a macro, I can provide you code that would let you use the Open box just like you get when you select "Open" from the toolbar. Beats having to move and rename files each time.

 

[bharatmvs]

If you don't mind using VBA rather than a macro, I can provide you code that would let you use the Open box just like you get when you select "Open" from the toolbar. Beats having to move and rename files each time.

Thank You I just somehow used some code and got it done. I have another requirement now.

I want to create login for different users. I came across this feature in a video. But the idea of using this is, I need to create a log table, in which when I make a change to certain record, it has to capture the user name and time the change has been made, previous text and present text.

Can you please share the code related to this. Please let me know if I am not clear in explaining my need.

 

[Frothingslosh]

This is a good starting point, but be warned, it's somewhat complex and can use up a LOT of space.

http://allenbrowne.com/appaudit.html