Simple openrecordset Question

[Paul H]

I haven't had a chance to use any DAO for several months. This seems quite simple, but I'm stuck.

Here's what I have and I can't get past this.

       Dim rst1 As Recordset, rst2 As Recordset
    
    Set rst1 = CurrentDb.OpenRecordset("SELECT * FROM tblStatementArchive WHERE Vendor_ID Is Null")
    Set rst2 = CurrentDb.OpenRecordset("qryStatement1_Archive")

On rst1 I get this error 13 - Type Mismatch

On rst2 I get this error 3061 - Too few parameters Expecting 2
There are no parameters in this query. There's not even a WHERE clause.

Both queries run normally. I'm stuck. Hope you can help.

[ItsMe]

For the first, maybe
Never mind. Was not paying attentionearlier. Not sure why you would get a data type error as long as vendorID is a field name in the table.

For the second, I do not know. I just started a thread of my own because I am getting the same error but, I am using a parameterized query.

[Missinglinq]

What happens if you use

Dim rst1 As DAO.Recordset, rst2 As DAO.Recordset

Linq ;0)>

[Paul H]

Dear Linq,

I tried that after reading a few online forums, but it did not recognize DAO.Recordset. I dunno, maybe I spelled it wrong. I'll get back to you Monday from work.

I see you are a fellow Richmonder. Greetings.

[June7]

Works for me with or without the DAO qualifier.

Dynamic filter parameter will cause the 'too few parameters' error. See ItsMe's thread https://www.accessforums.net/program...dao-42878.html
I am guessing you have 2 dynamic parameters in the query object.

[ItsMe]

I wish I knew what the difference between using the qualifier and not using the qualifier is. I know, in some circumstances, the only way I have been able to get my code to work was to use DAO.Recordset vs. Recordset. Could never distinguish what was different between the two approaches.

[Xipooo]

You can also get the "Too Few Parameters" error for misspelling something in your query. Since it can't find your misspelled word, it assumes then that it's supposed to be a parameter. But since you didn't set that parameters value it is considered missing.

[Xipooo]

I haven't had a chance to use any DAO for several months. This seems quite simple, but I'm stuck.

Here's what I have and I can't get past this.

       Dim rst1 As Recordset, rst2 As Recordset
    
    Set rst1 = CurrentDb.OpenRecordset("SELECT * FROM tblStatementArchive WHERE Vendor_ID Is Null")
    Set rst2 = CurrentDb.OpenRecordset("qryStatement1_Archive")

On rst1 I get this error 13 - Type Mismatch

On rst2 I get this error 3061 - Too few parameters Expecting 2
There are no parameters in this query. There's not even a WHERE clause.

Both queries run normally. I'm stuck. Hope you can help.

For your first recordset, are you getting data from an Access database or another database server like MySQL, or MS SQL? You need to be careful about some of the data types in other RDBMS's. For example, Access can't see Microsoft SQL's BigInt, and Date fields will show up as TEXT fields instead of date/time.

As for your second recordset, I'm willing to bet you've misspelled something in your query like I mentioned in my earlier post.

[Paul H]

Thanks for all your replies. Here's mine.

Using DAO.Recordset gives me "User defined type not defined" which I just discovered meant I did not have the DAO library referenced. This database started life in a very old version of Access. I checked it and no longer got the error.

I'm digging into part 2 of this. I'm thinking it will be an equally simple fix.

[Paul H]

OK, I do have two parameters in my query. All they do is populate two fields. I tried the Eval() function to no avail.
My original was sans Eval(). My 2nd attempt was without the tick marks. Oh yes, tried with and without declaring my parameters. All the same. Finally I dropped snapshot into my function and poof, no more problem. I don't even know what snapshot is. Very frustrated to have gone to so much trouble.

When I'm cooled down I'll read the link in the thread you fed me June or someone can explain what snapshot is and why it was necessary.

[ItsMe]

I use the constant dbOpenSnapshot or the integer equivalent 4 to open a snapshot. Basically, you are getting a read only copy of the data retrieved. There are not any record locks implied when using snapshot. However, if data is changed in the table by another user after you open the dataset, you will not see the changes when you move to the record that was edited by the other user. Changes made after you retrieve your snapshot will not be reflected in your snapshot.

If I am not going to do any edits, update, append methods I will open a snapshot to avoid locking records or having access attempt to lock records. I believe default is dbOpenDynaset. You can add stuff to it like dbOpenOptimistic or dbOpenPessimistic.

, dbOpenDynaset + dbOpenPessimistic)

[Xipooo]

Paul, why don't you post the SQL of your stored query here. I think it will make much more sense to us all if we can see it.