Importing Excel to Access > Objective: Update records

[Douglasrac]

Hi,



I would like to every month, update and add records in my database using excel. But I don't know how I will tell Access which record should be updated, because when I use the key it says it cannot overwrite the key. But its not to overwrite the key, its just to recognize which line should be updated.

So I have, for example, 2 forms and 2 tables.

Table 1/Form 1:

N of Client (key)	Name
1	Douglas

Table 2/Form 2:

N of Record (Key)	N of Client (just load from previous from to link this ke to table 1 key)	Product name	Date of Start	Date of End	Situation	State
1	1	Insurance	01/Set/2014	01/Set/2015	New	Valid (actually a check box marked)

What happens is that in 01/Set/2015 I want to update this record to: Situation (Renewed) and Uncheck the checkbox.

And then create a new record that will have the exact same things except the date will be different (01/Set/2015 to 01/Set/2016), Situation is renewed and the checkbox is marked. And of course the new key will be 2, but access should generate the key.

So the problem is how I setup my excel sheet in a way that Access knows it had to update record with key (N of Record) instead of adding a new record? Or instead of thinking that I want to add a key (which is forbidden - in my tables keys can only be added automatically).

[June7]

Why do you want to use Excel?

You are wanting to generate a custom unique identifier. This is common topic. Search forum. Here is one: https://www.accessforums.net/access/...ers-21361.html

Having to edit an existing record because of entry of another record is poor design. You know the policy is renewed because there is a later record. No need to save that into the earlier record.

[Douglasrac]

I want to use excel because I don't know any other way.

I need to register the "renew" because later record can mean another product. So just because there is another record (valid or not) it doesn't mean the current one is renew. It can have several active records.

[June7]

Then I don't understand what you are doing. Data entry/edit into Access tables is easily accomplished with forms bound to the tables.

Also not understanding the data structure.

[Douglasrac]

What I'm trying to do is add data by importing because that would be faster then manually input every single data by forms.

Perhaps I can create a form that will have a tabular shape and add data directly there.

[June7]

How does the data get into the Excel to begin with?

Forms can certainly be designed to look like a table.

[Douglasrac]

The data will get into excel by manual input and data generated by the database. So yes, a form looking like a table would help more.

But you know what would really help? Me inputting the data when its generated, instead of letting it accumulate

I give up this project, thanks anyway.