1 ADO Connection + 2 ADO Recordsets = 1 Big Problem 1

[zenenigma]

I have two tables within my database which I need to query within VB. The 1st recordset (RS) will be queried and updated with information from my text import. The 2nd recordset (RSFound) is a lookup query (the SQL statement looks for a variable that was set from the 1st recordset) that will try to match the current record from 1st recordset to the current (only) record from the 2nd recordset. I have one ADO Connection to the database. For some reason the second recordset is not populating. It worked fine in DAO, so I know the SQL statement is correct.

    dbpath = App.Path & "\pplog3.mdb"
    Dim Conn As ADODB.Connection  'Connection
    Dim Rs As ADODB.Recordset     'Recordset
    Dim strQuery As String        'Command

Set Conn = New ADODB.Connection
  With Conn
  .CursorLocation = adUseClient
  .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=" & dbpath
  .Open
  End With

strQuery = "SELECT * from ImportTable"

Set Rs = New ADODB.Recordset
    With Rs
    Set .ActiveConnection = Conn
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .Source = strQuery
        .LockType = adLockOptimistic
        .Open
    End With

<Code here to import files and parse them into the &quot;tempdesc&quot; and a date variable for matching later>

    Dim RsFound As ADODB.Recordset     'Recordset
    Dim strQuery2 As String        'Command

    strQuery2 = &quot;SELECT Value, Desc, Type, Group FROM Found WHERE Desc = '&quot; & tempdesc & &quot;'&quot;

Set RsFound = New ADODB.Recordset
    With RsFound
    Set .ActiveConnection = Conn
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .Source = strQuery2
        .LockType = adLockReadOnly
        .Open
    
    End With
    
Dim dbdesc As String

    dbdesc = RsFound!Desc

It's at this point where RsFound!Desc gives me the error: &quot;<Method 'Item' of object 'Fields' failed>&quot;. There is a field called &quot;Desc&quot; in the &quot;Found&quot; table. The SQL query brings back one record (when run manually). And there have been matches (not that it even got to the point of matching).

The first recordset is kept open because the records aren't added until after this lookup is done.

This code worked fine in DAO, but I wanted to get everything in ADO so that there would be less to package at deployment time.

Any ideas would be appreciated.

 

[CCLINT]

Enclose your table field names in brackets [].

&quot;Desc&quot; &quot;Value&quot; and &quot;Group&quot; are reserved words.

strQuery2 = &quot;SELECT [Value], [Desc], [Type], [Group] FROM [Found] WHERE [Desc] = '&quot; & tempdesc & &quot;'&quot;

dbdesc = RsFound.Fields(&quot;[Desc]&quot;}.Value

 

[zenenigma]

CClint, that seemed to help on some of my other forms, but not on this particular form.

I changed all the names in my table so they wouldn't be confused as reserved words

I also tried putting the second recordset call in a function and having the code on my form call that function (so that there would not be two recordsets in one procedure)

My new Function:

Dim tempdesc As String
'set variable equal to current import variable
tempdesc = currentimportdesc

            tempdesc = Left(tempdesc, Len(tempdesc) - 1)
    Dim temptempvalue As Integer
    temptempvalue = 0

    Dim Conn As ADODB.Connection  'Connection
    Dim Rs As ADODB.Recordset     'Recordset
    Dim strQuery As String        'Command
    Dim dbdesc As String
'Set, describe, and open the connection to the database:

Set Conn = New ADODB.Connection
  With Conn
  .CursorLocation = adUseClient
  .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=" & dbpath
  .Open
  End With
   
'Set and define your Command:

    strQuery = "SELECT [Value], [Desc], [Type], [Group] FROM [Found] WHERE [Desc] = '" & tempdesc & "'"

    ' Open the Recordset

Set Rs = New ADODB.Recordset
    With Rs
    Set .ActiveConnection = Conn
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .Source = strQuery
        .LockType = adLockReadOnly
        .Open
    
    End With
    
   dbdesc = Rs.Fields("[strdesc]").Value

