Understanding and understanding access-Experience

Abstract: This article analyzes the differences between access and xbase from the perspective of xbase users, and further describes the database objects and programming methods in access.

---- Key words: Access ---- FoxPro ---- Database

---- Microsoft has two popular desktop database development tools. One is access based on the Windows environment developed by Microsoft itself, and the other is FoxPro obtained by its merger with Fox. Generally, dBase, FOXBASE, and FoxPro are collectively referred to as xbase, which has a large number of users in China, with the rapid rise of Microsoft Access in the International desktop database market and the popularity of Chinese office in China, more and more users are using msaccess (one of the components of Chinese office97 ), many xbase users are also switching to or using Microsoft Access. Because of the file organization, data type, programming language, and programming method of access
There is a big difference with xbase, which makes many xbase users confused when learning access, some users even attack access because they cannot find commands corresponding to xbase from access, thus giving up access learning. Compared with xbase,
Access has many advanced features. Once you master the basic concepts and programming methods of access and change the "xbase Way of Thinking", you can use the existing xbase knowledge to quickly master access. This article compares and analyzes xbase and access by multiple parties, so that traditional xbase users can better understand and master access.

---- 1. Main differences between access and xbase:

---- In The Big Aspect, access usage and programming methods
Similar to visual foxpro3.0 and visual foxpro5.0, you are familiar with visual foxpro3.0 and later.
Users can easily learn access. No visual
Traditional xbase users of FoxPro are learning access or visual
FoxPro requires a great ideological transformation, because access and Visual FoxPro both adopt event-based processing and face-to-face
The Program Design Method of the object. The xbase mentioned below refers to versions earlier than Visual FoxPro.

---- Different file organizations: systems built with xbase usually contain many different types of files, each table, program, report, query, index, menu, and other content are stored on the disk as files. Other types of files can be organized and managed through project files; access is very similar to the database organization of a large background database system (such as SQL Server). Different data or program elements are called objects. All
Objects are stored in a physical file, which is called a database.

---- Different programming languages: in traditional xbase, data manipulation such as tables and records or interface design adopts xbase commands for programming, in the high version of FoxPro, some SQL statements (such as SQL-SELECT) are mainly used to query data. In access, data operations such as tables and records are mainly implemented through SQL, program and Event code are written using Visual Basic for application (access basic programming for lower versions), and macros are used for simple program design. Visual Basic is a more general programming language, MS Office components can be programmed with Visual Basic.

---- Different programming methods: xbase adopts process-oriented programming methods. Even the design of a user input interface requires a long xbase program; access adopts an object-oriented programming method. The data processing function is implemented by modifying the properties of the object or executing the object method. A program segment usually forms an event process, object method, and function, A small number of consecutive long Programs appear in access. In addition, access has field-level, record-level, and table-level data reference integrity data constraints.
Yes, transaction processing functions and complete security mechanisms.

---- II. database objects in access

---- The ACCESS database window contains six different database objects: Table, query, form, report, and macro) and module ). The Access Database contains all the data like a container.
Library object. Unlike the Foxpro project file, access
Is stored in a file. To create a database object, click the create button in the Database window, modify an existing object, select an object, and click design.

---- Tables in access: tables in access are equivalent to DBF Files in xbase or database tables in Visual Foxpro. Access Table
Compared with the xbase DBF file, it has the following new features:

The field name can contain up to 64 characters
You can set the field input mask, default value, and value range. Equivalent to using @? In xbase @? The functions of picture, default, range, and other clauses in et statements. You can set the relationship between fields (by setting table attributes)

---- In access, you do not need to use special commands or operations to create and maintain index files. To create an index based on a field, you only need to select a specific index type in the index attribute of the field, indexes are maintained and used through the access system. In xbase, operations such as index on and SET index to do not need to be concerned.

---- You can create multiple tables in the ACCESS database as needed. The so-called reference integrity relationship can be established between tables, just like the multiple-to-one and one-to-many relationships created using the set relation and set skip commands in xbase, access allows you to establish a one-to-many and one-to-one relationship through graphic interface operations, and ensures the validity of the relationship between records in the relevant table through chained deletion and chained update, you cannot accidentally delete or change the data.

