Linking Access 2007 DB and Excel

[sgtschultz]

Hello-

I have a database created where I export the contents of a table into an
excel spreadsheet. Now that works just fine. My question is about linking
this spreadsheet to the table in the database so if information is evtered
into either location it will merge together. I don't know if this is clear
so... I have access and the other employee does not. So in order for the
customer table to stay up to date I enter information in the database and he
enters it into the spreadsheet. I want to be able to merge the 2 and not lose
information that was entered on either end.

thanks in advance for the help and sorry for the confusing question.

-Josh-

 

[J_Goddard via AccessMonster.com]

Hi -

You are getting yourself into a bit of a messy situation here. From Access
2002 (2003?) on, although MS Access can export to Excel, and Excel
spreadsheets can be linked to and read by MS Access, they cannot be updated
by MS Access. So, the only way to "update" the Excel spreadsheet will be to
re-create it each time.

Secondly, and perhaps more importantly, how would you know who updated what,
and when? You would need to have a field to track the date and time of
changes in each record. Tracking the date/time is relatively easy in MS
Access, but not Excel, though each would require VB code I think. Not only
that, but Excel has very little data verification / validation capability
when just using a spreadsheet.

What is the unique key for each record (you DO have one, right?) - how would
you know what the key value for the next new record is? It might be
different depending on who enters the new record, or it might be generated
during the merge process.

And how would the merge process work? Presumably you would use the Excel
spreadsheet to as a source to "update" the MS Access database, but how would
you detect differences? (This is where the date/time of update is important).
And what about deletions? If a record was deleted from the spreadsheet, how
would the Access database know which one it was?

I ask these questions not so much that I want answers to them, but so that
you can see the difficulties you might be getting into. It's not that what
you want to do is impossible - but it would require careful thought on data
structres and programming required.

I really suggest that you spend the relatively few dollars (?) required to
get MS Access for the other employee as well, and if you have a network, use
a split database setup to let both of you update the same data. You also
would have the benefit of being able to develop more applications of course.

Don't see this as a negative diatribe - I just want to help you accomplish
your task with minimal anguish.

Cheers!

John