recordsets AND arrays 4

[bubarooni]

Every three months I get a disc from a vendor that contains a bunch of sales records. The data is in the form of .mbd and consists of about 175,000 records. Our billing dept is only interested in one field and only in the unique values that occur in that field.

I was able to get the unique values by doing a 'select distinct' and dumping the results into an excel spreadsheet but the billers aren't to happy thumbing thru about 40 pages to look up the value they are interested in.

Now I've got a little vb app that loads the values into a datagrid on form_load and then has a text box and command button for a search function that dumps the search result into the datagrid. The only thing is, it takes about 8 seconds for the program to load and it takes about 8 seconds for every search. I've got users over there doing their nails during searches!

I am sure this is because I'm searching the .mdb each and everytime. The initial search for unique values on form_load returns about 900 values. Is there a way to dump those 900 values into an array (or a disconnected recordset?) and then have the user searches just comb those 900 values instead of the .mbd files 175,000 records?

 

[Golom]

You may want to add a couple of steps to your initial load scenario. Create a temporary table that will contain the distinct values with an index on the field that you are going to search. Then (assuming ADO)

cn.Execute "Delete * From TempTable"

cn.Execute "Select DISTINCT TheField, ... OtherFields, ... INTO TempTable From BigTable "

Dim rs As New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "Select TheField, ... OtherFields, ...  From TempTable Order By TheField",cn
Set DataGrid1.DataSource = rs

Then a search is simple

rs.MoveFirst
rs.Find "TheField = '" & txtSearchBox.Text & "'"
If rs.EOF Then
    MsgBox txtSearchBox.Text & " does not exist."
End If

 

[YL99]

It would be better if you use a disconnected recordset. U can use apply further filter on the disconnected recordset and pass it to whatever methods as parameter.

Also u can clone the recordset. I find it more flexible.

 

[bubarooni]

how do i make the table temporary? it is being created permanently in my db during the form_load. It actually works fine, but when i close and then run the project again it give me an error that the table already exists. Other than that, this is working perfectly and is so much faster!

Private Sub Form_Load()

Set cnn = New ADODB.Connection
strConnectionString = "DRIVER=Driver do Microsoft Access (*.mdb);DBQ=\\Wbp_app\c\INSACCTS\INSACCTS.MDB;Uid=admin;Pwd=;"cnn.Open strConnectionString


    cnn.Execute "SELECT DISTINCT Right(Trim([Carrier]),7) AS Ins_Number, [Carrier] INTO TempTable3 FROM tblAccounts"
   
    rs.CursorLocation = adUseClient
    rs.Open "SELECT * FROM TempTable3", cnn
    Set DataGrid1.DataSource = rs
DataGrid1.Columns(1).Width = 6000

End Sub

 

[BobRodes]

Yes, exactly.

When you do a select into you'll get an error if the table already exists. So, you can add the line

drop table mytable

somewhere in your code. If you need to, you can also add the if exists line to see if the table exists before dropping it.

I don't know of any way to use select into to create a temp table. This is what I use.

Another thing you might try doing is taking the selection that you create and saving it locally as an xml table. You can then open a recordset on that table, secure in the knowledge that you aren't really accessing the full set of records in the database.

And, here's one more trick.

rs.properties("Optimized") = true will set up a temporary index on your recordset. (That one is tough to find, but it's been a godsend for me.) I've found that I get up to 10 times the performance when I do this. Some cursortypes won't support this; disconnected recordsets (cursorlocation = aduseclient, locktype = adlockbatchoptimistic) will.

 

[TallOne]

Hello BobRodes,
I was excited about trying out the "Optimized" property but couldn't get it to work. I'm using ActiveX Data Objects 2.8 referenced in my project and code like this.

    ' Create an instance of the Recordset.
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.LockType = adLockBatchOptimistic
    rs.Properties("Optimized") = True

And get runtime error 3265 "Item cannot be found in the collection..." In my Locals Window I could see the properties of the rs, and of 103 items, none had the name of "Optimized" Got any ideas what's going on? TIA

 

[BobRodes]

Sorry, my mistake. I looked up my code. It's "Optimize", and it's on the field object of course, not the recordset object.

So, for example:

rs.fields(0).properties("Optimize") = true

will put an index on the first field.

Bob

 

[TallOne]

Hello BobRodes,
I did a little searching and found some examples listed below of the Optimize property. Thanks for the tip and have a star!

http://msdn.microsoft.com/library/d...270/htm/mdmscadocodeexamplesinvisualbasic.asp

http://www.devx.com/vb2themax/Door/18897?cat=600&type=1

 

[bubarooni]

The drop table did fix that problem but, I can't seem to dig up the info I need to successfully implement the 'if exists' statement to check for the table. Can you provide an example for checking for the existence of a table in a .mdb.

It is working but, if I manually delete that table from the db and then run the project it throws an error at me when the code tries to drop the non-existent table. Would probably never happen in our environment, but...

 

[TallOne]

Hi Bubarooni,
This thread discusses a couple of different ways with pros and cons. Hope this helps!
Later

thread222-902271

 

[BobRodes]

I was talking about SQL Server, sorry. In case someone is looking for SQL server help on this, this comes straight from a generated SQL script in Enterprise Manager and drops the Categories table from the Northwind db if it exists.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Categories]
GO

Bob