Access / Excel Integration


I have a need to work between a new Access 2010 / 2013 database that is being built from an Excel spreadsheet.  I have multiple vlookups on the one I am converting to Access but I also have multiple other excel spreadsheets that were using the data in the old Excel sheet that I am converting to Access.  The project is large enough that I cannot do everything at once and so I am looking for some advice on the best way to keep everything up to date as real time as possible.

What is the best easiest way to work between the two real time and does 2013 give me any advantage over 2010?  

Open to suggestions before I jump down the rabbit hole.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Are you saying the vlookup values might change for the spreadsheet you are importing into Access before the other spreadsheet can get uploaded?  In my opinion you should not load the vlookup fields to Access if the other Excel spreadsheet you are vlooking up from will also be loaded to Access.  The lookup values should be determined in Access after all the spreadsheets are loaded to Access.
Jeffrey CoachmanMIS LiasonCommented:
Know that there is no way we can give you any targeted advice with out actually seeing these actual sheets, and you explaining all the detail to us.

Access and Excel work differently with "related" (Linked) data.

So while you can certainty substitute Dlookup for Vlookup (in some cases)
...The other "Linked" values must be handled differently .
So, at best you would need a few relationships between the new tables/sheets, worst you will need a complete redesign in some cases...

Helen FeddemaCommented:
I wrote a whole ebook on working with Excel data in Access.  Can't provide a link here (that would be shameless self-promotion), but you can find it on the Office-Watch Website.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.