Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
Welcome to the second part of our introductory COM course, hosted exclusively here on VB-World.
As ever, I’m your host Karl Moore and if you missed part one, be sure to check it out here.
Last week, we worked exceptionally hard to grasp the basics of COM and figure out class programming. In our instalment this week, we’ll be putting all the theory you’ve gained so far into practical use, with the creation of a real-life, database-powered COM object. And this one widget will make your future programming so easy, even David Beckham could do it.
So without further ado, let’s grab the steering wheel, put our chick mobile into top gear and speed off into the unCOMplicated <groan> world of COM…
Remember how last week we threw a class into a standard Visual Basic project?
Well, one of the main points in the elegant COM philosophy is that code can be reused.
In other words, if you create an Accounting class in Visual Basic, you shouldn’t only be able to access that from your application but perhaps also from another VB program your colleague is working on. And maybe an Excel spreadsheet that needs such data. You might even have a C++ programmer that needs to grab certain information for his latest project.
So what do you do? At the moment, you’ve worked with classes residing inside a standard Visual Basic application. And even if you give them a copy of your final .EXE program, they still won’t be able to access the classes or properties within it.
The solution is to throw all the classes into a program of their own. Then they’re "COM-enabled".
<Karl: Corr, I just invented that buzzword! COM-enabled. Let me call my trademark people…>
In other words, say your class has an AnnualProfitsToDate property. When this property is retrieved, your class dips into the company database, performs half-a-dozen calculations, then returns a value in pounds. Or dollars. Or Yen. Or green bananas. Or whatever.
Now instead of physically giving the Excel user a chunk of code to access the database, simply give him your ‘class program’. This program ‘exposes’ your class and the AnnualProfitsToDate property, allowing other users to plug into its functionality. And instead of telling the C++ programmer how to manually perform the calculation so he can access the statistic himself, you could also give him this program, saving you both a lot of work.
So let’s review this… in addition to throwing classes into Visual Basic projects, you can throw them into programs of their own, called ActiveX components, which say to other programs "Hey, I can do this provide an AnnualProfitsToDate property, update the Customers database, etc!".
And all the functionality of your ActiveX program can then be accessed from within any ActiveX-aware programming language. In other words, the AnnualProfitsToDate property can be retrieved from within Excel, C++, Access and more!
So how do you create an ActiveX program to hold your classes?
Let’s dive straight into the deep end and start creating our first real COM object, neatly sitting inside one of those ActiveX programs. Here goes:
- Launch Visual Basic
Observe the icons currently on that ‘New Project’ screen. You should have a few that begin with ActiveX. Let’s explain each of those now:
- ActiveX DLL A .DLL program that contains classes. This is our stop!
- ActiveX EXE A .EXE program that contains classes. This will be our stop later!
- ActiveX Control A project that allows you to create controls, those widgets in your toolbox. Not our stop. To find out more, click here for our ActiveX Control tutorial.
- ActiveX Document EXE A program that runs on a Web page, in .EXE format. Certainly not our stop.
- ActiveX Document DLL As above, but .DLL format. Not even remotely near our stop.
Incidentally, if you don’t have all of these items in your list, don’t worry. You’ve probably just got a cheap, substandard version of Visual Basic. Ho-humm.
Anyway, we’re really only interested in those first two options the ActiveX DLLs and Active EXEs. We’ll come back to the latter later but deal with the former foremost.
- Click ‘ActiveX DLL’
- Hit OK
Great! Now let me briefly explain what this project I fancy knocking up is all about. I want to create an ActiveX DLL that serves as a front-end to the Customers table in the Northwind database (ships with Visual Basic, usually found in the VB98 folder).
This will mean that in future when I want to access Customers information, I’ll only need to use my class and not mess around with tonnes of sticky data access code.
Heck, you’ve still gotta create the darn class but as I said earlier, COM makes your life a lil’ bit more difficult just before making it one heckuva lot easier.
Before dipping into database whatnots, let’s first christen our ActiveX program:
- Change the Name property of your class to ‘Customers’
- Click ‘Project’, ‘Project Properties’ and change the Project Name to ‘Northwind’
Brilliant! Now let’s get our class connected to that database:
- Click ‘Project’, ‘References’
- Select ‘Microsoft ActiveX Data Objects 2.1 Library’
This reference will allow us to access a database. And in the real world, that’s all most COM objects do all day sit around opening recordsets. It’s an exciting life.
- Add the following code to your class:
Dim rs As Recordset
This is the recordset object that will access our database.
Now, when someone starts the class, we want to kick start that recordset object and open a connection to the database. And when they stop using it, we want to close that connection. Let’s code this now:
- In the ‘Object’ combo, change the current ‘(General)’ to ‘Class’
- Ensure ‘Initialize’ is selected in the adjacent combo
Your code window should currently say something like:
Private Sub Class_Initialize()End Sub
Any code you enter here fires up when the class first starts. It’s a lot like the Form_Load event.
- Enter the following code in the ‘Initialize’ event:
Set rs = New Recordset rs.ActiveConnection = "Provider=Microsoft." & _ "Jet.OLEDB.4.0;Data Source=C:Program Files" & _ "Microsoft Visual StudioVB98Nwind.mdb;" & _ "Persist Security Info=False"rs.Open "select * from customers", , adOpenKeyset, adLockOptimistic
It’s worth noting here that this isn’t special class code. It’s just regular ADO database access code that you could use anywhere in Visual Basic. We’re just using it here to demonstrate how to open a database connection inside a class.
Top Tip: If your Northwind database isn’t in C:Program FilesMicrosoft Visual StudioVB98Nwind.mdb you’ll have to change the above ActiveConnection string!
So, when this class fires up, the rs object will have a link to the database. Now when the class object ends due to the program using it closing or whatever, we want to close that connection.
We code this in the Terminate event, which is pretty similar to Form_Unload. For a start, they both have an ‘n’ in their names. But more relevantly, both events fire when that particular object closes.
Let’s enter our database close code now:
- Select ‘Class’ from the Object combo and ‘Terminate’ from the Procedure combo
- In the ‘Terminate’ event, enter the following code:
rs.CloseSet rs = Nothing
Once again, this is just regular stuff. Here we’re simply closing the database, then being all good and setting rs to nothing, which calls in the heavies to effectively erase the fact that rs ever existed.
Great! Let’s continue on the next page, where we’ll be entering code to access records in our database!
Next, I’d like to add a property that allows our user to retrieve the Customer ID. Here’s a piece of sample code I created earlier:
Public Property Get CustomerID() As String CustomerID = rs("CustomerID")End PropertyPublic Property Let CustomerID(NewValue As String) rs("CustomerID") = NewValueEnd Property
Do you understand what’s happening here? The Property Get simply ‘returns’ whatever is in the ‘CustomerID’ field. This allows our end user to retrieve the property. The Property Let accepts a new value and sets the ‘CustomerID’ field to that value.
In other words, there are two parts to a property. The ‘getting’ and the ‘letting’. In fact, there’s also another one – ‘setting’ but that’s for a different day. Missing either the Get or the Let out will make the property either write- or read-only.
The power here is that we can check for certain things in these property procedures. For example, to call the Property Let here, the user could do something like:
ObjectName.CustomerID = "HALFI"
When this runs, it calls the Property Let, passing ‘HALFI’ as the NewValue string. I then set the ‘CustomerID’ field equal to NewValue. But looking at the Northwind database, I can see that the ‘CustomerID’ field is limited to just five characters. So if someone did something like this:
ObjectName.CustomerID = "HALFISTORE"
… then we’d get database errors. We could deal with this via database error handling, yes. But we could also check the length of NewValue in code. If it’s longer than five characters, you could either just snip the first five letters, ignore the new value altogether – or ‘raise’ an error. We’re going to check for the length and if it’s too long, I’ll be all-cruel and raise an error. Hehe! <Evil grin goes here>
- Add this code to your class:
Public Property Get CustomerID() As String CustomerID = rs("CustomerID")End Property
Public Property Let CustomerID(NewValue As String) ‘ If the length of NewValue is greater than five If Len(NewValue) > 5 Then ‘ … then raise an error to the program ‘ using this class! Err.Raise vbObjectError + 1, “CustomerID”, _ “Customer ID can only be up to five ” & _ “characters long!” Else ‘ … otherwise, change the field value rs(“CustomerID”) = NewValue End IfEnd Property
Brilliant! We’ve just got time for one more method before finishing this section:
- Add the following code to your class:
Public Sub Update() rs.UpdateEnd Sub
When somebody runs the Update method, it simply fires the Update method of our recordset object. Simple.
Next up, we’re going to test both this property and method with a mini sample application, plus use a special trick to follow what happens between both your class and test program.
Let’s get your class up-and-running:
- Press F5 to run your program
- If prompted for further information, select ‘Wait for Components to Start’, then click OK
Your class is now ‘alive’ but waiting for other programs to use its functionality.
- Launch another instance of Visual Basic
- Create a new ‘Standard EXE’ project
- Click ‘Project’, ‘References’
Have a scroll around the list. These are all extra widgets you can add to your project.
- Check ‘Northwind’ in the list
Northwind is your ActiveX project!
- Click OK
Now let’s add a little code to activate your project:
- Add a Command Button to Form1
- Type in the following:
Dim Test As CustomersSet Test = New CustomersMsgBox Test.CustomerIDSet Test = Nothing
This code creates a new Customers object. Then the CustomerID is displayed in a message box. Finally, the Test object is set to Nothing, closing it down.
- Hit F5 to run your test application
Top Tip: If you’re getting ‘invalid reference’ error messages when running your application, something definitely boobed. Follow these steps to ‘reset’ (1) remove the Northwind reference in your test project, (2) restart your Northwind project, (3) add the reference to Northwind back to your test project, then try again.
- Click your Command Button
It may take a few seconds to start-up after all, it’s opening a database connection and so on but after that initial delay, all calls will perform at lightening speed. You should get a message box displayed containing the name ‘ALFKI’.
- Stop your test application
Now, let’s figure out exactly what’s happening behind the scenes.
- Move your cursor to the line of code starting MsgBox Test.CustomerID
- Press F9
A red line will mark the code. When this code runs, it will stop here, at the ‘breakpoint’. Pressing F8 will run that line of code and move onto the next.
- Press F5 to run your test application (again)
- Click your Command Button
The code should stop on your MsgBox command.
- Press F8 and slowly step through each line of code
You should be automatically switched backwards and forwards between both instances of Visual Basic. This shows you exactly what happens when you access the various properties.
Notice anything interesting?
- When finished, stop your test application
Let’s continue testing your program. This time, we’ll set the CustomerID instead of merely retrieving the value.
- Change the Command Button code to:
Dim Test As CustomersSet Test = New CustomersTest.CustomerID = "KARLY"Test.UpdateMsgBox Test.CustomerIDSet Test = Nothing
This code sets the ‘CustomerID’ field, then Updates the recordset. Finally, it displays the CustomerID property, which should be set to ‘KARLY’.
- If you wish, press F9 to highlight the "Test.CustomerID =" line of code then step through once more with the F8 button to see how it all works
And that’s it! Congratulations on successfully creating and testing your raw database class!
As an experiment, why not try setting the customer ID property to a string greater than five characters in length? You should get back an error message. Try adding regular error handling code to deal with this. See what happens!
Next up, let’s take your current database class and improve it.
### Completing our Class ###
Now we want to add even more features to our class extra properties, a few methods and perhaps even an event or two.
I’m not going to walk you through every part of this code, you should be able to figure it out. So just take a look, read the comments, copy-and-paste and let’s continue:
- Replace your class code with this updated code:
Dim WithEvents rs As RecordsetPublic Event RecordsetMove()Private Sub Class_Initialize() Set rs = New Recordset rs.ActiveConnection = "Provider=Microsoft." & _ "Jet.OLEDB.4.0;Data Source=C:Program Files" & _ "Microsoft Visual StudioVB98Nwind.mdb;" & _ "Persist Security Info=False" rs.Open "select * from customers", , adOpenKeyset, adLockOptimisticEnd SubPrivate Sub Class_Terminate() rs.Close Set rs = NothingEnd SubPublic Property Get CustomerID() As String CustomerID = rs("CustomerID")End PropertyPublic Property Let CustomerID(NewValue As String) ' If the length of NewValue is greater than five If Len(NewValue) > 5 Then ' ... then raise an error to the program ' using this class, by running ' Err.Raise vbObjectError + OurErrorNumber Err.Raise vbObjectError + 1, "CustomerID", _ "Customer ID can only be up to five " & _ "characters long!" Else ' ... otherwise, change the field value rs("CustomerID") = NewValue End IfEnd PropertyPublic Property Get CompanyName() As Variant CompanyName = rs("CompanyName")End PropertyPublic Property Let CompanyName(ByVal NewValue As Variant) rs("CompanyName") = NewValueEnd PropertyPublic Property Get ContactName() As Variant ContactName = rs("ContactName")End PropertyPublic Property Let ContactName(ByVal NewValue As Variant) rs("ContactName") = NewValueEnd PropertyPublic Property Get ContactTitle() As Variant ContactTitle = rs("ContactTitle")End PropertyPublic Property Let ContactTitle(ByVal NewValue As Variant) rs("ContactTitle") = NewValueEnd PropertyPublic Property Get Address() As Variant Address = rs("Address")End PropertyPublic Property Let Address(ByVal NewValue As Variant) rs("Address") = NewValueEnd PropertyPublic Property Get City() As Variant City = rs("City")End PropertyPublic Property Let City(ByVal NewValue As Variant) rs("City") = NewValueEnd PropertyPublic Property Get Region() As Variant Region = rs("Region")End PropertyPublic Property Let Region(ByVal NewValue As Variant) rs("Region") = NewValueEnd PropertyPublic Property Get PostalCode() As Variant PostalCode = rs("PostalCode")End PropertyPublic Property Let PostalCode(ByVal NewValue As Variant) rs("PostalCode") = NewValueEnd PropertyPublic Property Get Country() As Variant Country = rs("Country")End PropertyPublic Property Let Country(ByVal NewValue As Variant) rs("Country") = NewValueEnd PropertyPublic Property Get Phone() As Variant Phone = rs("Phone")End PropertyPublic Property Let Phone(ByVal NewValue As Variant) rs("Phone") = NewValueEnd PropertyPublic Property Get Fax() As Variant Fax = rs("Fax")End PropertyPublic Property Let Fax(ByVal NewValue As Variant) rs("Fax") = NewValueEnd PropertyPublic Sub AddNew() rs.AddNewEnd SubPublic Sub Update() rs.UpdateEnd SubPublic Sub CancelUpdate() If rs.EditMode = adEditInProgress Or _rs.EditMode = adEditAdd Then rs.CancelUpdate End IfEnd SubPublic Sub MoveNext() rs.MoveNext End SubPublic Sub MovePrevious() rs.MovePrevious End SubPublic Sub MoveFirst() rs.MoveFirstEnd SubPublic Sub MoveLast() rs.MoveLastEnd SubPublic Function FindByCustomerID(CustomerID As String) As Boolean ' Uses the Find method to locate customers ' with a matching CustomerID. ' Returns True value is customer(s) found Dim varBookmark As Variant rs.MoveFirst rs.Find ("CustomerID='" & CustomerID & "'") If rs.EOF = True Then FindByCustomerID = False rs.Bookmark = varBookmark Else FindByCustomerID = True End IfEnd FunctionPublic Property Get EOF() As Boolean ' Example of a read-only property ' No Property Lets here EOF = rs.EOFEnd PropertyPublic Property Get BOF() As Boolean ' Another example of a read-only property BOF = rs.BOFEnd PropertyPrivate Sub rs_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, _ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, _ByVal pRecordset As ADODB.Recordset) ' Reacts to the recordset MoveComplete ' method - raises event with each move RaiseEvent RecordsetMoveEnd Sub
Top Tip: So far in this tutorial, we’ve only added code to one class. However you can easily add more classes to your projects, by clicking ‘Project’, ‘Add Class’. You can even make these classes work together by utilising ‘collections’. In a situation like ours however, you may want to consider using one class per table.
And now, it’s time to compile your class program.
When you do this, all the classes in your ActiveX project will magically turn into one .DLL file. After this, most tools on your computer will be able to see and use them. So after compiling, you’ll be able to use your DLL from within VB, Excel or even C++!
So let’s compile it:
- Click ‘File’, ‘Make Northwind.dll’
- Choose a location then click ‘OK’
And would you believe… that’s it! You’ve just created and ‘registered’ your first real COM component!
So, we’ve created our COM component and now we need to test it. Let’s shoot:
- Close your Northwind project
- Create a new ‘Standard EXE’
This program will access our class.
- Add a Label and List Box to Form1, like this:
That List Box will hold a list of our customers and their IDs. Here, I want the user to be able to click the Find Customer button and have another screen popup with the editable customer details. Let’s design that screen now:
- Click ‘Project’, ‘Add Form’
- Add seven Labels, seven Text Boxes and a Command Button to your Form
- Organise your controls like this:
Brilliant! That’s our application designed. Now let’s get coding.
Before working with our Northwind project, we need to add a reference to it:
- Click ‘Project’, ‘References’
Have a quick scroll up-and-down the list. This is where you find other COM components such as the ADO library and Excel automation widgets.
- Find and check ‘Northwind’, then click ‘OK’
Adding this reference means your application can now use the power of Northwind, the program you created just a few minutes ago.
- Add the following code behind Form1:
Private Sub Form_Load() Dim Cust As Customer Set Cust = New Customer Do Until Cust.EOF = True List1.AddItem (Cust.CustomerID & " " & _ Cust.CompanyName) Cust.MoveNext Loop Set Cust = Nothing End SubPrivate Sub lstCustomers_Click() Dim strCustomerID As String ' Grab the CustomerID from the List Box entry strCustomerID = Mid(lstCustomers.Text, 1, 5) ' Load the Customer Form2.LoadCustomer (strCustomerID)End Sub
Hopefully you should find this code amazingly easy to follow. During Form_Load, the program creates a new instance of our Customer object. It then loops round, adding customer IDs and company names to our List Box, until the EOF property is True.
Top Tip: EOF stands for End Of File. The EOF property of a recordset is equal to True when it has reached the ‘end’ of the records. For more information, take our database tutorial here.
Finally, the code sets our Customer object equal to nothing.
The code behind our List Box simply shows Form2 then runs the LoadCustomer method. The LoadCustomer method? Oh yes… we’ve not programmed that one yet. We’ll get round to it in just a few minutes, but all it will do is accept the Customer ID and look up our customer. And that’s as easy as your local 80s music station thanks to your new COM component.
Our code here actually chops the first five characters from the item selected in our List Box, then passes it to the LoadCustomer method. And coincidentally enough, those first five characters are the customers ID.
Let’s add the code for Form2 now:
- Add the following code behind Form2:
Dim Cust As CustomerPublic Sub LoadCustomer(CustomerID As String) Set Cust = New Customer Cust.FindByCustomerID (CustomerID) With Cust Text1.Text = .CustomerID Text2.Text = .CompanyName Text3.Text = .ContactName Text4.Text = .Address Text5.Text = .City Text6.Text = .Country Text7.Text = .Phone End With End SubPrivate Sub Command1_Click() With Cust .CustomerID = Text1.Text .CompanyName = Text2.Text .ContactName = Text3.Text .Address = Text4.Text .City = Text5.Text .Country = Text6.Text .Phone = Text7.Text .Update End With Unload MeEnd Sub
Here, we have a Customer object declared as Cust right at the top of the code.
Top Tip: If you declare an object behind, say, a Command Button as in Form1 only the code behind that Command Button can see and use it. But if you declare an object at the top of your code in General Declarations any chunk of code in your Form will be able to access it.
First off, our LoadCustomer method. This starts off by creating a new Customer object, then runs the FindCustomerByID method to locate the customer. Once located, various Customer properties are then dumped straight into our seven Text Boxes.
After any editing, our user will then click the ‘Save + Close’ Command Button. Here, our code simply takes the information from the Text Boxes and slots it straight back into the Customer object. Finally, our code runs an Update then unloads our Form.
Instead of testing our application within Visual Basic this time, I’d like you to compile it.
- Click ‘File’, ‘Make Project1.exe’
- Choose a memorable directory and click ‘OK’
- Close Visual Basic
- Run the .EXE file you just compiled
In the Form that appears, try opening a customer, changing the details, then clicking OK. Next, try reopening that customer. Are the details you entered still there? They should be because your ActiveX DLL saved them to the database.
Now sit back for a few moments and think about what you’ve just done. You’ve created your very own database application with update facilities… all in just a few simple lines of code. The COM bit took a little more effort, but your final using-application was a doddle.
And that’s the power of COM. It’s make your life harder before making it one heckuva lot easier.
- When you’ve finished testing your application, close it
If you thought things were running too sweetly, I’ve got news for you they were. So here I am with a great big spanner to throw into the works:
- Open your Northwind project
Now just pretend you’ve made a few changes and need to recompile. Righto, let’s do that now:
- Click ‘File, ‘Compile Northwind.dll’
- Choose the same directory as your last Northwind.dll (one will overwrite the other), then click OK
Top Tip: If you get errors compiling, it’s probable that something else is accessing your program. Attempt to close all running applications then try again.
- After compiling, close Visual Basic
- Attempt to run your ‘Project1.exe’ again
What’s that? You got an error message? Class doesn’t support expected interface or something like that? Oh dear.
Well, that’s my great big spanner. When you recompiled your ActiveX project, the program that used it just fell to pieces.
How can you fix this? Well, one way is to reopen your Project1 files and do a recompile. But just imagine all two hundred people in your office use Project1. That means you have to completely recompile the project and roll both it and the new DLL out smoothly to over two hundred people with minimum disruption.
Can you spell impossible?
No, what we need to do is find out why this error has occurred. Why can’t your project see and work with your newly-compiled DLL? The answer is compatibility and we’ll be dealing with this and more next time on the COM Course!
This week, we built on our class skills to create our first real COM component.
We programmed a real-life Customer object that accessed the Northwind database, then went on to create a test application to sample just a few of its features. We also discovered that after creating the initial COM component, programming becomes much easier.
In the next instalment, we’ll look at how to fix the incompatibility problem we’ve uncovered plus, we’ll be taking a sneak peak at the other ActiveX widgets available, figure out some weird thing called Instancing and finally, learn where you can go to take your COM skills to the next level.
So until then, this is Karl Moore wishing you all an exceptional evening. So it’s goodnight from me… oh, and it’s goodnight from me. Goodnight!
