query in excel

[vbCNEW]

Hi!!! could anyone please help me about querying in excel..

heres the scenario..

i have 2 sheets(sheet1,sheet2)

sheet1 = contain the correct answer
ex. col1 -- 3333 | col2 -- jose reyes
sheet 2 = contains only tha code... ex. col1 -- 3333 (shuffled)
(note.... record count is more than 5000)
now using the sheet1 as basis i need to know the corresponding NAME of the code in sheet 2...

actually i have a code... its working fine.. but i always run out of memory... pls help... its making me nuts...

heres the code::::

form___________
Private Sub CommandButton1_Click()
Dim st As String
Dim cnt As Long
cnt = 2
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\NAME.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"

Do
st = SearchNAMElink(ActiveSheet.Cells(cnt, 2).Value)
ActiveSheet.Cells(cnt, 4).Value = st
If ActiveSheet.Cells(cnt, 2).Value = "" And ActiveSheet.Cells(cnt + 1, 2).Value = "" Then
Exit Do
End If
Label1.Caption = "RECORDS :" & cnt - 1
cnt = cnt + 1
DoEvents
Loop While True

Set objConnection = Nothing

Unload Me

End Sub

inA MODULE___________
Public Function SearchNAMElink (stcode As String) As String
If stcode = "" Then Exit Function
objRecordset.Open "Select link FROM [NAMElinks$] where CODE = '" & stcode & "'", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
If Not objRecordset.EOF Then
SearchOthlink = Format(objRecordset!link, "00000")
End If
DoEvents
objRecordset.Close
End Function

:pretty:

[davidcairns]

firstly put this above Unload Me

Set objRecordset = Nothing
objConnection.Close
Set objConnection = Nothing

Always close and kill all objects

See what this does to it

[vbCNEW]

sir your right... i forgot to put it back because im debugging... i have it then!!!!

but the problen is my memory is going up inside the SearchNAMElink function

right aftrer this line...

objRecordset.Open "Select link FROM [NAMElinks$] where CODE = '" & stcode & "'", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

and when i put the
SET objrecordset = notrhing

before the function leaves, hthe next time the function was called... i need to create again the objrecordset....

same problem.... memory goes up...

which i think it shoudnt!!!! pls help again...

[davidcairns]

You should be able to work with the same recordset object, opening and closing it each time, without recreating it. One thing you can try is to change your locking since you only want to read the data

adOpenStatic, adLockOptimistic
to
adOpenForwardOnly, adLockReadOnly

Regards

D

[vbCNEW]

could you help me please how to declare adLockReadOnly and adOpenForwardOnly?

i dont know the value....

[davidcairns]

Change this line

objRecordset.Open "Select link FROM [NAMElinks$] where CODE = '" & stcode & "'", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

to this

objRecordset.Open "Select link FROM [NAMElinks$] where CODE = '" & stcode & "'", _
objConnection, adOpenForwardOnly, adLockReadOnly, adCmdText

[vbCNEW]

sorry if im not that straight to anwer you last time..
i know what you mean.. but it gives an error!!!

something like adOpenForwardOnly, adLockReadOnly are not known... they dont have any values at all..
i know its a built-in const... but im using excel only.. i dont have vb studio here yet...

just like what i did..
Const adOpenStatic = 3
Const adLockOptimistic = 3

hope u understand it..

[vbCNEW]

by the way... thank you very much for your time..
one more concern if u may.....

im a vb game programmer... do you know how can i use my knowledge ? im using GDIPLUS!!!... i ve done a lot of games,, BINGO games, REEL games, and other casino games.... thanks again..

[davidcairns]

You should add ADO (ActiveX Data Objects) as a reference then in the code editor of Excel. If you don't want to do that then the value of ForwardOnly is 0 and ReadOnly is 1