Me.Access

Data type is the most important property in a field. Data type determines what type of data should be stored in a field of a table and how to present the data in a report. For example, a field with Text data type stores data in a text or numerical character. For a field that has Numeric data type, this field can only stores numerical data.

In some circumstances, we want to know what the minimum and maximum number of a data type in Access is. That is, the range of a data type. Knowing the range of a data type would inform us how to determine the field size, thus to save memory used and improve loading speed. The following table shows the data types and ranges as quoted from Access Help.

Though the runActionQuery function is enough to execute all action queries types, we can create a more specific function to run an action query for deleting records. In general, deleting records has the same method as the runActionQuery. The only difference is that we insert required parameters to delete records. Usually, deleting records in a table has general pattern of SQL statement as follows:

In MS Access, action query is query that changes data or copies data. Action query differs with select query that returns a set of records. Using action query, we can append, delete, and update records in a table. We can create a table, as well. There are four types of action queries as follows:

To open a collection of recordsets, we use letRsOpen function. The letRsOpen function open recordsets collection of a query or table. The opened recordsets collection is viewed in a computer screen. We can set whether the recordsets can be edited or read only.

In a back-end database environment, we almost never create table or query in Access database that executed as the current application. Access database that executed as the current application, also known as front-end database, is almost never used to store a table or query. In most case, the table or query stored in the front-end database is temporary in nature, so that later will be deleted soon after no longer needed.

In some circumstances when opening a front-end database, we may use a record source of a form that bound to a back-end database. MS Access does provide linked table that can be bound to a record source of a form or report, either through a table as is or a query. The problem is, that linked table is displayed on Access Navigation pane. Though it may be hidden, using external database in the form of linked table is risky. Nevertheless, we do not want any table that comes from back-end database is displayed on Access Navigation pane, in order avoiding from user who may open the linked table directly from Access Navigation Pane.

This post describes how to create functions to open and close MS Access database. The functions involve two common methods provided by MS Access, they are OpenDatabase and Close. Opening a database, especially  used in connecting to tables and queries is the first process in accessing database. Meanwhile, closing a database is the last process in accessing database.

Besides using DAO, a connection string to a database using ADO can also be set. In ADO connection string, we use at least four constants to be set to specify the connection string. They are constStrProvider,constStrDataSource, constStrUserId, and constStrPassword. The constStrProvider Const specifies database provider to be employed, for example: the provider for MS Access database is Microsoft.ACE.OLEDB.12.0. The constStrDataSource Const refers to the database file path. The constStrUserId Const refers to user identity, if the database use access level. If the connection string has a password, then the constStrPassword Const must also be set.

To specify the connection string to a database, we use getDbsConString function. There are, at least, two variables to be set to specify the connection string. They are strProvider and strPassword. The strProvider variable specifies database provider to be employed, for example: the provider for MS Access database is MS Access. If the connection string has a password, then the strPassword variable must also be set. The result of getDbsConString function is a string value.

To get database location, we use getDbsLocation function to specify Access database location that will be opened. It is using strResult variable that can be a string value or expressions. The function result is a string file path name. The complete getDbsLocation function is as follows: