Access Database

RonR
10-23-2004, 04:20 PM
selectedjob is a string variable that contains the jobnumber i want. there can be 1 or 50 records with the particular jobnumber i want.

this code works but it is slow(with 4000 records), is there a faster way to find all the records with the particular jobnumber and load the id into the listbox??

once i know the material id number i can use the "find" and display it instantly.


For MMM = 1 To Material.RecordCount
Material.AbsolutePosition = MMM

If Trim$(Material("jobnumber").Value & "") = SelectedJob Then
List2.AddItem Material("id").Value & ""
End If

Next

VBJoe
10-23-2004, 05:07 PM
Use a query that only returns the records with the job number you need:

Dim sql As String, rs As ADODB.Recordset
Set rs = New ADODB.Recordset
sql = "SELECT * FROM Material WHERE JobNumber = " & SelectedJob
rs.Open sql, YourConnectionObject, adOpenStatic, adLockReadOnly
List2.Clear
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
List2.AddItem rs.Fields("id").Value
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing

RonR
10-23-2004, 05:23 PM
ok. i will give it a try

thanks!

RonR
10-23-2004, 05:36 PM
this is the code i use now to open my db.


CMD.CommandText = "SELECT * FROM Material ORDER BY id"

i changed it to this and it does not load, i get errors:

cmd.commandtext = "SELECT * FROM Material WHERE JobNumber = " & Selectedjob


any ideas?

VBJoe
10-23-2004, 05:50 PM
Are you using a Connection object? Here's how I might create a Connection object using the code I posted previously (changes are in bold):

Dim sql As String, rs As ADODB.Recordset, cn As ADODB.Connection
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data.mdb"
cn.Open
sql = "SELECT * FROM Material WHERE JobNumber = " & SelectedJob
rs.Open sql, cn, adOpenStatic, adLockReadOnly
List2.Clear
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
List2.AddItem rs.Fields("id").Value
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

If you already have a Connection object, use that. If you're using the ADO Data Control, I'd suggest going to straight code for your database work.

BTW, what errors are you getting when you change the query text?

RonR
10-23-2004, 05:55 PM
I am running out of time tonight to try what you suggest.

I can close my db quick, but i would have to load the db with your code, load the listbox then close the db again and reopen with my original code which would take longer than the code i already have.


It takes about 1.5 seconds to open the db with my code when it has 4000 records.

RonR
10-23-2004, 06:20 PM
i changed it to:

do until material.eof

do stuff here

loop

it is fast!!! now, i did not notice to stop using my for-next loop

THANKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum