50+ Excel Spreadsheets into Access

[jillmarchand]

Hi,
I'm terribly new to Access and am mighty confused!

I'm creating a DB to use with www.cart32.com shopping cart. Here's my question.

I have 50 spreadsheets in MS Excel from 50 different manufacturers (each manufacturer's spreadsheet has the same type of info: : Part #, Description, Price, Weight). I have one main DB in Access called Products.mdb. When I import or link the Excel spreadsheets to Products.mdb they become tables attached to the DB yet when I open the actual products.mdb there is nothing
listed..?! When I open the individual tables, everything is there from the Excel spreadsheets. So are tables part of the main database? I'm confused.

Basically, I need some advice on how to manage 50-100 spreadsheets of various manufacturer's products info, import it easily and quickly into MS Access from Excel into a single database, be able to update it when I get new spreadsheets and be able to FTP the DB to my account with Cart32.

HELP! Thanks!

 

[ScottGem]

If you import or link the excel sheets that does not append the data to the existing table. What you need to do is run an Append query to add the data to your Products table. Later on, when you have updates, you will need to run an update query as well.

You can automate this by importing to a temp table, then running the Append and Update queries from the temp table. By using the same name for the temp table and deleting it before importing, you can just run this from a module.

 

[jillmarchand]

re:

Hi Scott,
I appreciate the reply, however I have no idea how to run an Append query or update query. Do you know where a simple tutorial is on how to do this, or can you explain the steps that I need to take in order to do this?

Thank you very much!
Jill

 

[ScottGem]

Did you try Access Help? it will show you how to create such queries. You can then easily create a macro starting with TransferSpreadsheet to import and OpenQuery to run the queries.