Hi Guru's,

I apologise if this has been answered!

I am trying to import an Access mdb file with DTS into SQL Server
2000. I have the following problem.

1) The mdb files vary considerably. Is there a way to import all
tables and fields dynamically?

2) Is there another way to transfer data from Access to SQL without
building queries and transferring line by line.

Caveat: I need to do this programmatically. Basically I have various
users uploading an mdb file and need to import their disparate data
into its own SQL database. So it's web technologies that need to be
used.

Thanks for any and all help!

The short answer is no. The problem here is that Jet and SQLS are not
interchangeable database engines. There are no Jet equivalents for
SQLS features, and no SQLS equivalents for some SQLS features. There
is data type incompatibility (more or less depending on which version
of Jet you are upsizing). Validation rules are handled differently.
Datetime ranges are different. Optimally you will rewrite and
rearchitect your Access app to take advantage of SQLS features that
simply don't exist, like stored procedures, etc. Moving the data over
is only the beginning. There's some whitepapers around that discuss
some of the issues -- use this for a starting point--it has links to
other relevant papers:
How to Convert an Access Database to SQL Server (Q237980)
http://support.microsoft.com/default...;EN-US;Q237980

--Mary

On 1 Jul 2004 07:34:16 -0700, mike (AT) spirit (DOT) co.za (sorCrer) wrote:

Thanks Mary,

I had pretty much worked this out. But wanted to check with the more knowledgeable.