Importing Mass Data

[jalbao]

I have a local file that can have up to 30,000 records that will need to be imported into an MS Access 2k database.

I've never dealt with a db situation where I needed to insert mass amounts of data. I'm thinking that looping thru each record and executing the command will incur a lot of overhead and, more likely than not, will be very time consuming to insert all 30,000 (or so) records.

Before I start writing the code, I was hoping that some of you may be able to offer some advice on how I should handle this procedure.

tia

 

[NoCoolHandle]

Get rid of access.. MSDE is free and has 90% of the features of SQL Server. It supports bulk load and other features..

Worth the time to check it out..

Re access.. no clue, i agree cursors are slow and klutzy.

Rob

 

[chrissie1]

well if you use some of the access function you could import the data in one go depends on what you need to do with the data. try doing it in access first and then look up the macro or programming equivelant in access (perhaps go to the access forums) and then simply make your program that orders access to do the work.

Christiaan Baes
Belgium

What a wonderfull world - Louis armstrong

 

[ws1]

I'm using the below code for doing a mass transfer into a table.

dest_db1 = "xxxx.MDB"
strexportpath = "c:\parsedata"

Dim Conn As New OleDb.OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dest_db1)
Conn.Open()

strinfo = "Error"
sbuf = "([tool],[date],[lot_cntr],[err_start],[err_end],[err_time],[err_type],[err_detail_a],[err_detail_b], [err_detail_c],[err_detail_d],[err_detail_e],[err_num_a], [err_letter_b],[err_msg_a],[err_num_c],[err_msg_b],[cmdid])"
sbuf1 = "col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18"
Dim Comm3 As New OleDb.OleDbCommand("INSERT INTO error " & sbuf & " SELECT " & sbuf1 & " FROM [Text;Database=" & strexportpath & "\" & ";HDR=YES;FMT=CSV].[ws_error.txt]", Conn)
Comm3.ExecuteNonQuery()

I hope it helps

WS