Automatic import from Access

[alvus]

Hello, I am trying to migrate around 200 Access databases, each one with 150 big tables to SQL Server 2000, for the moment I am doing it database by database with the export option in Enterprise manager, but it seems to me that there must be a cleverer and faster way, Can anybody help me?
On the other hand some of the tables have a field with dates with the year 2500, and that produces an error in SQL and I have to change them to 2050, for instance, is ther a way to report the mistakes to a file and know wich tables produced the error instead of seeing it at the end of each import?
Thanks in advance, that would save me a lot of time and effort, and head aches...

 

[Glowworm27]

I have the same problem. Im still working out the kinks

I created a DTS package to look at all the files then get the filename, assign it to the access source, then the transform task will import that mdb.

The problem I have is automatinf the import routine to iterate through all the files.

The problem I have is once the transform data task connects to the MDB, and imports the file, I have no way of releasing the connection to the mdb file so i can delete it, and import the next one. I can do it just fine when importing text files.

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[WhiteKnight57t]

you have to save Date as DateTime in SQl not smalldatetime

Read Bol datetime data type, overview

==========================
Sorry to be terse
some say it's a curse
I know it's worse
I'm just diverse
-Tim