PDA

View Full Version : Solved: Flush ADODB.Recordset cache



sandam
05-10-2005, 02:30 AM
I am currently using an ADODB.Recordset to access an Excel spreadsheet which we use to maintain a contact list on a matter to matter basis. I'm posting here though because I'm using ADODB and its more a DB type question than a spreadsheet one.


I am getting multiple writes to the table even though I close the connection each time as well as set it to nothing. Now I read that the ADOdb connection has a 5 secs cache of the last query as well as using a lazy write technique. Is there a was to a flush the cache as quickly as possible (milleseconds would be good) and(preferably)/or force immediate writes to the table?

Andrew;?
P.s If this isn't clear enough, let me know.

sandam
05-12-2005, 02:31 AM
solved the problem with out needing this info.

MOS MASTER
05-12-2005, 10:30 AM
Hi Andy, :D

Uhmss..misted this post.

Could you tell us how you got your code more effici?nt? Are you reusing your connection?

Thnxx...:whistle:

sandam
05-13-2005, 12:40 AM
No, actually I was using .AddNew and .Update incorrectly. I found some examples of how the ADODB.Recordset is used in Access and worked from there. If it was a new record i would use AddNew and then assign values to the fields, or just .Update if not. However I found that its best to use .AddNew(Array(field1,field2,...,fieldn),Array(value1,value2,...,valuen)) and .Update(Array(field1,field2,...,fieldn),Array(value1,value2,...,valuen)) and just set a marker to disinguish if its a new or current record. The connection is opened and closed per transaction and its still "blitz vinnig". When I was using the other code (create excel application, assign workbook and ws, do stuff,close everything) it was very slow, too slow in fact.

sandam
05-13-2005, 12:41 AM
-----------Edit--------------
Flushing the cache didn't have anything to do with my problem, it was a poor understanding of the technique I was using.

MOS MASTER
05-13-2005, 11:06 AM
Hi Andy, :D

Sounds good!
Can you put the part of your code here that does the update to your recordset? :thumb

sandam
05-16-2005, 05:26 AM
Here it is

Sub SaveContactDetails()
Dim saveRst As ADODB.Recordset
Dim SearchingFlag
CheckIfNeedCreateFile (MatterNumberForDirectory)
Set saveRst = New ADODB.Recordset
saveRst.Open ContactQueryString, ContactConnectString1 + FilePathForXL + CreateDirectoryString(MatterNumberForDirectory) + _
ExcelFilename + ContactConnectString2, adOpenDynamic, adLockPessimistic, adCmdTableDirect
With Application
.ScreenUpdating = False
With saveRst
SearchingFlag = False
While Not .EOF And SearchingFlag = False
If StrComp(UCase(.Fields(FIELDNAME_1).Value), UCase(Field_Correspondence)) = 0 Then
SearchingFlag = True
Else
.MoveNext
End If
Wend
If isNewContact = True Or SearchingFlag = False Then
.AddNew Array(FIELDNAME_1, FIELDNAME_2, FIELDNAME_3, FIELDNAME_4, FIELDNAME_5, _
FIELDNAME_6, FIELDNAME_7, FIELDNAME_8, FIELDNAME_9, FIELDNAME_10, _
FIELDNAME_11, FIELDNAME_12, FIELDNAME_13, FIELDNAME_14, FIELDNAME_15, FIELDNAME_16), _
Array(CVar(Field_Correspondence), CVar(Field_Title), CVar(Field_FirstName), CVar(Field_Surname), _
CVar(Field_CompanyName), CVar(Field_Address), CVar(Field_Town), _
CVar(Field_Country), CVar(Field_Postcode), CVar(Field_DXNumber), _
CVar(Field_DXExchange), CVar(Field_FaxNumber), CVar(Field_EMail), _
CVar(Field_Reference), CVar(Field_Heading), CVar(Field_ContactType))
Else
.Update Array(FIELDNAME_1, FIELDNAME_2, FIELDNAME_3, FIELDNAME_4, FIELDNAME_5, _
FIELDNAME_6, FIELDNAME_7, FIELDNAME_8, FIELDNAME_9, FIELDNAME_10, _
FIELDNAME_11, FIELDNAME_12, FIELDNAME_13, FIELDNAME_14, FIELDNAME_15, FIELDNAME_16), _
Array(CVar(Field_Correspondence), CVar(Field_Title), CVar(Field_FirstName), CVar(Field_Surname), _
CVar(Field_CompanyName), CVar(Field_Address), CVar(Field_Town), _
CVar(Field_Country), CVar(Field_Postcode), CVar(Field_DXNumber), _
CVar(Field_DXExchange), CVar(Field_FaxNumber), CVar(Field_EMail), _
CVar(Field_Reference), CVar(Field_Heading), CVar(Field_ContactType))
End If
End With
.ScreenUpdating = True
End With
'By only closing but not setting the saveRst-variable to nothing we
'actually use the connection pooling technique.
saveRst.Close
'Release objects from memory.
Set saveRst = Nothing
End Sub

MOS MASTER
05-16-2005, 12:10 PM
Hi Andy,

Yes looks mighty good to me...I like this method.

Will do some playing with it myself..thanks for adding it over here! :yes