You should always try to bulk-upload, and packing multiple SQL's into a Transaction is never a bad idea (Rollback!).
Though, i have no idea if bulk-upload with parameters is possible.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
How that "batch" works? Is that similar to the transaction, but without a rollback feature and not breaking at first failed commit?
Don't think so, since UpdateBatch is a Method of the ADODB.RecordSet-Object
Basically it works like this:
RecordSet is (implicitely?) set into "Edit"-mode (for lack of better word)
You loop through the RecordSet(s), and edit Fields you want to edit
Never used it, but should also work with Add (or is it Append?)
Disadvantage: You don't use SQL, but you "edit" your RecordSet-Fields directly, so in that way: No sanitizing like if you use Parameters in a SQL-Statement
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
Not every DDBMS has a BULK INSERT or similar thing. But there are still things you can do to achieve better performance.
Here's a demo (Project1) that creates a Jet MDB, creates one table, and then "firehose writes" 1000 rows and exits.
Excerpt:
Code:
Dim Connection As ADODB.Connection
Dim FieldNames As Variant
Dim I As Long
ChDir App.Path
ChDrive App.Path
LoadPics
With CreateObject("ADOX.Catalog") 'Late-bind ADOX because unlike ADODB it doesn't
'maintain binary compatibility between Windows
'versions.
.Create "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;" _
& "Mode=Share Exclusive;Data Source='Demo.mdb'"
Set Connection = .ActiveConnection
End With
Connection.Execute "CREATE TABLE `Data`(" _
& "`ID` IDENTITY CONSTRAINT pkData PRIMARY KEY," _
& "`TextData` VARCHAR(255) WITH COMP NOT NULL," _
& "`PicData` IMAGE NOT NULL)", _
, _
adCmdText Or adExecuteNoRecords
FieldNames = Array("TextData", "PicData")
With New ADODB.Recordset
.CursorLocation = adUseServer
.Open "Data", Connection, adOpenForwardOnly, adLockOptimistic, adCmdTableDirect
For I = 1 To NUM_ROWS
.AddNew FieldNames, Array(NewText(), NewPicture())
Next
.Close
End With
Connection.Close
MsgBox "Data table in Demo.mdb created"
Opening the database exclusively isn't always practical, but when you can do it you bypass a lot of locking and its overhead. Not every DMBS supports adCmdTableDirect either.
There is also a Project2 included. This is a simple "browser" for stepping through the rows in the created database table. Notably, it has no code at all.
Reply With Quote
Originally Posted by Dragokas
