update utility

  • Thread starter Thread starter marta
  • Start date Start date
M

marta

Hello,
I have a linked excel table in access and I have an access
table with the same number of columns (same data type).
What is the best way to automate a process of copying the
data from the linked excel table to the access table
(adding the copied fields as new records)?
thanks for your help,
Marta
 
Marta,

What you need is an Append query. From the database window go to the queries
tab and click on New, then Design. In the Table selection window that pops
up select the linked Excel spreadsheet and add it to your query. Then get
all the fields you need down to the design grid. Next, go to menu item Query
Append, and in the pop up select the table you want to add the new records
Click to expand...
to. Finally, match the table fields to the linked Excel sheet fields in the
grid (Append To line) and save.

Now just run the query to append your new records. If you need to take this
a step further, you can include the append operation in a Macro, as an
OpenQuery action.

HTH,
Nikos
 
Thank you, this process is very useful. the only problem
is that when i try to run the append query, I get a
validation rule error message. I have verified that the
the data types in both tables are identical and that
there are no validation rules (at least that i can see).
is there some sort of restriction for running this type of
query on a linked excel sheet? why am i getting these
validation rule errors?

thanks so much for your help,
Marta
 
Marta,

I can think of a possible reason, which is that your spreadsheet may have
"rubbish" rows beyond the last actual data row (which you son't see in
Excel); this may happen if you have cleared contens in rows of Excel, but
not acutally deleted them. If this is indeed the problem, then if you open
the linked ssheet in Access in datasheet view, you should see them as empty
records. Also, if you open the ssheet in Excel and press Ctrl+End, you will
be taken to a cell beyond the last data cell, rather than right at it. In
that case, you need to open the ssheet in Excel, mark all rows after the end
of your actual data down to the last one, and delete them, then save.

HTH,
Nikos
 
Back
Top