Export Data from SQL Server to Access without DTS via .NET

[Jason]

I want to export up to a million rows from an SQL Server table (2000 Server
on Win2003 Server) to an Access 2003 table via a VB.NET program running on
Windows XP. I don't want to use DTS. I would optimally like to use Bulk
Insert commands or something like that. I can figure out how to get it out
of SQL Server pretty easily perusing BOL, but I'm not sure how to then get
it into Access. Does anyone have a fast and easy way to accomplish this?
Thanks!

 

[Guest]

Jason,

One idea that I can think of is export the data from sql to a specified
location then grab the MS Access database and use the docmd.transferdatabase
command within Access. I have posted a couple of links below to aid you if
you choose to explore this idea.

Hope this helps.

 

[Paul Clement]

¤ I want to export up to a million rows from an SQL Server table (2000 Server
¤ on Win2003 Server) to an Access 2003 table via a VB.NET program running on
¤ Windows XP. I don't want to use DTS. I would optimally like to use Bulk
¤ Insert commands or something like that. I can figure out how to get it out
¤ of SQL Server pretty easily perusing BOL, but I'm not sure how to then get
¤ it into Access. Does anyone have a fast and easy way to accomplish this?
¤ Thanks!
¤

Not sure whether you are working with an existing Access table but the following uses SQL to import
into a new table:

Function ImportSQLServerToAccess() As Boolean

Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
& _
"Data Source=e:\My Documents\db1.mdb")

AccessConn.Open()

Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO Orders FROM [Orders]
IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];",
AccessConn)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()

End Function


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Sahil Malik]

A million rows in access? OUCH !!

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik