Text, BCP and Mass Insert

[jasonp45]

Hi. What I'm trying to do is pump out up to a million rows from Access so I can put it into SQL Server (using VB.NET). I'm considering using text so I can avoid DTS and instead use BCP or mass insert. So I'm wondering if there is some analogous fast text-dumping feature in Access that I can programmatically call via VB.NET. I'm running SQLServer 2000 on Windows XP & 2000.

I wouldn't mind using DTS but I don't want to make a package if possible and can't seem to find a DTS class in .NET. Also I understand that the next version of SQL Server is going to involve a lot of changes to DTS so I'd prefer avoiding re-writing my code.

 

[ClayG]

I try to avoid DTS too.

A different idea: in SQLServer create a "linked server" reference to the access database. Then transferring data is simply Insert .. Select from accessdb..table.

I havent got an example handy, but I have an example of using OpenRowSet, which is yet another way:

Select ...
  from OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd',
  	Orders) AS a

The "4.0" might need changing.