---- Using access tables and using xbase DBF Files is important
The difference is that the concept of "open" is different. xbase uses the use command to open the DBF file. In essence, it establishes a data buffer in the memory to provide a data channel, to view the data in the DBF file, you need to use browse or other commands. Opening a table in access is equivalent to the use + beowse command in xbase. The data in the table is processed without displaying the records in the Table. there are usually two methods in access. One is to update or change the data using an SQL statement. You do not need to open the table before executing an SQL statement; the other is to use VBA programming to manipulate the table through the data access object (DAO). When manipulating the table through Dao, you need to open the so-called recordset ), the use command in xbase is similar to the openrecordset method in Visual Basic for application.

---- Query: the query in access is essentially the data manipulation language of SQL. You can use different methods to view, change, and analyze data through queries, it is equivalent to the SQL SELECT statement and FoxPro query in FoxPro. In access, most operations on tables, records, and fields can be performed through queries. For example, many replace... with... the command in access should be completed with an action query (SQL update
). Creating and querying in access is basically the same as using SQL commands in background databases (such as Oracle and SQL Server). The SQL statements of Microsoft Jet Database Engine comply with ansi SQL and access
SQL implements almost all ansi SQL functions, some SQL commands
Not in ansi SQL.

---- Selecting a query in access is more convenient and flexible than using the SQL SELECT command in FoxPro. in access, selecting a query is equivalent to a table, and you can establish a relationship between the table and the query, A query can also be used as a record source for forms and reports like a table, and can be used to create a query again.

---- Form: the form is the main interactive interface of the system developed by using access. In some books and periodicals, the form is called a form, and the form is equivalent to using @? In xbase @? Ay? The et command is written to implement the so-called full screen editing function. The form is similar to the screen file (. SCX) in FoxPro ). Creating a human-machine interaction interface in access is like creating a screen file in FoxPro. You need to create a form object.

---- Report: the report object of access is closest to the report file (frx and FRM) in xbase ). Create a report file in xbase
Use report form... command preview or print report. After the report object in access is created, you can directly click the open button in the Database window to preview the report or print or preview the report using the openreport command in the macro or module.

---- Macro: a macro is a set of one or more operations. Each operation implements a specific function, such as opening a form or printing a report. Macros can automatically complete some common tasks. For example, you can set a macro to run when you click a command button to print a report. In xbase, there is no specific file type that corresponds to the access Macro. in Access97 and earlier versions, macros are used to create and use
User-Defined menu.
---- Module: the access module is equivalent to the program files and process files in xbase. Each module can contain one or more subprograms, functions, or attributes. subprograms are equivalent to procedure in the base ).

---- 1. User Interface Design in access
---- In the development of information systems, the design of user interfaces occupies a large workload. The User Interface Design in access is much simpler than the user interface design in xbase. in earlier versions of xbase, the user interface design needs to determine the coordinates of the text displayed on the screen and then the user @? Ay? In Foxpro, the design of the user interface has been greatly improved. When designing the user interface, the screen file (. SCX), and then generate the program file (. SPR ). The establishment and design of the form and most Windows-based development tools (such as Visual
Similar to the xbase user interface design, xbase users should understand and grasp the access form design from the following aspects:

---- 1. Understand forms and programs

---- In Foxpro, to create a user interaction interface, you must first create a screen file. The screen file contains several code snippets (codesnippet) and generate a program file with the SPR extension based on the screen file by the generator, code snippets are included in program files, and the execution program file displays an interactive user interface. In access, the form is equivalent to a screen file. The form has three different views: Design view, form view, and data table view. The design view mode is used to design or modify the form. After the form is designed, you can open the form in the form view or data table view, which is the interactive interface used for user operations. You do not need to generate program code for the form in access. After the form is created, open the form directly or switch to the form view. One of the other headaches in FoxPro is to repeatedly modify the screen when debugging the screen.
Screen, generate program code, execute program to view the running effect, any minor changes to the screen file must be generated by the program, the execution program to see the results, access does not need to generate the process, you only need to switch between the design view and the form view to know the modified form effect.

---- Form module and event process: In Foxpro ,@? Commands such as et, read, and activate can contain many clauses, such as the most commonly used valid clause. A clause is usually used with a user-defined function. When a screen file is created using a screen generator, the content of the User-Defined Function after a sub-statement can be directly input. The user-defined function program segment used by these sub-statements is called a code snippet (codesnippet ). In access, the program segment corresponding to the code segment in FoxPro is the event procedure. The so-called event process is an automatic execution process, it is used to respond to events initiated by users or program code or events triggered by the system. For example, if you want to input or modify the value of a field in Foxpro and then execute a program, the user-defined functions required by the valid clause and the valid clause must be created by entering code snippets. in access, a control) after the value is entered or modified, you must enter the program content during the event process of the afterupdate event attribute of the control to execute a program, when the value of the control changes, the after update Event System Automatically executes the event process corresponding to the event. Each form in access has a form module associated with the form. The form module stores all the event processes of the form and the common subprograms and functions of the form, this form module is automatically saved when the form is saved.

