Share this Page URL

Chapter 5. Using Visual Basic to Automat... > Understanding Scope and Variables - Pg. 149

Using Visual Basic to Automate Your Database 149 The first two variables are object variables, which refer to an object of a particular type, such as a database, a table, a form, and the like. The type you declare for an object variable is critical because each type of object has its own set of properties and methods, some of which you'll use in any given program. As you saw earlier, you assign to a variable a reference to the object you want to work with. The Database and TableDef objects are part of the DAO object model, which is one of the object models you can use to work with the data in an Access database. Code samples later in this chapter and throughout this book make use of the DAO and ADO object models. After you declare a variable, you can use it to refer to an object or assign a value to it. To assign an object reference to a variable such as db or tbl, you use the Set statement, as shown in the following code. Notice that assigning a value to the variable intAnswer does not require the Set statement. You use the Set statement only when you're assigning an object reference to an object variable such as db or tbl. The code assigns the value that's returned by the MsgBox function to the variable intAnswer. What's a keyword? Dim, Const, While, and If are examples of Visual Basic keywords. Programming languages use keywords to provide instructions to the computer. Keywords are reserved by the programming language. You cannot, for example, name a procedure Dim. Set db = CurrentDb Set tbl = db.TableDefs("CampaignExpenses") intAnswer = MsgBox("Campaign Expenses were last updated on" & " " & _ tbl.LastUpdated, vbYesNo + vbQuestion, _ "Do You Want To Update the Expense Table?") Quick Check Q. What's the difference between private and public scope for a procedure? A. A public procedure can be called anywhere within the database. A private procedure can be called only within the module in which it's declared. Q. What are the two steps in creating a variable? A. Declaring the variable and its type and assigning a value or an object reference to the variable. Modifying the Import Procedure In the following steps, we'll add two variables to the Import procedure, declare them both as strings, and then use the InputBox function (which displays a simple dialog box that's used to input data) to set their value. We'll use one variable to store the name of the table we want to import data into and the other to store the name of the file we want to import. Adding these variables removes hard- coded information from the procedure, which gives the procedure greater flexibility. With these modifications, the Import procedure could be used to import any spreadsheet into any table in your database. Replace hard-coded strings with variables 1. 2. If the HelloWorld5 database isn't open, open the file, and then press Alt+F11 to open the Visual Basic Editor. In the upper left corner of the Visual Basic Editor, in the small window labeled Project, expand the folder named Microsoft Access Class Objects. You should see an item labeled Form_Im- port, as shown here: