Open Record Set Problems

[BenSteckler]

Hi Everyone,

I have a problem.

I am trying to open 2 recordsets. But when I run the code, it displays a message "Type Mismatch." Can someone look at my code and tell me what I am doing wrong?

    Dim dbs As Database
    Dim rs As Recordset, rsa As Recordset, sql As String, sqlA As String
    
    Set dbs = CurrentDb
    Set rs = dbs.openrecordset("Table1", dbOpenDynaset)
    Set rsa = dbs.openrecordset("Table2", dbOpenDynaset)

Thanks
BDS

 

[79ssecca]

Whats here looks fine post more of the code. Are you truly pulling from tables or is it possible you are actually puling from a query?

79ssecca

 

[BenSteckler]

Thanks for the reply,

It is an acutal Table in the same database. I Tried to create a SQL statement and use that instead, but I still got the same message.

Any ideas?
BDS

 

[WayneRyan]

Ben,

Your code looks fine. The only error that I could see is if you
got "user defined type ..." due to a missing DAO reference.

Can you tell us what line the error is on?

Wayne

 

[79ssecca]

Two questions...

Does the SQL statement run as a query ok?


Is it possible that one of the variable names is being used somewhere else as a different daata type?

Post the entire code and indicate what line is causing the error.

 

[BenSteckler]

Here is a copy of the whole code:

Public Sub BuildCorrectTable()
    Dim dbs As Database
    Dim rs As Recordset, rsa As Recordset, sql As String, sqlA As String
    
    Set dbs = CurrentDb
    Set rs = dbs.openrecordset("All Products", dbOpenDynaset) '<---Error Occurs Here
    Set rsa = dbs.openrecordset("tbl: Cube Data", dbOpenDynaset)
    
    rs.MoveFirst
    For i = 1 To rs.RecordCount
        rsa.AddNew
        rsa!SKUNumber = rs!ShortProdSKU
        rsa.Update
        rs.MoveNext
    Next i
    
    rs.Close
    rsa.Close
    
    Set rs = Nothing
    Set rsa = Nothing
    Set dbs = Nothing
End Sub

When I attempted to use the SQL statement, it returned the same error.

I normally use VB 6 for my programming, so maybe I have my references wrong.

I am using Microsoft DAO 3.51 Object library. Is that correct?

Thanks
BDS

 

[79ssecca]

I just cut and pasted your code into a test DB renamed two of the tables to your names changed the names of a couple of fields in the tables and the code run with out a hitch.

The reference is correct it is the only optional one I have selected as well.

I am at a real loss. For the record however I am using Access 97

Good Luck

 

[BenSteckler]

I figured out where the problem was.

I looked at my References and Microsoft DAO 3.51 Object library was the list reference on my list.

A co wprker of mine told me to move the DAO reference to the top of the list, or as high up on the list as it will allow.

This solved the problem.

My co worker who told me that this is an Access 2000 problem. And that when they converted from 97 to 2000, they had to do this to all their databases.

Thank you to everyone for your help.
BDS

 

[The_Doc_Man]

Absolutely correct, order of reference appearance is quite significant.