::: more code below:::

Before I moved this code to a function I would get the rror on the &quot;dbdesc = Rs.Fields(&quot;[strdesc&quot.Value&quot;.

Now that it's in the function, the function ends as soon as the &quot;.Open&quot; of the recordset is run.

I've tried renaming the connection, recordset, sqlquery, etc. with no luck. I'm not really getting an error message, it just ends the function and brings me back out to procedure (as if there were something wrong with the SQL statement).

Any further help would be greatly appreciated.

 

[zenenigma]

Err stupid me had the wrong SqlQuery references.

strQuery = &quot;SELECT [Value], [Desc], [Type], [Group] FROM [Found] WHERE [Desc] = '&quot; & tempdesc & &quot;'&quot;

Should be:
strQuery = &quot;SELECT [intvalue], [strdesc], [strtype], [strgroup] FROM [Found] WHERE [strdesc] = '&quot; & tempdesc & &quot;'&quot;

But even when I fix that, I have another problem:
dbdesc = Rs.Fields(&quot;[strdesc]&quot.Value

when I hover over Rs.Fields(&quot;[strdesc]&quot.Value it shows:

<Item cannot be found in the collection corresponding....> (tooltip ends)

 

[zenenigma]

Well I was able to fix the Rs.Fields(&quot;[strdesc]&quot issue by changing it back to Rs!strdesc.

Who knew!

Now I've moved onto another form and found yet another problem. With CClints advice I've changed all my field names to non-SQL92 names. However, I'm still getting :

&quot;Method 'Open' of object '_Recordset' failed&quot;

when I use the following code:

    dbpath = App.Path & "\pplog3.mdb"
    Dim Conn As ADODB.Connection  'Connection
    Dim Rst As ADODB.Recordset     'Recordset
    Dim strQuery As String        'Command

Set Conn = New ADODB.Connection
  With Conn
  .CursorLocation = adUseClient
  .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=" & dbpath
  .Open
  End With
    
strQuery = "SELECT strdesc, strgroup, strtype, intvalue " & _
"From Found " & _
"GROUP BY strdesc, strgroup, strtype, intvalue;"
    
Set Rst = New ADODB.Recordset
    With Rst
    Set .ActiveConnection = Conn
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .Source = strQuery
        .LockType = adLockReadOnly
        .Open
        
    End With

It happens on the &quot;.Open&quot; of the recordset. I've tried all sorts of combinations for the SqlString. Using brackets around the field names, using [found].[fieldname], no brackets, using table name (found.tablename) but I've had no luck whatsoever. I've even tried taking the semi-colon out.

All of my searches for this error code seem to point to the Sql92 Names, however I've been through the Sql92 list and I don't have any names in my SQL Statement.

This is the last form I'm converting from DAO to ADO and I'm so close to converting this entire project. Any help would be greatly appreciated.

 

[CCLINT]

Start with something simple and work your way through it:

strQuery = &quot;SELECT strdesc From Found&quot;

strQuery = &quot;SELECT strdesc, strgroup, strtype, intvalue From Found&quot;

strQuery = &quot;SELECT strdesc From Found GROUP BY strdesc&quot;

etc.

 

[zenenigma]

CClint if I could give you two stars, I would. The problem is that &quot;found&quot; is a reserved word and it's the name of my TABLE! Oh, the humanity.

Everything's running fine. I suggest the brackets for anyone who has this problem.

Now, on to find out how I can compact my database through VB code if the user might not have Access on their PC (and the fact that I'm programming with Access 97, not 2000)

 

[TheVampire]

Even if you can't give him two stars, give him one at least!

Robert

 

[zenenigma]

Bah, I thought I clicked it before. Well I clicked it again, so hopefully it'll show up now.

 

[CCLINT]

Thank you both!

Use JRO to compact (reference to &quot;Microsoft JEt and Repl. Object library&quot