Introduction

Microsoft Office Access is a relational database management system released by Microsoft. It combines the characteristics of the Microsoft Jet Database Engine and graphical user interface, and is one of the system programs of Microsoft Office.

Microsoft Office Access is a database management system that combines the graphical user interface of a database engine with software development tools. It is a member of Microsoft Office and is sold separately in both professional and higher versions of Office. On September 25, 2018, the latest Microsoft Office Access 2019 was released in Microsoft Office 2019.

MS ACCESS stores data in its own format in an Access Jet based database engine. It can also directly import or link data (which is stored in other applications and databases).

Software developers and data architects can use Microsoft Access to develop application software, and "advanced users" can use it to build software applications. Like other office applications, ACCESS supports the Visual Basic macro language, which is an object-oriented programming language that can reference various objects, including DAO (Data Access Objects), ActiveX data objects, and many other ActiveX components. Visual objects are used to display tables and reports, and their methods and properties are in the VBA programming environment. VBA code modules can declare and call Windows operating system functions.

Function

One of the significant improvements of Access 2007 is to eliminate traditional menu operations and replace them with functional areas, where users can perform the vast majority of database management related operations. By default, Access 2007 has the following four functional areas, each divided into multiple groups based on the function of the command.

Start

The "Start" ribbon includes 8 groups: view, clipboard, font, formatted text, record, sorting and filtering, search, and Chinese simplified and traditional conversion. Users can operate on Access2007 in the "Start" ribbon, such as copying and pasting data, modifying font and size, sorting data, etc.

Create

The "Create" ribbon includes five groups: tables, forms, reports, others, and special symbols. The commands contained in the "Create" ribbon are mainly used to create various elements of Access 2007.

External Data

The "External Data" ribbon includes four groups: import, export, collect data, and SharePoint list. In the "External Data" ribbon, it mainly processes data outside of Access2007.

Database tools

The "Database Tools" ribbon includes five groups: macros, show/hide, analyze, move data, and database tools, mainly for advanced operations on the Access 2007 database.

In addition to the four functional areas mentioned above, there are also some hidden functional areas that are not displayed by default. The relevant functional areas will only be displayed during specific operations. For example, when performing a table creation operation, the "Data Table" ribbon will automatically open.

Advantage

Microsoft Access Basic provides a rich development environment. This development environment provides you with sufficient flexibility and control over the Microsoft Windows application program interface, while also protecting you from various troubles encountered when developing in advanced or low-level language development environments. However, many aspects of optimization, effective data, and modularity can only be used by application designers. Developers should strive to use algorithms with caution. In addition to general programming concepts, there are also some special storage space management techniques that can improve the execution speed of applications and reduce the storage resources consumed by applications.

  

Increase speed and reduce code load

You can use several techniques to improve your encoding speed, but you cannot find an effective replacement algorithm. The following suggestions can improve your encoding speed while reducing the storage space consumed by your application.

Using integers for mathematical operations

Even if Microsoft Access uses a joint processor to handle floating-point arithmetic, integer arithmetic is always faster. When your calculation does not contain decimals, try using integers or long integers instead of variables or double integers. Integer division is also faster than floating-point division. When using other valid data types, there is a warning that there is nothing that can replace a valid algorithm.

Avoid using procedure calls

Avoid using subroutines or function calls in the loop body. Each call adds a burden to the coding due to additional work and time. Each call requires pushing the local variables and parameters of the function onto the stack, and the stack size is fixed and cannot be arbitrarily increased, and it must also be shared with Microsoft Access.

Be cautious when using variable length data types

Variable length data types provide greater flexibility, such as allowing for correct handling of null values and automatic handling of overflows. In addition, this data type is larger and consumes more storage space than traditional data types. As mentioned earlier, variables with variable length data types are relatively slow in mathematical calculations.

Using variables to store attributes

Searching and setting variables is faster than performing these operations on properties. If you want to obtain or look up a property value multiple times, assign the property to a variable and use this variable instead of the property, your code will run much faster. For example, in a loop, if you look up a controlled attribute in a table, it would be faster to assign the attribute to a variable outside the loop, and then use the method of looking up a variable instead of looking up a property in the loop.

Preload table

When your application starts and sets their visible properties to 'false', if you install all your tables, the performance of your application will make you feel quite fast. When you need to display a table, you only need to set the visible attributes of the table to 'true', which is much faster than installing a table. It should be remembered that for each table you install, you will need to consume storage space from the global heap of the application.

Application of Connection String in ASP

"Driver={microsoft access driver(*.mdb)};dbq=*.mdb;uid=admin;pwd=pass;"

dim conn

set conn = server.createobject("adodb.connection")

conn.open "provider=Microsoft.ACE.OLEDB.12.0;" & "data source = " & server.mappath("bbs.mdb")