9. (Advanced Programming In Access 2013) DAO vs ADO

[@AjayKumarparmar]

Steve i love your voice and passion which one can feel in your videos. i also run excel vba access channel . Generally i dont comment but your honesty towards your work made me stop here today. you have my like...

[@funniq]

I agree Ajay .... but you are also doing a great job with your channel. I am following you both....you know ... the IT landscape is changing TMHO. Software developers and suppliers are developing platforms c.q. software in which 'super-users' can develop 'stuff' for end-users. And people like Steve, Wiseowl, you Ajay and many other good people are helping out the many 'super-users' who are not a programmer but are enthousiastic and willing to build 'Office Applications' to make life easier (improve process efficiency) and not expecting for something in return. That is special in a world that almost everything is about money and power et cet ... Thanks guys .... With love from Holland Hugs, André

[@rickstephens1216]

Thanks for the reply Steve. I was referring to the inability in ADO to use the FIND method with multiple criteria whereas it's so easy in DAO. This was what put me off ADO.

[@ProgrammingMadeEZ]

Rick Stephens Ah, yes. With ADO Recordsets... gotcha. Yeah, limited to just one criteria for that method is a real drag.. however you could change the SQL for the recordset and requery. It's just a bit of a pain.

[@richardmoumakwe5890]

Really cool, never understood this up until now. Thanks to you!

[@pw.70]

You can execute stored procedures with DAO using a passthrough query; it's all about how you structure the query and pass the values in.

[@Adnan_Khan24]

Steve you are great. I really love your video.

[@tomservo75]

Thank you for making this! I've been programming in Access for close to 20 years and this has ALWAYS confused me! DAO vs ADO vs ODBC vs OLEDB and all the nuances of each. However, I notice that you say DAO is deprecated, yet it's still the default driver for Access?! That seems weird. And what's the relationship to Jet, I used to hear about that all the time.

[@swapnilwankhede3440]

Steve you are great. Thanks for video

[@johnywhy4679]

17:23 "can't manage master/child forms with ADO, have to use VBA" - Unclear. You have to use VBA to manage master/child forms with DAO too. How is that different?

[@ProgrammingMadeEZ]

Not with bound forms.

[@johnywhy4679]

@@ProgrammingMadeEZ i think you mean the form is using DAO under the hood, correct?

[@tomservo75]

Quick question: I have programmed in Access for a long time and have understood that when I type "Dim rs as Recordset" it's implicitly creating a DAO recordset. Yet this is the first time I've heard of ACEDAO. Is Access 2007+ using ACEDAO behind the scenes and "calling" it DAO?

[@chuckroberts9218]

Thanks for this. I was considering upgrading a very simple db with 2 tables from DAO to ADO, but I'm not sure it's necessary at this point, as there would be a big learning curve with ADO because I haven't done much Access programming in 10 years. Does MS have any plans to totally scrap the DAO objects? DAO is no longer updated but I can still install an older version of Access for my database to use, and set a reference to DAO 3.6.

[@ProgrammingMadeEZ]

No one knows what Microsoft's plans are for DAO and ADO. It's been an ongoing conundrum for them for years. However, if I were to make a guess, I'd say they'd get rid of ADO before they got rid of DAO... and it's also quite possible they introduce ADO.NET since that's their flagship in .NET. DAO is basically the heart and soul of how Access Forms, Reports, and Queries operate. Because of this, I don't think they'll be getting rid of it unless they go to a .NET version.

[@kevinle6388]

Steve, You are soooo goood in explaining in your videos. Thank you! I hope you can help, I am stuck on a customer pole display (USB or Serial Port (RS232C) communication) as I am trying to build a POS database to show the 2 lines total etc as you see in the stores.

[@ProgrammingMadeEZ]

+Kevin Le I'm afraid I don't follow.

[@kevinle6388]

+Programming Thank you for your prompt response, I am trying to build a POS (Points of Sale) database in Access 2013, to ring up the sales as you see in the store. I am having problem of display the total to the Pole (customer display pole) so a customer would know how much to pay. Sorry I was not clear earlier

[@ProgrammingMadeEZ]

Ahh, I gotcha. There must be some sort of interface to the driver of that pole screen. I'm afraid this is a bit beyond the scope of what I could help with on KZbin.

[@johnywhy4679]

26:37 "We converted the DAO to ADO" You mean, ADO talking to the Access database it lives in? Or you mean you offloaded the data to SQL Server?

[@ProgrammingMadeEZ]

The data lives on a SQL Server now while we've replaced the DAO connection to the Access data with an ADO connection to the SQL database.

[@johnywhy4679]

18:25 To use .NET objects in Access VBA, they would have to replace the entire Access VBA system with VB.NET.

[@ProgrammingMadeEZ]

You can make add .NET library references to an Access application.

[@johnywhy4679]

@@ProgrammingMadeEZ So you don't mean using .NET inside of Access VBA, you mean controlling Access with an external .NET application, correct?

[@oscargoyee4159]

Hello, This has broadened my mind and I like to appreciate you so very kindly. I followed the steps and it works very well, thanks again. I have just one problem with my login form. It is not allowing users to input their login credentials. There is an error message which says “Error 3073 in cboUser_Update procedure: Operation must be an updatable query”. I don’t really know what this means as I have checked the entire database. I will appreciate your kind assistance.

[@oscargoyee4159]

This is what I have in my cboUser After_Update: Private Sub cboUser_AfterUpdate() On Error GoTo Err_Handler Me.LblOld.Caption = "Password:" Me.TxtOldPWD = "" '1. Check if user logged in elsewhere Dim strCriteria As String strCriteria = "UserName='" & Me.CboUser & "' And LogoutEvent Is Null" If DCount("*", "tblLoginSessions", strCriteria) > 0 Then If DLookup("ComputerName", "tblLoginSessions", strCriteria) GetComputerName Then 'user logged in on another computer FormattedMsgBox "User " & Me.CboUser & " is already logged in at workstation " & DLookup("ComputerName", "tblLoginSessions", strCriteria) & " " & _ "@User " & Me.CboUser & " MUST logout from that computer before logging in again @", vbCritical, "Already logged in" Me.CmdLogin.Enabled = False CboUser = "" Exit Sub Else 'end previous session for this user on current computer so a new session can be started CurrentDb.Execute "UPDATE tblLoginSessions SET LogoutEvent = Now()" & _ " WHERE UserName=GetUserName() AND LogoutEvent Is Null AND ComputerName=GetComputerName();" End If End If '2.Get user info If Trim(Me.CboUser & "") "" Then 'StrUserName = FindUserName() 'ONLY use this if you want to use the default network user name strUserName = Me.CboUser 'user name from combo strComputerName = GetComputerName() ' StrPassword = DecryptKey(Me.cboUser.Column(2)) strPassword = RC4(Me.CboUser.Column(2), "RC4_Key") blnChangeOwnPassword = Me.CboUser.Column(3) intPasswordExpireDays = Me.CboUser.Column(4) intAccessLevel = Me.CboUser.Column(5) End If '3. Does the user have a current password 'If DecryptKey(Me.cboUser.Column(4)) = "Not Set" Then If RC4(Me.CboUser.Column(2), "RC4_Key") = "Not Set" Then bFlag = False FormattedMsgBox "You have not set a login password yet. " & _ "@You must setup a password before you can access the application. @", vbExclamation + vbOKOnly, "Setup Login Password" Me.TxtOldPWD.Visible = False Me.TxtNewPWD.Visible = True Me.TxtConPWD.Visible = True Me.TxtNewPWD.SetFocus Me.LblNew.Caption = "New Password:" Me.LblCon.Caption = "Confirm Password:" Exit Sub Else bFlag = True Me.TxtOldPWD.Visible = True Me.TxtNewPWD.Visible = False Me.TxtConPWD.Visible = False End If '4. If the user has a password and they can change their own password is it due to expire If bFlag = True And intPasswordExpireDays > 0 And blnChangeOwnPassword = True Then Dim DaysLeft As Integer Dim DateExpire As Date DateExpire = DateAdd("d", intPasswordExpireDays, Me.CboUser.Column(6)) DaysLeft = DateDiff("d", Date, DateExpire) 'Debug.Print DateExpire, DaysLeft 'Which option to choose Select Case DaysLeft Case Is < 0 ' Mandatory change FormattedMsgBox "Your password has expired and MUST be changed now. " & _ "@First enter your OLD password @", vbExclamation + vbOKOnly, "Password Expired" Me.TxtOldPWD.Visible = True Me.LblOld.Caption = "Old Password:" Me.LblNew.Caption = "New Password:" Me.LblCon.Caption = "Confirm Password:" bReset = True Case Is < 8 ' Optional change If FormattedMsgBox("Your password expires in " & DaysLeft & " day(s). " & _ "@Do you want to change it now? @", vbQuestion + vbYesNo, "Password Expires Soon") = vbYes Then Me.TxtOldPWD.Visible = True Me.TxtNewPWD.Visible = True Me.TxtConPWD.Visible = True Me.LblOld.Caption = "Old Password:" Me.LblNew.Caption = "New Password:" Me.LblCon.Caption = "Confirm Password:" bReset = True End If Case Else ' Not applicable for change Me.LblOld.Caption = "Password:" bReset = False End Select End If '5. Which control to go to If bFlag = False Then Me.TxtNewPWD.SetFocus Else Me.TxtOldPWD.SetFocus End If Exit_Handler: Exit Sub Err_Handler: MsgBox "Error " & Err.Number & " in cboUser_AfterUpdate procedure: " & Err.Description Resume Exit_Handler End Sub Private Sub cboUser_NotInList(NewData As String, Response As Integer) On Error GoTo Err_Handler FormattedMsgBox NewData & " is not a valid user name " & _ "@Please retry or refer to your system administrator " & vbCrLf & _ "to add this new user to the database. @", vbCritical + vbOKOnly, "'" & NewData & "' not in list" CboUser = "" 'block default Access not in list error message Response = acDataErrContinue Exit_Handler: Exit Sub Err_Handler: MsgBox "Error " & Err.Number & " in cboUser_NotInList procedure: " & Err.Description Resume Exit_Handler End Sub

