DAO in ms access 2010

Hi:
Dose the DAO still work in ms access 2010?
please help
i have type mismatch
DAO1.PNG
DAO2.PNG
Mohammad Alsolaimanapplication programmerAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
Try explcitly declaring your DAO object types:
Dim db As DAO.Database
Dim rs As DAO.Recordset

Open in new window

0
 
Patrick MatthewsCommented:
And, of course, make sure you do not have any broken references!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
In fact, be sure you have a Reference Set to Microsoft DAO ...

mx
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Actually, is there more code than shown?  Because you are not using the recordset rs ... ?

mx
0
 
Patrick MatthewsCommented:
LOL
0
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
1- i try to add a reference but no way, i have an err. see the image
2- i Try explcitly declaring, and it works good for that particular subroutine only
DAO3.PNG
DAO4.PNG
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
odd.  What references are further up the list?  Screen shot ....

mx
0
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
see this image please
references.PNG
0
 
rockiroadsConnect With a Mentor Commented:
With 2010 I dont think there is a need to add the reference. Should be the same as 2007.

All you need to do is what Patrick said, explicitly declare DAO type.

You said it worked in that subroutine only, thats because that is probably what you changed only

You would have to change the other places in code

you could try a global search/replace

as database

to

as dao.database

etc


Then do a debug/compile

Hopefully your not using ado.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Yeah .. that's right, you don't need that Reference.
The Microsoft Office 14.0 Access database Engine Object .... is the one, so that's not the issue.

Again, is there more code in this sub than shown ?  If not, where are you creating a recordset?  I don't see it being used ...

mx
0
 
Mohammad Alsolaimanapplication programmerAuthor Commented:

Private Sub cmdOK_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
'   Dim db As Database
'   Dim rs As Recordset
   Set db = CurrentDb()
   Set rs = db.OpenRecordset("UsersAccountsTbl")
   If IsNull(Me.Department) Then
     MsgBox "ÝÖáÇ ÃÏÎá ÇáÅÏÇÑÉ "
     Me.Department.SetFocus
     Exit Sub
   End If
   If IsNull(Me.UserName) Then
     MsgBox "ÝÖáÇ ... ÃÏÎá ÇÓã ÇáãÓÊÎÏã!"
     Me.UserName.SetFocus
     Exit Sub
  End If
   Do While Not rs.EOF
   If rs!UserName = Me.UserName.Value And rs!Department = Me.Department.Value Then
      If rs!PassWord = Me.PassWord Then
         Form.Visible = False
         strDepartment = Me.Department
         strUserName = Me.UserName
         If rs!UserType = "ADMIN" Then
            strAdmin = "True"
         Else
            strAdmin = "False"
         End If
      DoCmd.OpenForm ("SwetchBord")
      DoCmd.Close acForm, "frmSplash"
      Exit Sub
      End If
   End If
   rs.MoveNext
   Loop
   MsgBox "ÚÝæÇ ... ÇÓã ÇáãÓÊÎÏã Ãæ ÇáÑÞã ÇáÓÑí ÎØÃ", vbExclamation
   Me.PassWord.SetFocus

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Is "UsersAccountsTbl" a table, or possible a query ?

mx
0
 
rockiroadsCommented:
Are you looking for just the one record? In this case you just need one call and can avoid recordsets by using the DLOOKUP function
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Is UsersAccountsTbl  possibly the name of a Form or Report ?
Odd you would get a Type Mismatch on that statement ....

mx
0
 
rockiroadsCommented:
Check all your code for use of dao and change like Patrick says. For this problem, Patrick's suggestion would solve it.

but additional info for you

In your attached code, good practice is to
1. indent properly
2. close your recordsets - you are opening but you do not close them

from what I said in my last post if just looking for one record you can use functions like dlookup

assumiung the username, department and password are string fields you can rewrite it like this
Private Sub cmdOK_Click()

    Dim sWhere As String
    Dim found
   
    If IsNull(Me.Department) Then
        MsgBox "ÝÖáÇ ÃÏÎá ÇáÅÏÇÑÉ "
        Me.Department.SetFocus
        Exit Sub
    End If
    
    If IsNull(Me.UserName) Then
        MsgBox "ÝÖáÇ ... ÃÏÎá ÇÓã ÇáãÓÊÎÏã!"
        Me.UserName.SetFocus
        Exit Sub
    End If
    
    sWhere = "Username = '" & Me.UserName & "' AND Department = '" & Me.Department & "' AND Password = '" & Me.password & "'"
    found = DLookup("Username", "UsersAccountsTbl", sWhere)
    If Not IsNull(found) Then
        Form.Visible = False
        strDepartment = Me.Department
        strUserName = Me.UserName
        If rs!UserType = "ADMIN" Then
            strAdmin = "True"
        Else
            strAdmin = "False"
        End If
        DoCmd.OpenForm ("SwetchBord")
        DoCmd.Close acForm, "frmSplash"
    Else
        MsgBox "ÚÝæÇ ... ÇÓã ÇáãÓÊÎÏã Ãæ ÇáÑÞã ÇáÓÑí ÎØÃ", vbExclamation
        Me.password.SetFocus
    End If

End Sub

Open in new window

0
 
rockiroadsCommented:
Right remembering where I am, and remembering it is 2010/2007, then references are not required and dao is by default

so to fix the problem at hand follow what mx says (my bad from before)

but the fyi added using dlookup and closing recordsets (not for points, just fyi only) still stands

0
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
DatabaseMX:
"Is UsersAccountsTbl  possibly the name of a Form or Report ?"
No
i did what rockiroads said  
"With 2010 I dont think there is a need to add the reference. Should be the same as 2007.
All you need to do is what Patrick said, explicitly declare DAO type.
You said it worked in that subroutine only, thats because that is probably what you changed only
You would have to change the other places in code
you could try a global search/replace as database to as dao.database etc
Then do a debug/compile
Hopefully your not using ado."
it works very good
0
 
