I'd like to get some help on what to try.

I've got an application that reads message  periodically from an MSMQ queue
and logs the messages to SQLServer7.  The problem I have is that after the
application has been running for a while (a day or more),  the system cpu
usage will go to 100% or I will get an error message that the machine is low
on virtual memory.  I've tracked the problem down to ADO.  My logging
application opens one connection and one recordset (rs.Open "TableName",
connection, adOpenForwardOnly, adLockBatchOptimistic, adCmdTable).  I call
rs.Addnew strFieldList, strFieldValues repeatedly throughout the execution
of the application.  The memory used by my logging application grows
steadily over time and seems to be related to the number of rows in the
table.  If I use a loop to insert records as fast as I can, I get to about
300,000 rows and the system goes to 100% cpu usage an basically stops
inserting records while it thrashes.

Any ideas about what is going wrong?  Doesn't somebody else have a process
that runs for long periods of time inserting thousands of records?

Thanks for any help you can give,

Curtis

Sample Code:

    Note: My sample app can insert rows based on a timer event or use a
"For" loop so both modes of operation are shown in this sample code

Private Sub Form_Load()

    lngRecCnt = 0
    OpenStuff

End Sub

Private Sub OpenStuff()
    'Open the connection.
    Set objADOConnection = New Connection

    objADOConnection.ConnectionString = _
        "Provider=SQLOLEDB;Password=myPWD;initialcatalog=MyCatalog;Persist
Security Info=True;User ID=MyID;"

    objADOConnection.Open

    Set objRS = New ADODB.Recordset
    objRS.CursorLocation = adUseClient
    objRS.Open "StateChanges", objADOConnection, adOpenForwardOnly,
adLockBatchOptimistic, adCmdTable

End Sub

Private Sub CloseStuff()
    objRS.Close
    Set objRS = Nothing

    objADOConnection.Close
    Set objADOConnection = Nothing

End Sub

Private Sub InsertRow()

Dim varFields As Variant, varRecordData As Variant
Dim strSQL As String

    '   Set fields
    varFields = Array("PropertyName", "PropertyValue", "Comments")
    varRecordData = Array("TestName", "TestValue", "TestComment")

    '   Add new record
    objRS.AddNew varFields, varRecordData

    SetProcessWorkingSetSize GetCurrentProcessId(), &HFFFFFFFF, &HFFFFFFFF

'    strSQL = "Insert into StateChanges
(""PropertyName"",""PropertyValue"",""Comments"") values
('TestProperty','TestValue','This is a test')"
''    Debug.Print strSQL
'    Set objRS = objADOConnection.Execute(strSQL)

    lngRecCnt = lngRecCnt + 1
    Form1.lblRecordCount.Caption = lngRecCnt

'    If lngRecCnt Mod 1000 = 0 Then
'        CloseStuff
'        SetProcessWorkingSetSize GetCurrentProcessId(), &HFFFFFFFF,
&HFFFFFFFF
'        OpenStuff
'    End If

    DoEvents

End Sub

Private Sub Form_Unload(Cancel As Integer)
    CloseStuff
End Sub

Private Sub Timer1_Timer()
    InsertRow
End Sub

You could try issuing an INSERT query across the connection instead of
opening a cursor.

You may even want to track how frequently the record adding occurs and judge
if keeping the connection open for the duration really saves much overhead
in terms of performance.

You could even try detaching the recordset until shut-down and then
reconnect for the batch update.

Michael