Type Mismatch Error and I don't know why...

I'm working on a program that involves a database connection, but I ran into a problem...I get a type mismatch error when opening a recordset. I've shown this to colleagues, and they are all confused too...so I figured I'd stick it up here where the world can see and provide some input :-)



Dim SQL As String
Dim DatabaseName As String

Private Sub btnGo_Click()
Dim db As Database
Dim rs As Recordset
DatabaseName = "customer.dat"
SQL = "SELECT * from tblCustomerReminders WHERE fldNextReminderDate < #" & CDate(txtDateBox.Text) & "#;"

Set db = OpenDatabase(App.Path & "\" & DatabaseName, False, False)
Set rs = db.OpenRecordset(SQL, dbOpenDynaset)
...



At this last line, I get a Type Mismatch Error. I have verified that the database and table both exist, and I have run the SQL query in MS Access to verify it. Interestingly, If I change the line "Set rs = ..." to

Set rs = db.OpenRecordset(tblCustomerReminders, dbOpenDynaset)

I still get the same error, even though I avoid using the SQL. I have no idea what could be causing this.... any suggestions?

Have you added a reference to DAO in your project?

Yes, I have a reference to "Microsoft DAO 3.51 Object Library".

Why your db filename is "customer.dat"??Is it not "customer.mdb"??Plz change the file extended name with mdb,If it is a Ms access db file..

The file name has a .dat file extension as an added security function. The file customer.dat is part of a preexisting program. While the data files used by the preexisting program are MS Access files, the group who developed this program named them .dat files so that users would be less likely to double click them and have Access open them - basically, it makes it less likely for a user to fool around with the data. Also, I have written several programs that use data access, and I almost always save the files as .dat. VB doesn't care about the file extension, only the contents of the file. So, basically, cleverpig, I can't change the file extension, or the program that currently uses it wouldn't be able to find it.

On a side note, naming your data files .dat instead of .mdb is actually a very good practice - users shouldn't need to know anything else about this file other than that it stores data used by the program. Giving it a file extension that is linked to a program only gives the user one more way to corrupt the file. Any changes you want made to this data should be done thru the program itself, so there should never be a need for a user to open the file in Access. However, should the need arise to access this file in Access, suppose the user requested a feature that requires you to write a special query, you can always access it by opening access and then opening the file...so it really can benefit you by NOT naming the files .mdb

OK,MAcrado!I see!My meaning is u can try to change the dat expanded filename to mdb expaned filename,so that u can find the wrong whether is caused by expaned filename..