ACC2: Macro and Module Questions and Answers (114813)

MORE INFORMATION

1. Q. What is DAO?
   
   A. Data access objects (DAO) and collections provide a framework for using code to create and manipulate components of your database system. Objects and collections have properties that describe the characteristics of database components and methods. DAO provides the functionality of the dynasets, Snapshots, and QueryDef objects you used in version 1.x, while giving you more structure and features. All collections use the same syntax.
   
   For more information about DAO, please refer to the following:
   • Search for "data access objects" then "Data Access Objects and Collections" using the Microsoft Access Help menu.
   • Search for "converting databases to version 2.0" then "Converting Macros and Code from Version 1.x to 2.0" using the Microsoft Access Help menu.
   • See Microsoft Access "Building Applications," Chapter 7, "Objects and Collections."
   • See Microsoft Access "Building Applications," Chapter 11, "Working with Sets of Records."
2. Q. How can I use data access objects (DAO)?
   
   A. You can use DAO methods to create or modify different parts of your database, including TableDef objects, security, relationships, and so on. Three common tasks that you can accomplish with DAO are:
   
   
   • Indexing an existing field in a table.
   • Assigning permissions to a user.
   • Adding a user to a group.
   
   For additional information about indexing an existing field in a table, see 112107.
   
   For additional information about assigning permissions to a user, see 112106.
   
   For additional information about adding a user to a group, see 112063.
3. Q. How do I create an SQL pass-through query using Access Basic?
   
   A. Use the CreateQueryDef method to create an SQL pass-through query using Access Basic. Set the properties of your QueryDef as follows:
   
   
   • ConnectString: This sets the ODBC connection string, and must be at least "ODBC;". If the connection string does not include at least "ODBC;" the query is not an SQL pass-through query and you will receive a syntax error message. If you do not include the data source name, you will be prompted for it when you run the query. For more information about connection strings, search for "ODBC connection string" then "ODBCConnectString Property" using the Microsoft Access Help menu.
   • SQL statement: This is the SQL statement that is passed to the server. For additional information about the syntax of the SQL statement, please refer to your server's documentation.
   
   For additional information and an example of an SQL pass- through query, see 112108.
4. Q. How do I determine if the record being edited is a new record?
   
   A. In Microsoft Access version 1.x, the counter field is null until the record is saved. So, to check for a new record, you would check to see if the counter field is null. In Microsoft Access version 2.0, the counter field is updated as soon as you begin inserting a new record. However, the OldValue property for the counter will still be null. You can use the following expression to determine whether the record being edited is a new record:
   

             IsNull([<counterfieldname>].OldValue)
   						

   
   For additional information about checking for new records in Microsoft Access 2.0, see 112109.
5. Q. Why doesn't my version 1.x Access Basic code work correctly in Microsoft Access 2.0?
   
   A. Microsoft Access 2.0 introduces many changes and new features to Access Basic. When you convert your database from version 1.x to 2.0, your Access Basic code is not automatically converted. Because of the changes and new features, you must modify your code for it to work correctly in version 2.0. Some of the changed items include the SendKeys and DoMenuItem actions, and field name references. For more details about these changes, order the fax or mail copy of this script.
   
   
   • The SendKeys action: Some menus in Microsoft Access 2.0 have changed. One of the common uses for the SendKeys action, hiding and showing the toolbar, has been replaced with the new ShowToolbar action. To access the options in the Options dialog box (such as Show Status Bar), use the new SetOption and GetOption methods with the Application object. For more information about the SetOption method, search for "SetOption" then "GetOption, SetOption Methods" using the Microsoft Access Help menu.
   • The DoMenuItem action: Because some menus have changed, there is now a fifth argument, <Version>, that you must supply when you use the DoMenuItem action in Access Basic. For more information about the DoMenuItem action, search for "DoMenuItem" then "DoMenuItem Action" using the Microsoft Access Help menu or see 112065.
   • Using a period to reference field names: In Microsoft Access 1.x, you can reference fields using a period before the field name. For example, you can use "MyTable.Name" to refer to the Name field in the MyTable table. If you use the same expression in Microsoft Access 2.0, you reference the Name property instead of the field called Name. Use "MyTable!Name" to refer to the Name field in Microsoft Access 2.0.
   
   For more information about other changes and new features in Access Basic, search for "converting databases to version 2.0" then "Converting Macros and Code from Version 1.x to 2.0" using the Microsoft Access Help menu.
6. Q. Why doesn't a new table created using data access objects (DAO) show in the Database window?
   
   A. Database changes made using DAO are not automatically synchronized with the Database window in order to avoid affecting system performance. To view changes made using DAO, refresh the Database window by choosing a different object button, and then choose the object button for the object type you were working with. You can automate this refresh method in your code by using two SelectObject actions.
   
   When you make a change without using DAO, it is not reflected in the corresponding DAO object unless you call the Refresh method on the collection containing that object. For example, if you delete a table in the Database window, use the following expression to remove the table from the TableDefs collection:
   

             <MyDatabase>.TableDefs.Refresh
   						

7. Q. How do I include a variable in the WHERE clause of my SQL statement?
   
   A. The syntax for including a variable in the WHERE clause of an SQL statement depends on the variable's data type. Numeric variables do not require delimiters, string variables should be enclosed in single quotation marks, and date variables should be enclosed in number signs (#). Concatenate the variable and the appropriate delimiter, if required, as shown in the three examples available in the fax or mail copy of this script.
   
   For a numeric variable, use the following syntax:
   

   <myq>.sql = "select * from <table> where [<field>]=" & <mynum> & ";"

   
   For a string variable, use the following syntax:
   

   <myq>.sql = "select * from <table> where [<field>]='" & <mytext> & "';"

   
   For a date variable, use the following syntax:
   

   <myq>.sql = "select * from <table> where [<field>]=#" & <mydate> & "#;"

   
   For additional information about concatenating variables, see 96576.
8. Q. Will the macros I created in Microsoft Access version 1.x work in version 2.0?
   
   A. Macro syntax has not changed from version 1.x to 2.0, so macros created in version 1.x should work correctly. However, if your macros use the SendKeys action, you may need to make some changes to reflect the menu changes and new features in Microsoft Access 2.0.
9. Q. Can I call a function stored in my form module from a regular module?
   
   A. Functions stored in form or report modules are private to that module's form or report, and can only be called from that module's form or report. If you want to use the function in a different form, report, or global function, make it a global function by storing it in a regular module.
   
   For more information about writing and using event procedures, see Microsoft Access "Building Applications," Chapter 3, "Introducing Access Basic," and Chapter 5, "Access Basic Fundamentals."
10. Q. How do I edit a library? Do I have to unload the library first as in Microsoft Access version 1.x?
    
    A. Debugging libraries in Microsoft Access version 1.x requires you to unload the library and restart Microsoft Access each time you find an error in the library. In Microsoft Access 2.0, you can debug libraries without unloading the library and restarting Microsoft Access.
    
    To debug or edit a library in Microsoft Access 2.0, add the following line to the [Options] section of the MSACC20.INI file:
    

              DebugLibraries=True
    						

    
    Once you have added this line to the MSACC20.INI file and restarted Microsoft Access, you can edit the functions in your library. If this line is not in your MSACC20.INI file, or is set to False, you will not be able to edit your library.