---- 2. Understanding the record source)

---- In Foxpro, you should open the relevant table before creating the screen file to establish the relationship between the table and the table, after a screen file is created, the system will ask whether to save the environment (Environment). If the environment is saved, the system will save the opened table, the generated program code contains commands for enabling and disabling tables. The "data environment" of the form in access is called the record source. The record source can be a SELECT statement of tables, queries, or SQL. Access record sources provide greater flexibility than FOXPRO's "data environment". When access forms have different record sources? When associating multiple tables, access creates a query or uses the SQL SELECT statement as the record source. The query and select statements specify the related tables and the relationship between the tables.

---- To create a FoxPro Screen file, you need to open the relevant table and save the "data environment" before the program can contain the code line for opening the table, the record source of the access form only provides the table, query name, or SQL SELECT statement in the record source attribute of the form. The record source does not generate program code. When a form is opened, it automatically processes the data of tables, queries, and select statements. There are no commands or statements used to open tables or queries.

---- 3. Understand controls, attributes, and event Processes

---- The textbox and ListBox items added to the FoxPro Screen Builder interface correspond to memory variables or field variables after the program is generated, in the screen generator, many settings for text boxes and other projects form @.. many get statements include enable, disable, Font, style, default, message, range, color, and other clauses. code snippet) A user-defined function or process in the program and @? The valid, when, and other clauses of the et statement.
In access, similar to projects such as text boxes in Screen Builder FoxPro are called controls ). @? In FoxPro @? The clauses of the et statement are implemented by the control attributes in access. For example:
In Foxpro, the @... get text disable statement is
Set the enabled attribute value of the text control to false (No). In Foxpro, use the value assignment statement me in show get text enable in access! Text. Enabled = true to change the properties of the text control. Code snippets in FoxPro are implemented through the event process in access, for example, @? In FoxPro @? The et text valid check () statement contains a valid clause and a check () user-defined function. This function implements the same function in the before Update (or after update) of the text control in access) select '[event process]' for the property value and enter the corresponding program code. Common FoxPro @? The correspondence between the clause of the et statement and access control attributes is as follows:

FoxPro clause
Access Control Properties
Function
Format and input mask attributes
Picture
Format and input mask attributes
Font
Font color name Size weight italic underline and other attributes
Style
Font italic underline and other attributes
Default
Default Value
Enabled | disable
Enabled
Range
Validation rule and validation text
Size
Left, top, height, width
Vilid
Before update, after update
When
On enter, on got focus

---- 1. "Move Record Pointer" in the form"

---- The user interface created through the FoxPro Screen Builder usually contains buttons used to move records. You can click the buttons to change the current records on the screen or window, in Foxpro, this function is generally implemented by adding pointer moving statements in the function of the valid clause of the button variable, such as Skip, Goto, and locate. When xbase users use an access form for the first time, they do not know how to implement the same functions. There are three methods to implement record navigation in the form in access:

