WX0998: Macro and Module Questions and AnswersLast reviewed: October 21, 1997Article ID: Q114813 |
The information in this article applies to:
Moderate: Requires basic macro, coding, and interoperability skills. The "Macro and Module Questions and Answers" (WX0998) Application Note contains questions and answers about Microsoft Access version 2.0 macros and modules. You can obtain this Application Note from the following sources:
THE TEXT OF WX0998
Microsoft(R) AnswerPoint Services Application Note (Text File)
WX0998: MACRO AND MODULE QUESTIONS AND ANSWERS
Revision Date: 3/94
No Disk Included
The following information applies to Microsoft Access(R) version 2.0.
| INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY |
| ACCOMPANY THIS DOCUMENT (collectively referred to as an Application |
| Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER |
| EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED |
| WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR |
| PURPOSE. The user assumes the entire risk as to the accuracy and |
| the use of this Application Note. This Application Note may be |
| copied and distributed subject to the following conditions: 1) All |
| text must be copied without modification and all pages must be |
| included; 2) If software is included, all files on the disk(s) |
| must be copied without modification (the MS-DOS(R) utility |
| diskcopy is appropriate for this purpose); 3) All components of |
| this Application Note must be distributed together; and 4) This |
| Application Note may not be distributed for profit. |
| |
| Copyright (C) 1994 Microsoft Corporation. All Rights Reserved. |
| Microsoft, Microsoft Access, and MS-DOS are registered trademarks |
| and Windows is a trademark of Microsoft Corporation. |
|---------------------------------------------------------------------|
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, you can order item number Q112107 by selecting the
FastTips Technical Library option from the FastTips Main Menu.
For additional information about assigning permissions to a
user, you can order item number Q112106 by selecting the
FastTips Technical Library option from the FastTips Main Menu.
For additional information about adding a user to a group, you
can order item number Q112063 by selecting the FastTips
Technical Library option from the FastTips Main Menu.
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, you can order item number Q112108 by selecting
the FastTips Technical Library option from the FastTips Main
Menu.
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, you can order item number Q112109 by
selecting the FastTips Technical Library option from the
FastTips Main Menu.
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. You can also order item number Q112065
by selecting the FastTips Technical Library option from the
FastTips Main Menu.
- 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, you
can order item number Q96576 by selecting the FastTips
Technical Library option from the FastTips Main Menu.
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.
TO OBTAIN THIS APPLICATION NOTE
Keywords : Fstqa Softlib kbappnote kbfile kbfasttip Version : 2.0 Platform : WINDOWS Hardware : x86 Issue type : kbinfo |
================================================================================
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |