Error if variables are declared

I am using code to log on to an application using an internal Database and Recordset.

The variables are declared as follows:

Dim DB As Database
Dim RS As Recordset
Dim stLoginName As String
Dim stPassword As String

The code I use is as follows:

Set DB = OpenDatabase(stDatabaseName, False, False)
Set RS = DB.OpenRecordset("Select * From [PROGRAM SECURITY] where Login = '" & stLoginName & "'")

Login is a text type in an Access database
stLoginName is the contents of a text box on my login screen

On the 2nd line of code, I get run-time error 13 Type Mismatch

If I do not use an Option Explicit and do not declare the variables, the code works fine.

As soon as I add an Option Explicit and declare the variables, the run time error occurs.

I use this same code in all my other programs I have created and have never run into this before. Can anyone help me please. It is driving me nuts.... :-)

Have you decalred stDatabaseName as string?

Yes, it is declared as a string in the .bas file. It is working correctly and contains the name of the database being accessed.

just curious.. why do you have the "'" at the end of that statement?

Instead of declaring sDatabaseName in a module, try declaring it in your code.

The strDatabaseName has to be declared globally. It is used by a function in the .bas file to get the path to the database.

I use this same code all the time and it works fine until today.

The error occurs when the variables are declared.

The error occuring when the vars are declared is indicitive of something wrong with your code. If they aren't declared, then they are of type variant which is a catch all-do everything var type..

The type mismatch error also looks to be caused by something other than the obvious. I'd really start looking outside of these things specifically.. just because it works when you don't declare the vars doesn't mean that there isn't something wrong someplace else..

I know you say the error occurs in the 2nd line of code, but I also notice that in one of your posts you say the path to the database is strDatabaseName, but in your code sample you use stDatabaseName. Should it be str...?

By the way a suggestion when naming your variables is to append them with m_ when they are defined in a form module and g_ when they are defined globally in a code module. You may well be aware of this already, but I find that helps me to understand the code when I come back to it a year later. Also you should always Option Explicit.

The error I was having is solved.

The recordset had to be declared as a DAO recordset IE:

Dim RS as DAO.Recordset

I have used this code in all other program I have ever created and this is the first time I had to declare the recordset in this manner. We are using VB 6.0.

I wonder if someone could tell me why VB would suddenly require me to change the way the recorset is declared.

Thank you to all of you who posted a reply.

Simply put it is MS's way of making everyone atleast look at ADO. Incidentally the same is true in VBA6 (Access2k) where anyone looking at using ADO within a JET database should be shot.