|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Late Binding By Richard Rost 
5 years ago
Early vs. Late Binding in Microsoft Access VBA In this video, I'll discuss the differences between early vs. late binding in Microsoft Access VBA. We'll see how to take the code from my Outlook Email video and change it from early to late binding for greater compatibility. Bradley from Garland City, Arkansas (a Platinum Member) asks: I used the code in your Send Email from Access using Outlook video, and it works great on my PC, but a few people in the office get error messages. Could it be because they have different versions of Office installed? MembersThere is no Extended Cut for this video, however Members get access to the Extended Cuts for ALL of my TechHelp videos, including the original Send Email video. I have posted an updated database file for the Gold Members to download with the late binding code.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Links
Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, early binding, late binding, early v late binding, static binding, dynamic binding, compile error, user-defined type not defined, variable not defined, object library, references, compile time, runtime, IntelliSense, dim as object, createitem, outlook.application, olmailitem, olformathtml
IntroIn this video, we will talk about the differences between early and late binding when programming with VBA in Microsoft Access, using the example of sending email through Microsoft Outlook. I will explain the advantages and disadvantages of each approach, show you how to convert your code from early binding (which requires specific Outlook references) to late binding for better compatibility across different Office versions, and discuss how to handle constants and objects without the Outlook library reference. This video is intended for advanced Access users comfortable with VBA.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. My name is Richard Rost. I'm your instructor.This one is for the advanced Access users, the ones who are doing some VBA programming. No complaints from the beginners that this is over your head. Sometimes people complain, but I'm telling you upfront, this one is for the advanced people. In this video, we're going to talk about early versus late binding in your Microsoft Access VBA. Today's question comes from Bradley in Garland City, Arkansas. One of my platinum members, Bradley says, I use the code in your send the email from Access using Outlook video, and it works great on my PC. But a few people in the office get error messages. Could it be because they have different versions of Office installed? Yes, Bradley, that's definitely possible. In fact, that's one of the problems with having different versions of Office in your office. Someone's got Office 2019, someone else is 2013, someone else is 2007. I always recommend to my clients if you've got a multi-user setup, try to get the same version of Office on all the machines because stuff like this happens. But I might have a good fix for you. We might be able to fix it by using late binding in our VBA. Let me explain what I mean. First up, there are two prerequisites for this video. This is a more advanced video. This goes out to everybody who knows how to do a little VBA. If you don't know VBA, go watch my Intro to VBA video. It's a free video. It's on my website. It's on my YouTube channel. Also, go watch my Send Email video. I show you how to send email from Access using Microsoft Outlook. Here I am in a copy of the database that we built in the Send Email video. When we put in our Outlook stuff, we had to go up to Tools, References, and add a reference to the Microsoft Outlook 16 Object Library. This is called early binding. In other words, we're specifically telling Access we're going to use stuff from this Outlook Library. Now, the problem is, if you've got people using different versions of Office, they're not going to have the Outlook 16 Object Library. They might still have an Outlook Library, but it might be Outlook 11 or Outlook 8 or some older version. In which case, that reference won't work. But we might be able to get around it by not using this. Let me show you how we can get around this. Before we do, let's talk a little bit about early versus late binding. It's also called static or dynamic binding. You might hear it called that. What is binding? Binding is basically matching the function calls that you're going to use with the actual code that's written by the programmer at Microsoft. For example, he's got a mail item, and some of the objects from mail item might include sending the mail, specifying the recipient, specifying the body and the subject. You have to match up your calls in your VB when you're typing, your parameters, with what the object library is expecting. Just like here with DoCmd.OpenForm. This is the first parameter. OpenForm is expecting that. So if we're going to use the Outlook library, then we need to know what order to accept our parameters and what some of the constants are, that kind of stuff. It's basically matching up your function calls with what the programmer programmed the functions to be. Early binding happens at compile time. When Access looks at all your code and compiles it into machine language, you can check all the references and make sure you got all your variables spelled right and that kind of stuff. Whereas late binding happens at runtime. It doesn't do any pre-compiling stuff. Late binding just accepts the fact that you know what you're doing. It doesn't try to help you out at all. Late binding is supposed to be faster, but in reality, maybe just a teeny tiny bit. On newer computers, you can barely notice it. On some old, 10 year old machines, that might have been a big deal, but now, I can barely notice a difference. One of the benefits of early binding by setting up that reference - that's early binding - is you get IntelliSense and you get the object library references. So when you type in something in your code like that, the little m dot (that's the mail item), it gives you all the properties and the methods and the stuff you can use with that object. With late binding, you don't get any of that. You have to know what to type in. Early binding also gives you friendly constants you can use, like OLMailItem. That's a constant that's stored in that library. Otherwise, you have to know what the number of that constant is. You might have to type in a number - one, I think, is OLMailItem. You have to look those up. I'll show you how in a minute. Early binding also gives you compile errors. If you go up to Debug, Compile, it'll say, I don't know what that is. I intentionally spelled that wrong there. I put Wtml instead of HTML and you can see it threw up a compile error. If you use late binding, you don't get that. You just won't know why your code isn't working. You'll click on run and I don't know what's going on. It won't tell you that you spelled that wrong. It just won't work. Or you'll get some other kind of crazy weird error. The benefit of late binding is that it doesn't require references to other library files, so it offers greater compatibility with other users. You've got different people using different versions of Office, as long as the Access is compatible. You can't go back to Access 2003 - it's a different file format. You could have one person running Access 2010 and one person running Access 2019 and different versions of Outlook. With late binding, you could probably still use the Outlook libraries. I haven't tested it with 2010, but with 2013, 2016 and 2019, you can share those object libraries. What's my advice? What should you use? For development, I recommend using early binding so you get IntelliSense and compile errors if you're working on a big project. Start off with early binding. Make those references to your machine. Then, later on, when you're getting ready to distribute it to other people, you might want to switch stuff over to late binding, because you need to get rid of the need to have those external references. Let me show you how to take the Outlook Mail database and switch it over from early to late binding. It's not that hard, but you've got to know what you're doing. I'm going to show you so you know what you're doing. Here we go. Here I am in the TechHelp free template again. This is the version of the database that I made with the extended cut, so it's got some extra stuff in it, but I'm going to show you a scaled-down version. In the customer form, we've got our Send Statement Balancing email. Click on that. There it goes - Outlook pops up, there's your stuff, then you just hit send. Let's go into the code, Design View, and right-click. It's a little different. In here, with the extended cut, I made Send Email a function. We're just going to rem that out, and we'll put the code right in here, like we did in the regular TechHelp video. There's a scaled-down version of the code that we had from the other TechHelp video. We've got Dim o as an Outlook application, and Access knows what that means, just like it knows what an Outlook MailItem is. How does it know, even though this isn't part of Access? It knows because of Tools, References - this guy. That's early binding. So when I say Set m = o.CreateItem(olMailItem), Access knows what to do. Then, with m, set the body format, set the HTML body = message, the To, the Subject, display it, and then kill everything else. Let's make sure it still works. There it goes. Everything's working fine. Now the problem is, if you've got other people in your office who don't have the same version of Outlook, this isn't going to work. You can see the IntelliSense right here, like m., and there's all the stuff you can do with an Outlook MailItem. But we're going to turn that off now. Let's go Tools, References, let's uncheck that box and hit OK. Sometimes you've got to stop and restart the database, but let's see if it works. Good. See, Compile error: user-defined type, not defined. Now, since I got rid of the reference, Access has no clue what an Outlook Application is. Here's what we're going to do. Instead of dimming o as an Outlook Application, we're just going to say Dim o as Object. I'm going to put a rem there so you know that's the Outlook Application. o is an Object. What's an Object? I don't know. It's like a generic carry-all - you could put anything you want in there. I'm not going to worry about it at compile time, but when this thing runs, it better work. Now what I like to do is just run Debug, Compile again and have it tell me what the next thing it doesn't know is. Outlook MailItem. What are we going to do with that? We're going to Dim that as Object as well. So now I've got two random objects. Now we got to know what to set them to. Because again, Debug, Compile - Access has no clue what a new Outlook Application is. So now we're going to say CreateObject("Outlook.Application"). Just like that, we're going to create an object called Outlook.Application. Here's where it gets a little tricky. This is a constant that is in that library. If I Debug, Compile now, it gets down to here. This constant isn't known by Access. It's in that library, so we have to figure out what that constant is. Here's a real easy trick. All you have to do is Google this with value after it. So it's OLMailItem and then value. Copy that to your clipboard. Now let's flip over to our browser. I'm just going to type in that and then value, and a reference should come up. OLMailItem constant value is 0. I actually like Google's better. Let's go to Google. You'd think that Microsoft would have the better one because this is a Microsoft thing. Value. This comes up on Google. Google's got a better thing. OLItemType: OLMailItem is 0. So all we do now is flip back over to our code and replace this with a 0. I want to keep that there. Let's put this here and then maybe remark after it so we know what it is - OLMailItem. What's next? Debug, Compile. OLFormatHTML - that's a constant, we need to find the value for it. So copy that, come back over to your browser window, and we're going to say Google, tell me what this is, please. That's a 2. OLBodyFormat: OLFormatHTML is 2, plain text is 1, rich text is 3, and unspecified is 0. So we want the 2. Again, back to my code and I'll put a 2 in front of that and save it. Debug, Compile again. Now it's finding all my other stuff that I put in a global module for the members when I made that Send Email function. Unfortunately, I have to delete all of this, otherwise it won't be able to compile. So I deleted all that stuff and I deleted any other references in this database to that SendToEmail function. Let's Debug, Compile again, and we're good. Now we should be able to run it. Come back out here. Close that. Save changes? Yes. Ready? Here we go. There it goes. It's working now with late binding. So if I come in here, there's all the code with just generic references to objects, CreateObject, CreateItem you can use. We had to replace the constants. But now you can distribute this to anybody. As long as they've got a version of Access that will run this, the version of Outlook that they have doesn't matter. As long as they have some version of Office that knows Outlook.Application, they're good to go. That goes back pretty old. So Bradley, I hope that answers your question. Members, sorry, there is no extended cut for this video. I just couldn't think of anything extra to do except maybe run through another example of the same thing. But if you guys really want to see more of this stuff, let me know. Maybe I'll do another video on it in the future. I will put the updated database in the downloads for the gold members if you guys want to grab the new code. If not, I hope you learned something and we'll see you soon. Oh, and remember that members do get access to all the other extended cut videos too, not just the ones for each video. You get all of them. There are like 200 of them now, including the extended cut video for the previous email video. I will see you again. Become a member. Click the Join button below the video. After you click the Join button, you'll see a list of all the different types of membership levels that are available. Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions and more. Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. Platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more. But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more. If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select All to receive notifications when new videos are posted. Click on the Show More link below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons and lots more. YouTube no longer sends out email notifications when new videos are posted. If you'd like to get an email every time I post a new video, click on the link to join my mailing list. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It's over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar, and it's also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there. Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. What is the primary topic discussed in this video?A. Using macros in Access to automate forms B. Early versus late binding in Microsoft Access VBA C. Importing data from Excel to Access D. Designing Access Reports Q2. What is the main issue Bradley from Garland City encountered? A. Problems with Access database queries B. Difficulty generating reports in Access C. Code to send emails using Outlook works on his PC but not on others D. Trouble with password-protecting the database Q3. What type of VBA programming practice initially requires setting a reference to the Microsoft Outlook Object Library? A. Late binding B. Early binding C. Dynamic linking D. Universal referencing Q4. Why can using different versions of Office on multiple computers cause issues with early binding? A. Office has compatibility with all other libraries B. Older versions of Outlook do not support VBA C. Each version of Office may have a different Outlook Object Library version D. Access only works on Office 2019 Q5. Early binding is also referred to as: A. Dynamic binding B. Manual referencing C. Static binding D. Visual binding Q6. Which of the following is a benefit of early binding? A. No need to set object libraries B. Access to IntelliSense and compile-time errors C. Code runs on any version of Office without changes D. No need to know object properties or constants Q7. What significant downside does late binding have compared to early binding? A. It requires all users to have the exact same version of Office B. It doesn't let you use Outlook at all C. No IntelliSense or compile-time error checking D. It makes the code slower on new computers Q8. What is a primary advantage of late binding in VBA? A. Easier access to object library documentation B. Greater compatibility across different Office versions C. Faster code execution on old computers D. Ability to use advanced Access forms features Q9. In late binding, how are objects such as Outlook.Application created in VBA? A. By importing an external DLL file B. By referencing the object library under Tools, References C. Using CreateObject("Outlook.Application") D. By calling Application.NewObject("Outlook") Q10. Which constant value should be used in late binding to create an Outlook MailItem, replacing OLMailItem? A. 1 B. 0 C. 2 D. 3 Q11. What should you do when distributing your Access database to users with unknown or different versions of Office? A. Always use early binding B. Only use macros, not VBA C. Switch code from early binding to late binding before distribution D. Use SQL Server as the back end instead Q12. What must be done with code references to constants like OLMailItem or OLFormatHTML when switching from early to late binding? A. They can be left unchanged B. They must be replaced with their numeric values C. They must be commented out D. They should be replaced with text values Q13. When using early binding, how does Access "know" what an Outlook MailItem is? A. It is built into Access by default B. By adding a reference to the Outlook Object Library in Tools, References C. By importing a macro template D. By using a built-in Access template Q14. What is a recommendation given for the development versus distribution phases of your Access VBA project? A. Use late binding throughout for consistency B. Start with late binding, switch to early at the end C. Use early binding during development, switch to late binding for distribution D. Only use macros during development, switch to VBA later Q15. What was the method shown in the video to find the numeric value of a constant like OLMailItem? A. Check the Access help file B. Ask in an online forum C. Google the constant name with "value" appended D. Use the Intellisense tooltip Answers: 1-B; 2-C; 3-B; 4-C; 5-C; 6-B; 7-C; 8-B; 9-C; 10-B; 11-C; 12-B; 13-B; 14-C; 15-C DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone is designed for those of you who are already comfortable working with VBA in Microsoft Access. If you're a beginner, this might be a bit advanced, but don't worry, there are other videos available for you to build up your skills.We're focusing today on the concepts of early binding and late binding in Access VBA, an important topic for anyone writing code, especially if you're working in an environment with multiple versions of Microsoft Office installed. Bradley from Arkansas asked about this topic after noticing that his code for sending emails through Outlook worked fine on his computer but not on some of his coworkers' machines, likely because they're running different versions of Office. This is a common problem in mixed-version offices. I always recommend standardizing the office software where possible, but in reality, many workplaces deal with a variety of Office installations like 2013, 2016, or even older ones. The good news is that there's a way to improve compatibility using late binding in your VBA code. Let me explain how this works. Before moving forward, there are two prerequisites for this lesson. First, you should have a basic understanding of VBA, so if you haven't already, check out my free Intro to VBA video on my website or YouTube channel. Second, take a look at my Send Email video, which demonstrates how to send emails from Access using Outlook. In the example database built in the Send Email video, you had to set a reference to the Microsoft Outlook object library under Tools, References. This approach is known as early binding. Early binding means you're explicitly telling Access which object library (and version) you'll be using. This can cause issues when your code runs on systems that either lack that specific version or have a different one, which is why some users will encounter errors. Early binding will tie your code to a particular version of a library, so if your code refers to the Outlook 16 library, it won't work for someone with Outlook 11 or another version. That's where late binding comes in. Let's clarify these terms. Binding itself is the process of linking the function calls in your VBA code to the actual code that Microsoft developers wrote for their objects, methods, and properties—like sending email items or setting email attributes. In early binding, this relationship is checked and locked in at compile time, allowing Access to show IntelliSense, provide meaningful compile-time errors, and offer constants like OLMailItem or OLFormatHTML that make coding easier. Late binding, on the other hand, sets up this connection at runtime. With late binding, you don't get IntelliSense or compile-time checking in the editor. You have to know exactly what to type, because VBA is not going to prompt you with suggestions for methods or properties. It also means you won't have helpful error messages before running your code—misspelling a method or property won't be flagged until you actually try to run the code. However, late binding has a big advantage: it doesn't require setting a reference to a specific object library, so your code can work across different versions of Office installed on various users' computers. For development work, I suggest starting with early binding. This gives you the benefits of intelliSense and compile-time error checking, which can be invaluable when you're building and testing your application. Once you are ready to deploy your database to other users, especially in a mixed-version environment, you should switch over to late binding to maximize compatibility. Now, let me describe how you would convert your early binding code for sending Outlook emails to use late binding. Typically, with early binding, you declare variables as specific Outlook objects. Access recognizes these types because of the library reference you set up. If you remove that reference, Access will no longer recognize types like Outlook.Application or MailItem. If you try to compile, you'll get errors saying user-defined type not defined. With late binding, instead of declaring your variables as specific types from the library, you declare them as generic objects. Then, you use the CreateObject function to create an instance of Outlook.Application at runtime. Because you are no longer referencing the Outlook library at compile time, Access won't know what the named constants (like OLMailItem or OLFormatHTML) mean. You'll have to look up the numeric values for those constants—these can be found easily online with a quick search. For instance, instead of using OLMailItem, you substitute the number 0, which represents a standard mail item. The OLFormatHTML constant, used for setting the email's body format, is replaced with 2. These small changes allow your code to run regardless of which version of Outlook is installed, as long as it supports the automation interfaces you're using. Once all your library-specific declarations and constants are changed over to late binding, your code should be more flexible and portable. Anyone in your office with Access and some version of Outlook should be able to use the feature without running into version-related reference errors. To sum up, early binding is best for development because of the useful coding aids it provides, but late binding is better for distributing solutions to users with potentially different software versions. There is no extended cut for today's video, but if you want to see more on this topic or have specific related questions, let me know and I might produce future videos on advanced VBA techniques. Gold members can download the updated database file if you want to see the completed code in action. To explore all the other extended cut videos, remember that membership at the Silver level and above grants you access to my archive of over 200 extended videos as well as additional perks. Gold members also have exclusive access to a download folder with all my sample databases and my code vault. Platinum members get all these benefits plus access to my full beginner and some expert courses not just for Access, but also Word, Excel, Visual Basic, and more. Even if you're not a member, I'll continue to produce free TechHelp videos for as long as there's interest. If you enjoy these lessons, please like the video, leave comments, and make sure you're subscribed so you don't miss any new content. For additional resources, links to related videos, and more, click the Show More section below the video. YouTube no longer sends email notifications for new videos, so if you'd like email updates, please join my mailing list. If you haven't taken my free Access Level 1 course yet, I encourage you to check it out on my website or YouTube channel. It's a comprehensive introduction to designing databases with Access and is over three hours long. Level 2 is just one dollar or free for channel members. If you have questions you'd like answered in a future TechHelp video, submit them through my website. For complete step-by-step instructions covering everything discussed here, you can watch the full video tutorial on my website at the link below. Live long and prosper, my friends. Topic ListEarly vs late binding in Access VBAAdding Outlook reference for early binding Problems with different Office versions and early binding Definition and explanation of binding in VBA Compile time vs runtime binding Benefits of early binding (IntelliSense, constants, compile errors) Drawbacks of late binding (lack of IntelliSense, manual constants) Compatibility advantages of late binding Migrating VBA code from early to late binding Modifying Dim statements for objects with late binding Replacing Outlook constants with literal values Using CreateObject to instantiate Outlook objects in VBA Debugging and compiling late-bound VBA code Distributing Access databases using late binding for wider compatibility |
||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access early binding, late binding, early v late binding, static binding, dynamic binding, compile error, user-defined type not defined, variable not defined, object library, references, compile time, runtime, IntelliSense, dim as object, createi PermaLink Late Binding in Microsoft Access |
