Solved

DAO in ms access 2010

Posted on 2010-09-17
39
1,347 Views
Last Modified: 2013-11-28
Hi:
Dose the DAO still work in ms access 2010?
please help
i have type mismatch
DAO1.PNG
DAO2.PNG
0
Comment
Question by:Mohammad Alsolaiman
  • 14
  • 12
  • 8
  • +2
39 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 470 total points
Comment Utility
Try explcitly declaring your DAO object types:
Dim db As DAO.Database

Dim rs As DAO.Recordset

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
And, of course, make sure you do not have any broken references!
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
In fact, be sure you have a Reference Set to Microsoft DAO ...

mx
0
 
LVL 75

Expert Comment

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

mx
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
LOL
0
 

Author Comment

by:Mohammad Alsolaiman
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
odd.  What references are further up the list?  Screen shot ....

mx
0
 

Author Comment

by:Mohammad Alsolaiman
Comment Utility
see this image please
references.PNG
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 30 total points
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
 

Author Comment

by:Mohammad Alsolaiman
Comment Utility

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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Is "UsersAccountsTbl" a table, or possible a query ?

mx
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Is UsersAccountsTbl  possibly the name of a Form or Report ?
Odd you would get a Type Mismatch on that statement ....

mx
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 

Author Comment

by:Mohammad Alsolaiman
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Credit to patrick, not me for stating DAO. I am just giving you info
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
If you are not using ADO at all then remove that reference
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
I see there IS an ADO reference ... sorry.

mx
0
 

Author Comment

by:Mohammad Alsolaiman
Comment Utility
full of good advices
0
 

Author Comment

by:Mohammad Alsolaiman
Comment Utility
my first app is working now properly in ms access 2010
very glad
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
So, what actually fixed it?

mx
0
 

Author Comment

by:Mohammad Alsolaiman
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
 

Author Comment

by:Mohammad Alsolaiman
Comment Utility
very good and short , i like it
0
 

Author Comment

by:Mohammad Alsolaiman
Comment Utility
dose the End With will close clean the memory from the object? or what?
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
 

Author Comment

by:Mohammad Alsolaiman
Comment Utility
good night, sorry i am too late, i have only 3 hours to sleep
it was good night for me
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"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
 

Author Comment

by:Mohammad Alsolaiman
Comment Utility
very nice
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 
LVL 11

Expert Comment

by:datAdrenaline
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"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
 
LVL 11

Expert Comment

by:datAdrenaline
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Caffeine is the Staff Of Life !

0
 

Author Closing Comment

by:Mohammad Alsolaiman
Comment Utility
thanks very much for the valuable inf
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now