Bulk Insert into an Access database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hallo

I have a very large dataset that I am inserting into an access database using INSERT Commands. The problem is that individually inserting row by row is too slow. What is the fastest way to insert a very large dataset (or flat file) into an access database via VB.NET?

Thanks...

Nigel...
 
SQL Server Data Transformation Service (DTS) is the easiest way to get a
large amount of data from one place to another. It works well with a variety
of database and I have used it to move from a non-SQL Server DB to another
non-SQL Server DB on numerous occasions.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
Nigel Findlater said:
Hallo

I have a very large dataset that I am inserting into an access database
Click to expand...
using INSERT Commands. The problem is that individually inserting row by row
is too slow. What is the fastest way to insert a very large dataset (or flat
file) into an access database via VB.NET?
 
Nigel,

I had much better result by looping through the data and saving it to a
comma-delimited file, after which I do an "Insert Into..." from the CSV file
into a new access-file.

Summarized it looks like this :

dim sw as system.IO.streamwriter = new system.IO.streamwriter("temp.csv",
false)
for i as integer = 0 to dsOrig.Table(0).rows.count -1
field1=....
field2=....
field3=...
sw.writeln( field1 & ";" & field2 & ";" & field3)
next
sw.close

dim connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0; Datasource=" &
application.startuppath & "\; Extended
properties=""Text;HDR=no;FMT=Delimited;"""

dim cn as new oledb.oledbconnection(connectionstring)

dim sql as string = "INSERT INTO myNewTable (myNewField1, myNewField2,
myNewField3) in 'myNewAccessFile' SELECT field1, field2, field3 FROM
temp.csv"

dim cmd as new oledbcommand(sql, cn)
cmd.executeNonQuery

Regards,
Jan

Nigel Findlater said:
Hallo

I have a very large dataset that I am inserting into an access database
Click to expand...
using INSERT Commands. The problem is that individually inserting row by row
is too slow. What is the fastest way to insert a very large dataset (or flat
file) into an access database via VB.NET?
 
Thanks Greg

Unfortunately the bulk import is to be carried out on home PCs that would not have access to dts utilities. What I was looking for was something like a BULK INSERT command that would work on an Access database or some API that could crontrol the Access Import functionality..

Thanks again...

Nigel...
 
there is no fast bulk insert for ado or ado.net. you have to use DAO com
library to get this feature.

-- bruce (sqlwork.com)



Nigel Findlater said:
Hallo

I have a very large dataset that I am inserting into an access database
Click to expand...
using INSERT Commands. The problem is that individually inserting row by row
is too slow. What is the fastest way to insert a very large dataset (or flat
file) into an access database via VB.NET?
 
Back
Top