Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

DAO in ms access 2010

Posted on 2010-09-17
39
1,370 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
ID: 33704862
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
ID: 33704866
And, of course, make sure you do not have any broken references!
0
 
LVL 75
ID: 33704872
In fact, be sure you have a Reference Set to Microsoft DAO ...

mx
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 75
ID: 33704895
Actually, is there more code than shown?  Because you are not using the recordset rs ... ?

mx
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33704896
LOL
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 33704975
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
ID: 33705010
odd.  What references are further up the list?  Screen shot ....

mx
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 33705063
see this image please
references.PNG
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 30 total points
ID: 33705084
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
ID: 33705162
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
ID: 33705184

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
ID: 33705212
Is "UsersAccountsTbl" a table, or possible a query ?

mx
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33705218
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
ID: 33705251
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
ID: 33705264
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
ID: 33705287
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
ID: 33705291
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
ID: 33705298
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
ID: 33705299
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
 
LVL 65

Expert Comment

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

Expert Comment

by:rockiroads
ID: 33705311
If you are not using ADO at all then remove that reference
0
 
LVL 75
ID: 33705335
I see there IS an ADO reference ... sorry.

mx
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 33705377
full of good advices
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 33705388
my first app is working now properly in ms access 2010
very glad
0
 
LVL 75
ID: 33705394
So, what actually fixed it?

mx
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 33705457
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
ID: 33705501
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
ID: 33705528
very good and short , i like it
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 33705538
dose the End With will close clean the memory from the object? or what?
0
 
LVL 75
ID: 33705544
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
ID: 33705593
good night, sorry i am too late, i have only 3 hours to sleep
it was good night for me
0
 
LVL 75
ID: 33705606
"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
ID: 33705611
very nice
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33705913
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
ID: 33713794
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
ID: 33713804
"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
ID: 33713982
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
ID: 33714155
Caffeine is the Staff Of Life !

0
 

Author Closing Comment

by:Mohammad Alsolaiman
ID: 33752357
thanks very much for the valuable inf
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

809 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