Importing Access databases into SQL Server

Hi there,

I have a situation where an application needs to import data from
number of access mdb files on a daily bases. The file names change
every day. The data import is very straight forward:

insert into sql_table select * from acess_table

There are up to 8 tables in each access file and some access files will
have less. So the process needs to figure out which tables exist in
Access mdb file and import them whole into sql staging tables.

Any recommendations are appreciated.

ThanksIt probably depends where you're running the load process from - that's
not really clear (to me) from your comments. If you push the data from
Access, then presumably it's not a problem, because you know which
tables are in each database. If you need to pull from MSSQL, and the
Access database names are always the same, then you could create linked
servers to each one, and get the data that way.

If the Access database names change, and you don't know in advance how
many tables there will be in each one, then you'll need something more
flexible. Personally, I would probably use DTS to connect to each
database, query the metadata to get the table names (although I don't
know exactly how to do that - perhaps an Access group could give more
details), and then load the data dynamically. Or write a tool in Perl,
C# or whatever to dynamically export and import the data via flat files
or ADO.

Finally, one other option would be to convert your Access databases to
ADPs, so you would have the data in MSSQL already. But this may not be
possible or desirable in your situation.

If this doesn't help, I suggest you post some more specific details of
what you need to do.

Simon|||How are these Access files being created daily
with different names and more importantly why?

What kind of bizarre methodology would require
different-named Access files on a daily basis?

GeoSynch

<boblotz2001@.yahoo.com> wrote in message
news:1112217371.433176.279470@.f14g2000cwb.googlegr oups.com...
> Hi there,
> I have a situation where an application needs to import data from
> number of access mdb files on a daily bases. The file names change
> every day. The data import is very straight forward:
> insert into sql_table select * from acess_table
> There are up to 8 tables in each access file and some access files will
> have less. So the process needs to figure out which tables exist in
> Access mdb file and import them whole into sql staging tables.
> Any recommendations are appreciated.
> Thanks