Add the record navigation button in the lower left corner of the form. Use the Goto record macro or find record macro to change the record of the current form using the Data Access Object (DAO ).

---- If you only move the record before and after the form, you do not need to write a program. In the design view of the form, set the value of the form's navigation button to true, a navigation button will appear during the form, you can click the navigation button to move the record forward or backward. To move a record to a specific record, you must use a macro or VB programming. For example: when you click the CMD button in the form to move down three records, enter the following VB statement during the on Click Event of the CMD button:

---- Docmd. gotorecord, 3

---- The following example uses the record set and findfirst methods in the current form to locate the record to the "name" field with the value? Michael Jacob's record:

Dim rst as recordset
Dim strsearchname as string
Set rst = me. recordsetclone
Strsearchname = "zhangsan"
RST. findfirst "name =" & strsearchname
If RST. nomatch then
Msgbox "not found"
Else
Me. Bookmark = RST. bookmark
End if
RST. Close

---- Me in the program. recordsetclone is a copy of the basic query or base table specified by the recordsource attribute of the form. bookmark is the bookmark of the record set, which uniquely identifies a specific record in the form base table, basic query, or SQL statement. In this example, the findfirst method first locates records in the record set copy of the form record source, and then moves the record position of the form to the same position.

---- 1. How to implement common xbase commands in access

---- When using access for the first time, many xbase users often look for statements, functions, or implementation methods that are equivalent to the xbase command in access, the following table lists the access macro commands and VB statements or implementation methods corresponding to commonly used xbase commands:

Xbase command
Access macro command
Access VBA statement
Access operation
Use <Table>

Openrecordset Method
Usually not required. Used to Process Table records
Browse/edit
OpenTable
Docmd. OpenTable
Open the table directly in the Database window
Index on

Index attribute of the specified table Field
Sort on

Use the buttons or menu commands in the toolbar
Replace

Use SQL statements
Sum aver, etc.

Dsum () and other functions
Use SQL statements
Goto
Gotorecord
Move Method
Use record navigation buttons
Skip
Gotorecord
Move Method
Use record navigation buttons
Locate

Findfirst Method
Search and filter operations using toolbar or menu commands
Total

Use SQL statements
1. Data Types in access

---- When xbase users use access for the first time, they are not applicable to the field type of the access table, especially the numeric field type. It is precisely because of lack of adequate understanding of access data types that many users encounter some problems in the development process. The field types and their relationships between access and xbase tables are shown in the following table:

Field Type of the table in access
Field Types of libraries in xbase
Text)
Character (character)
Numeric type (short integer, long integer, single precision, Double Precision)
Numeric)
Currency (currency)
Numeric)
Remarks (Memo)
Remarks (Memo)
Date and time (date/time)
Date)
Logical type (Yes/No)
Logical)
OLE object)
General)

---- One of the problems that xbase users often encounter when using access for the first time is the numeric field type Problem in the Access Table. to define a numeric field in the DBF File of xbase, the field type, width, and decimal places must be given, when record numeric fields store data, xbase only saves the specified decimal places and automatically handles rounding. The numeric fields in the access table are different from the numeric fields in xbase, to store numeric data in a table field, you must set the field to either of short integer, long integer, single precision, double precision, or currency type, the number of decimal places with a fixed length cannot be saved when the value field stores data, nor can the number of decimal places be automatically rounded up. Although the field attributes include decimalplaces and format, the two attributes only determine the display mode of the data, rather than retaining the specified decimal places, you must handle the number of decimal places and rounding when saving the numeric data.

---- When using the access table, xbase users often encounter another problem: the null value of a field. In xbase, if no value is entered for a field, the character field is a space string and the numeric field is zero; in access, if the field does not enter a value, the value of the field is null. null is a special constant in access that indicates no value. null cannot be assigned to non-variant variables. When you use DAO to access the recorded fields, use the NZ () function to convert null to an empty string of the numeric type or zero value of the numeric type.