Faster code for inserts? 2

[meckeard]

Hi All,

I am using the following code to insert a value in an Access table, but it seems slow.

Can anyone offer some suggestions for tuning the code?

Dim CurrConn As New ADODB.Connection
Dim CurDB
Dim intReturnID As Integer


'Create our database and connection objects.
Set CurDB = CurrentDb
Set CurrConn = New ADODB.Connection

'Set some connection object parameters.
With CurrConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source = " & CurDB.Name
.Open
End With

'Execute the insert statement with the connection object.
CurrConn.Execute "Insert into tblAllocation(Billing_ID, Allocation_Amount, Unit_ID) values (" & intBillingID & ", " & curAllocationAmount & ", " & intUnitID & ""

 

[JeremyNYC]

Dim db as dao.database
set db = currentdb
call db.execute("Insert into tblAllocation(Billing_ID, Allocation_Amount, Unit_ID) values (" & intBillingID & ", " & curAllocationAmount & ", " & intUnitID & "", dbfailonerror)

Should be a bunch faster.

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[meckeard]

Jeremy,

Yes, it really helped. Now my code runs in about 3 seconds.

One more question.....how could I modify this code to handle a select statement?

I need to get the ID of this newly inserted record. I am currently performing a select on max(ID), returning it using a recordset. I know it's not the most efficient way for returning a small value (integer data type). Is there a better way???

Thanks,
Mark

 

[JeremyNYC]

Mark,

Actually, one of the drawbacks of this method is that you can't return a value from it.

Also, beware of multi-user issues. If there's someone else in the database it's alwasy possible that someone else will insert a record between your insert and your call to get the max(id), which would sort stink.

If you really need that id, you'll probably have to use a recodset, much like your original code.

I don't know ado, I do all dao. It's possible that dao would be faster for something like this. You could run a test.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[jhsurti]

What I do is I have created a separate table called "IDGenerator" which has 2 fields: KeyName and NextNum. I have a function in a module called GenerateID(pKeyName as String)
This will pass the keyname of the ID field u require to generate a new value for. NextNum stores the next number to be allocated to the Key. Once that is allocated, NextNum is incremented by 1.
HTH.

 

[JeremyNYC]

Sure, that will work. But you have to make sure you lock that IDGenerator table, so that only one person can be in there at a time, otherwise the same thing can happen.

There's great code in the Access [Version Number] Developer's Handbook that does this.

If you don't have the book, run out and get it. Best 50 bucks you could spend.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.