General discussion

Locked

How to link excel to access

By k_and_l ·
I have 500 or so purchase orders, each on an excel spreadsheet. I would like to link the information in all spreadsheets to an access database, so that I will be able to pull up the information by PO#, Part#, Job#, Discription, Amount ordered, Vendor, or Price.

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

How to link excel to access

by BenWagg In reply to How to link excel to acce ...

When you say "each on an excel..." do you mean that you have 500 individual files in your PO folder? (that po' folder...) )

What version of Excel & Access do you have? Access 2k will natively link to Excel data files (*.xls), but rather than create 500 link tables IMO it might be better to consolidate all those files by making an Access db to hold all the existing orders and also for creation of future orders. Migrating 500 orders is a lot of legwork, but not near as much as 1000 or 2500... Once the data is in Access, then you can just query for those fields.

A2k has an excellent Wizard for setting up an Order Entry database, which you may want to use after examining the "Northwinds" sample database. Northwinds is a pretty completecompany-database and has examples of most, if not all, of the features in Access.

Collapse -

How to link excel to access

by k_and_l In reply to How to link excel to acce ...

Poster rated this answer

Collapse -

How to link excel to access

by k_and_l In reply to How to link excel to acce ...

Yes. I have 500 seperate files. I inherited this, then added to it. I am using Excel 2k & Access 2k. I've tried the wizards and building my own, but to no avail. The way the excel sheets are designed doesn't seem to work. Thanks.

Collapse -

How to link excel to access

by ananthap In reply to How to link excel to acce ...

If all the Excel files have data in identical cells, then it's possible by writing macros to either consolidate to a new (single) Excel sheet and then on to Access.

Otherwise, since you have clearly made up your mind not to continue using the Excel practice, it might really make more sense to do some smart data entry. Eg. Keep both Excel and Access open, cut from Excel and paste to Access. At 500 POs, averaging about 2 parts per order - say, if you decide to structure your Access table to hold just the important info. like "Part#, Job#, Discription, Amount ordered, Vendor, or Price", with a link to any special, textual information, about 3-4 days work should do it.

HTH

End of mail.

Collapse -

How to link excel to access

by k_and_l In reply to How to link excel to acce ...

Poster rated this answer

Collapse -

How to link excel to access

by k_and_l In reply to How to link excel to acce ...

This question was closed by the author

Back to Software Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums