9. (Advanced Programming In Access 2013) DAO vs ADO
HTML-код
- Опубликовано: 30 июл 2024
- DAO vs ADO video in the "Advanced Programming in Microsoft Access 2013" series hosted by Steve Bishop. In this free advanced video tutorial series Steve will be going over Microsoft SQL Server installation, Database Migration, creating a better User Interface, using external data sources, complex Visual Basic For Applications (VBA) concepts and distributing your application.
Click here for the full playlist of "Advanced Programming in Access 2013":
• 1. (Advanced Programmi...
Click here for the Work Files of this series:
github.com/Xipooo/AdvancedPro...
Steve you are great. I really love your video.
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...
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é
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.
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.
Steve you are great. Thanks for video
Really cool, never understood this up until now. Thanks to you!
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.
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.
That came in handy!
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.
+Kevin Le I'm afraid I don't follow.
+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
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 RUclips.
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 :(
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.
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.
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?
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.
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.
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.
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
I say 'dao' and 'ado' as though they are not acronyms
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?
Not with bound forms.
@@ProgrammingMadeEZ i think you mean the form is using DAO under the hood, correct?
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?
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.
18:25 To use .NET objects in Access VBA, they would have to replace the entire Access VBA system with VB.NET.
You can make add .NET library references to an Access application.
@@ProgrammingMadeEZ So you don't mean using .NET inside of Access VBA, you mean controlling Access with an external .NET application, correct?
8:10 But the only time you're using DAO is in VBA too.
Bound forms use DAO as well as the stored queries.
@@ProgrammingMadeEZ You mean under the hood?
2:58 "No support for stored procedures".
Meaning? Can't edit them? Can't create them? Can't receive data from them?
13:49 "slower" seems to contradict previous point, "quickly". Also contradicts your comments at 26:37.
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.