Log Analyzer w/ hits to Access

[zenenigma]

I'm building a log analyzer and the heavy lifting is done. Visual Basic imports the files, only keeps the records I want, and puts them into Access in multiple fields.

So instead of one string:

Ford Mustang GT Convertible Red

It chops it up into fields and puts it in the "log table", though I have more blank fields in my "log table" that need to be populated. So I have one connection to my Access Database to be able to add records to the "log table". While processing these records, I want VB to look at another Access "lookup table" (within this same database) to determine the price of this car. I currently have the "GT" field in a variable ("varEngine&quot, but I want to lookup in "tblPrice"(lookup table) where "tblPrice.Engine" = "varEngine". And when it finds the price, I want it to put the price into variable "varPrice" (to be used in the update).

My current connection for the database:

---------------------------

Dim strconnect As String
Dim strprovider As String
Dim strdatasource As String
Dim strdatabasename As String
strprovider = "Provider= Microsoft.Jet.OLEDB.4.0;"
strdatasource = App.Path
strdatabasename = "\pplog3.mdb;"
strdatasource = "Data Source=" & strdatasource & strdatabasename
strconnect = strprovider & strdatasource
Set ConnConnection = New ADODB.Connection
    ConnConnection.CursorLocation = adUseClient
    ConnConnection.Open strconnect
    Set rsrecordset = New ADODB.Recordset
    rsrecordset.CursorType = adOpenStatic
    rsrecordset.CursorLocation = adUseClient
    rsrecordset.LockType = adLockPessimistic
    rsrecordset.Source = "Select * From ImportTable"
    rsrecordset.ActiveConnection = ConnConnection

rsrecordset.ActiveConnection = ConnConnection
rsrecordset.Open

--------------------------
And then I just use the addnew and update functions of the recordset to populate the Access table.


So how would I go about doing the lookup? Could I just create another recordset off of this same Connection (it's the same database). I tried creating another recordset (and connection) and the process just hangs, so any help would be appreciated.

 

[zenenigma]

After alot of toying around and searching for the forums I decided on a ghetto method of using DAO to pull from the second lookup table. It works. If anyone has any better suggestions they would be appreciated.