Excel to Access conversion

610 pts.
Tags:
Data transfer
Excel 2003
Excel to Access Conversion
Microsoft Access
Microsoft Excel
Accounting came and asked me if a very complicated Excel spreadsheet that they are using for cash receipts could be "moved" into Access. I explained to them the difference between a spreadsheet and a database and that Access could definitely be used to better handle transactions etc. but it would not merely be a matter of a "copy" but a design and build from scratch. Because I do not know much about Access - I thought I would double check my answer here. Thanks.

Answer Wiki

Thanks. We'll let you know when a new response is added.

That’s about it.

Some of the data structure can be re-used, but if the sheets rely heavily on functions or other methods of manipulation, they will have to be redesigned too.

Although you could do data manipulation in Access, I prefer exporting to excel and re-import the results.
Spreadsheets are best at that imho.

Not knowing exactly what functions/Macros you use with the data, the advice given can only be general.

How’s your VBA expertise? I’d examine the way you can use it in this case.

Good luck

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • KSoniat
    Thanks Chippy!
    610 pointsBadges:
    report
  • Dgrainge
    I use access with Excel data in order to use reports. Spreadsheets are good at simple repetitive calcs, data validation and the like. Excel is lousy at outputting summary data in sensible layouts. I Import and link excel sheets to an access database, then build my reports. I never attempt to edit data directly from Access because in my experience that leads to data corruption in Excel. Thanks Microsoft for allowing me to do so and cocking it up. Access 2000, 2003. In order for Access and Excel to link cleanly, you have to have tables in Excel which are laid out suitably. If you have bits of data all over the place, forget it. Similar data spread over miltiple sheets - forget it. If you know about normalising databases and the like, fine - but if you don't, please bear in mind that database design is a whole degree more complex than building a table or two in Excel.
    70 pointsBadges:
    report
  • KSoniat
    DGrainge; Thanks. Accounting has had these for awhile and I know they will be a mess which is why they want a different vehicle. They were supposed to be a short term solution for a conversion which is now high priority and in the works. K
    610 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following