[@rickstephens1216]

Please correct me if I'm wrong but one major reason I haven't used ADO in the past is the inability to use multiple criteria in an ADO query. Am I still correct about this? With a sequel backend this can be overcome using T-SQL or stored procedures but not using the standard backend Access Database.

[@ProgrammingMadeEZ]

Rick Stephens Typically I build my SQL strings in VBA and fill the parameters into the string. You can add multiple parameters to the command object if that's what you'd prefer. ADO can call a stored procedure specifically, but even with DAO I still prefer just using the EXEC command and it makes the string usable for either.

[@tomservo75]

I'm glad I found this video! ADO, DAO, OLEDB, ODBC, Jet, I consider myself very well-learned in Access but this alphabet soup continues to confuse me :(

[@johnywhy4679]

8:10 But the only time you're using DAO is in VBA too.

[@ProgrammingMadeEZ]

Bound forms use DAO as well as the stored queries.

[@johnywhy4679]

@@ProgrammingMadeEZ You mean under the hood?

[@johnywhy4679]

2:58 "No support for stored procedures". Meaning? Can't edit them? Can't create them? Can't receive data from them?

[@westsideslasha]

I say 'dao' and 'ado' as though they are not acronyms

[@johnywhy4679]

13:49 "slower" seems to contradict previous point, "quickly". Also contradicts your comments at 26:37.

[@johnywhy4679]

17:51 "ADO.NET not supported in Access" - If ADO and ADO.NET are very similar, as you mentioned, then why would you need this? - Access is supported in ADO.NET. That means, you can write a C#.NET program to control Access.