Errors on Opening Recordset MSAccess

I am using the following code in Access to try to open a recordset:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("fieldHistory")

I consistently get the error "Too few parameters."

fieldHistory is a query with the SQL code as follows:

SELECT Date, User, Type
FROM Inventory
WHERE  ((Inventory.Type) In ("Insert","EditTo"));

I开发者_运维技巧 have looked into this some - I have found that if I use the code db.OpenRecordset("Select * from Inventory") I do not get an error.

Also, I found this forum which seemed to be on to something but I couldn't understand how to create the appropriate querydef objects to create my query.

http://access.mvps.org/access/queries/qry0013.htm

Thank you very much!

Date, User, and Type are all special words or reserved words in MS Access. DO NOT use them for your field names.

You may have some success by enclosing each of them in brackets but I highly recommend you change the field names instead.

Create a new database.

Open the new database and make sure you have DAO included in the references.

Insert a new standard module, making sure to include Option Explicit in the Declarations section.

Then paste in this code and run it.

Public Sub CreateTableZack()
    Dim strSql As String

    strSql = "CREATE TABLE tblZack (" & vbNewLine & _
        vbTab & "id COUNTER CONSTRAINT pkey PRIMARY KEY," & vbNewLine & _
        vbTab & "foo_text TEXT(255)," & vbNewLine & _
        vbTab & "date_assigned DATETIME);"
    Debug.Print strSql

    CurrentProject.Connection.Execute strSql
End Sub

Assuming the table is created successfully, create qryZack with this as its SQL:

SELECT *
FROM tblZack;

Then try your code to open a DAO recordset based on qryZack.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("qryZack")

Does it work?

If not, your Access installation may be hosed ... you may need to repair or reinstall it. You could also try this on another machine which has Access available, if you can.

If it works with the new database, but not your old one, the old one may be corrupted. Make a backup copy first, then try Compact & Repair.

Another issue is your field names. Date, User and Type are all Access reserved words. See Problem names and reserved words in Access. I can't say those names are causing problems here, but using reserved names for database objects (tables, fields, queries, etc.) can have dramatic consequences ... like it confuses the crap out of Access. So I fastidiously avoid them.

Edit: You should also check the references in your old database. Missing/broken references also confuse the crap out of Access.