Create an Import Table

[Brook]

Good Day,

I am trying to create table that I import Excel Files into. I am assuming
that I will need a form to start the import process?

Can anyone help?

Thanks

Brook

 

[John Nurick]

Hi Brook,

The usual way to import data from Excel is to use the File|Get External
Data menu command.

 

[Brook]

I understand how to import information into Access from excel. But what I am
trying to accomplish is this:

I have a table in my database that I will need to import an inventory file
into on a periodic basis (once a week), and I want to set up some sort of
"automation" for that. Maybe a button "Import Inventory", that will allow me
to search for my file, select my file, then import it.

Does this help?

Brook

 

[John Nurick]

You'll need to write a VBA procedure to do this, and a command button on
a form is a convenient way to call the procedure.

There's ready-made code at http://www.mvps.org/access/api/api0001.htm
that will call the standard Windows File Open dialog so the user can
specify the file to import.

Then the VBA statement to import data from Excel is
DoCmd.TransferSpreadsheet.

There are lots of possible complications (e.g. field types, what to do
with data that's already in the database when you import from a new
inventory file). If each week's file is a complete replacement for the
previous one, consider (instead of importing) just linking to the most
recent worksheet.