I am trying to create a macro to import information from excel worksheet to a table in access. The table contains a primary key (emp number) and when I try to add the data to the field termdate from Excel i get the error that data was lost. I understand that the primary key is the problem. I only want to add the dates to the field terdate for each employee that has been termed. I have multiple dates to enter on a daily basis and was trying to make entry quicker. I put the emp num and term dates in a spreadsheet to import to the table.

Macro :

Opentable
transferspreadsheet
close

Can you give me an idea of how to do this??

Thanks

Hi,
You may want to check to see how the cells are formatted in Excel (ie can't import a currency field into a date field). I come across this issue every once in a while, I usually save the excel file as a text file and import. This way there is not formating.

Hope this helps,
Chris

celss are formatted the same. I think what I am trying to do is have it recognize the emp number and append the record in an existing table for that emp num. Does that make any sense?? I do not want to add the emp num, just the term date for that emp.

So the employee is already an existing record in the table you're trying to append?

Sounds like you want to update existing records, not append new records.

You should use an update query. Pull in your excel spreadsheet as a new table in your db. Then create an Update Query (let's call "Updatetermdates") that has both your original access table, and the recently imported spreadsheet table as the data source, joined 1:1 by emp_number. Then pull down the termdate field from the table you are going to update (orig. access table) as your destination field. In the "Update To" area of this field, put [newspreadsheettblname]![termdate]. Run query.

Your macro will look like this:

TransferSpreadsheet: (transfer Type = Import; Table Name = "NewImportFile" ) *or whatever
OpenQuery: (Query Name = Updatetermdates; View = Datasheet; Data Mode = Edit)

and that's it! You will get a warning prompt when you run the update query. You can turn it off by inserting the SetWarnings = No action above the OpenQuery Action.

If you need help creating the update query, let me know. I'd be happy to upload a demo db.

thanks works great