Importing multiple data files

[C Tate]

I have a couple of problems with Access and would be grateful if anybody
could offer any advice.

First, I work with an Access 2000 database at work. At the end of a quarter
various companies send us a data file (usually called something like
anonymous data.mdb). Each file from every company is called exactly the same
thing. We save these files into folders which are usually given the company
name.

We then go about importing each file into our own Access database. This is a
very tedious procedure, especially as it involves importing 80 or so of
these damned files. I'd love to be able to automate the whole thing. I dream
of being able to put them all in one folder and import the lot in one fell
swoop. Is there any way at all I could achieve this?

The second problem is this. Sometimes I have to remove a particular
company's data from that big combined file. Once again, this is a big
headache! Sometimes it is just quicker to start importing again from
scratch. Could I automate the procedure with a macro? It would have to look
through the tables for a particular 'company name' then delete all the rows
of data for that company.

Help!

 

[Ken Snell [MVP]]

Automating your process is doable, but not one for the timid or
faint-hearted to set up! < g >

VBA in ACCESS allows you access to the files in their folders -- you can
rename files, import data from them, link to them, read directly without
importing, move files from one place to another, copy files from one place
to another, etc.

If you have a defined directory/folder structure that allows you to "walk"
through folders and find files, and you can identify the data in those
files, then it's a doable method. It can be complicated.

A lot of the work needed to make this work automatically will be spent on
programming around error conditions that can occur when a file isn't there,
renaming so that the names stay unique, etc.

What can you tell us about the actual things you are doing manually right
now... do you manually link to table(s) in the file(s), or do you actually
import the data via manual import of the table, or do you run a query to
append data, or ...

We'll need some specific info to try to get you started on the path. How
much do you know about using VBA to manipulate files/folders, etc.?

 

[C Tate]

Hmmm ... maybe I am on to a non-starter here. I know nothing about vba
unfortunately.

To answer your other question, we are manually importing the backends from
other users.