Re: What's the best way to open a recordset for insert only?

As it turns out, it was necessary to close and reopen the recordset
periodically. I really don't think I would have come across this if I
hadn't picked at the thing like an itchy scab.

This is where "ADO choking on that much data" came into play. I found that
the recordset object leaked memory to the extent that the program eventually
crashed. To implement your suggestion, I tried:

1. After verifying the table structure

Set m_active_rs.activeconnection = Nothing
m_conn.Close

2. Just before UpdateBatch:

m_conn.open
Set m_active_Rs.activeconnection = m_conn

3. Just after UpdateBatch, do the stuff in step 1.

Unfortunately, this did not stop the memory leak. If, instead, you close
and reopen m_active_rs after every UpdateBatch, memory usage appears to be
stable. I'm only getting 800-1200 records per tick now, but I hope that
improves in the production environment.

"Bob Barrows [MVP]" wrote:

I should have added that you could close that connection while the
recordset was disconnected, and then reopen it just before reconnecting
it.

Spencer wrote:
Just in case you were wondering, the application is now inserting
3000-4000 records in the test environment per tick of the progress
bar/call to BatchUpdate. Setting rs.ActiveConnection to Nothing
between batches doesn't seem to improve that significantly.

"Bob Barrows [MVP]" wrote:

Spencer wrote:

So it was a failed experiment, and it looks like I'll have to use
the recordset. But I'm wondering if there's a way to open the
recordset so that I can call AddNew on it, but doesn't query the
entire table first.

Use a disconnected recordset opened using a sql query that includes
"WHERE 1=2"

rs.CursorLocation = adUseClient
m_rsname = "select <fields> from " & m_rsname & " WHERE 1=2"
rs.Open m_rsname, m_conn, , adLockBatchOptimistic, adCmdText
Set rs.ActiveConnection = Nothing
'add your records, then
Set rs.ActiveConnection = m_conn
rs.UpdateBatch

.

Relevant Pages

  • Re: Releasing memory
    ... "nisant" wrote: ... Then use SQL to create a third recordset of the 70*3000 ... essentially defragment the memory, and that's what's taking a long time. ... >>> At program termination the db structure is released setting to nothing ...
    (microsoft.public.vb.general.discussion)
  • Re: filter with vb code
    ... I guess ADO classic doesn't interest them. ... There may be no recordset in your post be there certainly is a memory ... SQL involving an open workbook causes a memory leak. ...
    (microsoft.public.excel.programming)
  • Re: VB6 not releasing Memory when Form is Unloaded
    ... I create a recordset and then display the results in a DataGrid using ... I haven't had any memory problems, but I'm not dealing with 20k ... >> End Sub ... >> With the above example, after you close the form2 and unload it, if you ...
    (microsoft.public.vb.general.discussion)
  • Re: how to process data when internet connection broken?
    ... keep it in memory using DataSet, but my client don't want to keep recordset ... It is in XML ... >> DataSet) and update the SQL server and local data with the difference. ...
    (microsoft.public.dotnet.faqs)
  • Re: VB6 not releasing Memory when Form is Unloaded
    ... escalating workstation memory use considerably. ... Also, while populating the grid/listview ... > Private Sub Form_Unload ... > 'close and destroy the recordset ...
    (microsoft.public.vb.general.discussion)