rockiroadsCommented:
Actually just saw your last image. You are using ADO as you have added it as a reference.

So change code back to DAO.Database and DAO.Recordset

and that should be fine.

If you are using both ADO and DAO you will have to go thru and check the code to make sure you use the right types.

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Of course, we would all prefix with DAO ... not sure that's the problem here because there is no ADO reference, so Access *should* ... not be getting confused ...

mx
0
 
rockiroadsCommented:
Credit to patrick, not me for stating DAO. I am just giving you info
0
 
rockiroadsCommented:
If you are not using ADO at all then remove that reference
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I see there IS an ADO reference ... sorry.

mx
0
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
full of good advices
0
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
my first app is working now properly in ms access 2010
very glad
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
So, what actually fixed it?

mx
0
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
matthewspatrick:
Try explcitly declaring your DAO object types:

1:Dim db As DAO.Database
2:Dim rs As DAO.Recordset

but i learn plenty of good information
like "With 2010 I dont think there is a need to add the reference. Should be the same as 2007"
and "he Microsoft Office 14.0 Access database Engine Object .... is the one"
and "Are you looking for just the one record? In this case you just need one call and can avoid recordsets by using the DLOOKUP function"
"found = DLookup("Username", "UsersAccountsTbl", sWhere)"
and "In your attached code, good practice is to
1. indent properly
2. close your recordsets - you are opening but you do not close them"
and "closing recordsets "
and "If you are not using ADO at all then remove that reference"










0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Lets go one more step w/o Dim anything:

Private Sub cmdOK_Click()

   With CurrentDb.OpenRecordset("UsersAccountsTbl")
     
     If IsNull(Me.Department) Then
       MsgBox "ÝÖáÇ ÃÏÎá ÇáÅÏÇÑÉ "
       Me.Department.SetFocus
       Exit Sub
     End If
     
     If IsNull(Me.UserName) Then
       MsgBox "ÝÖáÇ ... ÃÏÎá ÇÓã ÇáãÓÊÎÏã!"
       Me.UserName.SetFocus
       Exit Sub
     End If
     
     Do While Not .EOF
     
     If ![UserName] = Me.UserName.Value And ![Department] = Me.Department.Value Then
        If ![Password] = Me.Password Then
           Form.Visible = False
           strDepartment = Me.Department
           strUserName = Me.UserName
           If ![UserType] = "ADMIN" Then
              strAdmin = "True"
           Else
              strAdmin = "False"
           End If
        DoCmd.OpenForm ("SwetchBord")
        DoCmd.Close acForm, "frmSplash"
        Exit Sub
        End If
     End If
       .MoveNext
     Loop
   End With
   
   MsgBox "ÚÝæÇ ... ÇÓã ÇáãÓÊÎÏã Ãæ ÇáÑÞã ÇáÓÑí ÎØÃ", vbExclamation
   Me.Password.SetFocus
 
End Sub


mx
0
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
very good and short , i like it
0
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
dose the End With will close clean the memory from the object? or what?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Also, in this case ... it would have avoided the issue altogether ... although, it would no doubt have occurred elsewhere.  Uncheck the ADO reference is a *good* move.  In fact, you probably do not need OLE Automation reference either.  I always uncheck that.

mx
0
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
good night, sorry i am too late, i have only 3 hours to sleep
it was good night for me
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"End With will close clean the memory from the object? or what?"
Well ... basically yes.  And technically, everything goes with the End Sub (or Function) occurs.  But, still good practice to explicitly close and/or Set to Nothing ... but with the With / End With ...  the End With closes the recordset opened.  Even if the Exit Sub occurs ... it's still ok.

mx
0
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
very nice
0
 
rockiroadsCommented:
Right, Im back. Had to pop out after my last post. I see you got on more.

Remember to try make use of the functions if you just want one off data like you do in this case. Will make life a little easier for you eg DLOOKUP, DMAX etc
0
 
datAdrenalineCommented:
FYI ...

It is important to realize in all this that ADO and DAO both have objects named "Recordset", and with ADO higher up in the Reference list, the ADO Recordset object gets the priority.  So since the Dim statement did NOT explicitly declare the library the "Recordset" object was to come from, the ADO object model was used to provide the type for the rs object variable, thus the error on the line indicated since .OpenRecordset() returns a DAO recordset object.  The proper solution for this is to explicity declare the library you want to use, as matthewspatrick said from the beginning --- you will have to explictly declare your DAO.Recordsets in your ENTIRE VBA Project if you have both ADO and DAO referenced.

I will often have both the ADO and DAO (note that with the arrival of A2007 the Access Database engine Objects library is the DAO object library name) referenced in my db apps.  When I do so, I will place the DAO object library HIGHER on the list in order to give that object model priority when compiling, however, I will ALWAYS explicitly reference the library I want the object type to come from ...

Dim rs As DAO.Recordset
... Or ...
Dim rs As ADODB.Recordset

-----

Did that make sense?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"Did that make sense?"
Yes, but ... I think PM pointed out in the first post :-)
However, you did amp it up a bit ... and I certainly agree.

mx
0
 
datAdrenalineCommented:
Yes ... I mentioned that in my text ... I was just trying to add the "Why?" behind it ... actually the trigger to my reply was the "So, what actually fixed it?" from you :) ...

>> However, you did amp it up a bit  <<

Must have been the Frappe I got from McD's ... I can never sleep when I get one of those! ...
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Caffeine is the Staff Of Life !

0
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
thanks very much for the valuable inf
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.