Access is a relational database management system from Microsoft. Access can use data stored in Access, SQL Server , Oracle, or any ODBC-compliant data container including MySQL Access 1.0 was the first version released in November 1992. Since then, the different versions have been released: 2.0, 95 (with Office 95), 97 (with Office 97), 2000 (with Office 2000), 2002, 2003 (with Office XP), and 2007 (with Office 2007).
Access is used by small businesses, within departments of large corporations, and by hobby programmers to create ad hoc customized desktop systems for handling the creation and manipulation of data. Access can be used as a database for basic web based applications hosted on Microsoft's Internet Information Services (IIS) and utilizing Microsoft Active Server Pages (ASP). Most typical web applications should use tools like ASP/Microsoft SQL Server.
One of the benefits of Access is its relative compatibility with SQL (structured query language) - queries may be viewed and edited as SQL statements, and SQL statements can be used directly in Macros and VBA Modules to manipulate Access tables. Users may mix and use both VBA and Macros for programming forms and logic.
Access allows relatively quick development because all database tables, queries, forms, and reports are stored in the database. For query development, Access utilizes the Query Design Grid, a graphical user interface that allows users to create queries without knowledge of the SQL programming language. In the Query Design Grid, users can chose the source tables of the query and select the fields they want returned by clicking and dragging them into the grid. Joins can be created by clicking and dragging fields in tables to fields in other tables. Access allows users to view and manipulate the SQL code if desired. Two database access libraries of COM components are provided: the legacy Data Access Objects (DAO), which was superseded for a time (but still accessible) by ActiveX Data Objects (ADO); however (DAO) is still included in Microsoft Access 2007.
One of the drawbacks of Access database is that it scales poorly if data access is via a network and when access to larger projects involving multiple concurrent users which slows down processing speed - it because it is a desktop application, not a true client-server database. One recommended technique is to migrate to SQL Server and utilize Access Data Projects. This allows stored procedures, views, and constraints - which are greatly superior to anything found in Jet. However, an Access "front end" (ie. forms, reports, queries and VB code) can be used against a host of database "backend", including JET (file-based database engine, used in Access by default), Microsoft SQL Server, Oracle, and any other ODBC-compliant product.
A database management system (DBMS) such as Access, FileMaker Pro, Oracle or SQL Server provides you with the software tools you need to add, modify or delete data from the database, queries about the data stored in the database and produce reports summarizing selected contents. Three of the major components of Access database are - tables, queries, and forms. Queries in Access provide the capability to combine data from multiple tables and place specific conditions on the data retrieved. Access also provides forms interface that allows users to enter information and pass it to the database. Access Reports allows users to quickly produce attractively formatted summaries of the data contained in one or more tables and queries. Through the use of wizards, reports can be produced in a matter of minutes. Access also provides web integration. If you have a formatted report that you would like to share with Internet or Intranet users, you can simply export it to an HTML file and publish it to your organization's web server. Access database also allows SQL Server integration.
Get started with Access Database
In this site, I will not be showing you the VBA
fundamentals such as VBE menus and windows, creating Modules, sub
procedures and functions, declaring variables and constants, assignment
statements, arrays, loop structures and decision statements, errors
handling and debugging. These are in large parts fundamentals common to
the VBA programming for Excel which you can find plenty of explanation
and examples in my VBA Excel page. Instead I will show you the basics
that are specific to Access programming such as VBA Objects, Properties,
Methods and unique parameters, Forms and Controls, Events Procedures,
Reports, Queries using ADO, ADOX, and SQL.
VBA is used to help Access to accomplish what the database can not do on
its own, and therefore you should maximize all the built-in functions in
database before you would want to develop your own VBA code. In fact you
may not realize, the queries and macros that you have done for your
database, are the predefined built-in VBA codes that Access application
has provided. There are many ready built-in programs and class libraries
in Access application that you should always make full use of them
before you will want to use VBA codes to fill in the remaining jobs that
need to be done. That is because in certain complex situations, VBA
syntax for Access can be more difficult and confusing than VBA for
Excel. You can download to experience the following Access database
example which has no VBA code in it, and to realize that predefined
built-in queries and macros can also allow you to fully automation your
jobs just as VBA does. Basically, my macro run these activities:
1. import two text files
2. link tables and query safety stock status
3. query excess inventory status
4. query surplus inventory status
5. query before-stockout need-to-buy status
6. query stockout need-to-buy status
7. transfer the five tables result to five Excel worksheets in the c:
directory as "template.xls". It creates the workbook and worksheets if
there is not already one that exists. It overwrites all existing data in
the worksheets.
DOWNLOAD
the above example here. The .exe file
will extract all the files to C:\myDatabase. When you open up the
database file, there are three option
buttons for you to select - run macro, close the form, or quit Access.
Here is a second example rather similar to the above example, except that when the table in Access is exported to Excel, a workbook will be fired up automatically to run further program through its visual basic code. The exe file will extract all the files to C:\WILLIAM\... Click to run the database file (MyDatabase.mdb). The ReadMe file explains to you all the steps clearly. DOWNLOAD it here. Have fun!
Following is a tabulated list all of the built-in macros actions that
are available in Access 2003. On the right are the descriptions of the
macro actions, and you can click on the hyperlinks to see for more detail
explanation and
examples. The
sample NorthWind database (it resides in your installed
Office Sample folder. It comes with your Office Pro CD but isn't
installed by default) has examples on most of these macro actions,
together with queries and forms. It is advisable that you constantly go
back to study the database's samples as you start to go through each of
these macro actions.
|
AddMenu Apply Filter Beep CancelEvent ClearMacroError Close CloseDatabase CopyDatabaseFile CopyObject DeleteObject Echo Introduction to macros FindNext FindRecord GoToControl GoToPage GoToRecord Hidden Language Element Hourglass |
LockNavigationPane Maximize Minimize MoveSize MsgBox NavigateTo OnError OpenDataAccessPage OpenDiagram OpenForm OpenFunction OpenModule OpenQuery OpenReport OpenStoredProcedure OpenTable OpenView OutputTo PrintOut |
Quit RemoveAllTempVars RemoveTempVar Rename RepaintObject Requery Restore RunApp RunCode RunCommand RunMacro RunSavedImportExport RunSQL Save SearchForRecord SelectObject SendKeys SendObject SetDisplayedCategories |
SetMenuItem SetProperty SetTempVar SetValue SetWarnings ShowAllRecords ShowToolbar SingleStep StopAllMacros StopMacro TransferDatabase TransferSharePointList TransferSpreadsheet TransferSQLDatabase TransferText |
Opening, Closing, Printing and Saving Tables, Queries, Forms, and Reports Renaming, Copying, Deleting, Importing, and Exporting
Objects Running Another Application for MS-DOS or Windows Testing Conditions and Controlling Action Flow Setting Values Searching for Data Controlling Display and Focus Building a Custom Menu and Executing Menu Commands Informing User of Actions
Macro Action
Purpose
Close
Closes specified or active window for a table, query, form,
or report.
OpenForm
Opens a form in Form view, Design view, Print Preview, or
Datasheet view. Can apply filter or Where condition.
OpenModule
Opens the specified Visual Basic module in Design view.
OpenQuery
Opens a query in Datasheet view, Design view, or Print
Preview. If an Action query, updates performed.
To specify parameters for an Action query, use the RunSQL
action.
OpenReport
Opens a report in Design view or Print Preview or prints the
report immediately. For Print and Print Preview, can also
specify a filter or Where condition.
OpenTable
Opens a table in Datasheet view, Design view, or Print
Preview.
OpenFunction
open a user-defined function in Datasheet view, inline
function Design view, SQL Text Editor view, or Print
Preview.
OpenDiagram
open a database diagram in Design view. The Diagram Name in
the Action Argument is a required argument.
RunSQL
Runs the specified SQL Insert, Delete, Select...Into, or
Update statement. Can refer to form controls in the
statement to limit the affected records. See the
RDoCmd.RunSQL example.
OpenStoredProcedure Action
open a stored procedure in Datasheet view, stored procedure
Design view, or Print Preview. This action runs the named
stored procedure when opened in Datasheet view. You can
select the data entry mode for the stored procedure and
restrict the records that the stored procedure displays.
PrintOut
Prints the active database object. You can print datasheets,
reports, forms, and modules.
Save
Saves the specified object or the active object if none is
specified.
CopyObject
Copies the specified database object to a different
Microsoft Access database or to the same database
with a new name.
CopyDatabaseFile
make a copy of the current Microsoft SQL Server 7.0 or later
database connected to your Microsoft Access project.
DeleteObject
Deletes any table, query, form, report, macro, or module.
OutputTo
Exports the specified database object to a Microsoft Excel
file (.xls), rich-text file (.rtf), text file (.txt), or
HTML file (.htm).
Rename
Renames the specified object.
SendObject
Send the specified Access datasheet, form, report, module,
or data access page as an attachment in
an e-mail, where it can be viewed and forwarded. You can
include objects in Microsoft Excel 2000 (*.xls),
MS-DOS text (*.txt), rich-text (*.rtf), or HTML (*.html)
format in messages for Micros oft Outlook,
Microsoft Exchange, or another electronic mail application
that uses the Mail Applications Programming
Interface (MAPI).
TransferDatabase
Imports or exports data to or from the current database from
or to another database (Access, Paradox,
Jet, ODBC). Can also use this action to attach tables or
files from other database.
TransferSpreadsheet
Imports data from a spreadsheet file into the current
database or exports data from the current database
into a spreadsheet file.
TransferText
Imports data from a text file into the current database or
exports data from the current database into a
text file. See the
DoCmd.TransferText example.
TransferSQLDatabase
Transfer a complete Microsoft SQL Server database to another
SQL Server database.
RunApp
Run a Microsoft Windows-based or MS-DOS-based application,
such as Microsoft Excel, Microsoft Word, or Microsoft
PowerPoint, from within Microsoft Access. For example, to
open up Excel, in the RunApp macro, you type
"Excel.exe" "D:\YourFolderName\YourWorksheet.xls"
CancelEvent
Cancels the event that caused the macro to run.
RunCommand
Run a built-in Microsoft Access command. The command may
appear on an Access menu bar, toolbar,
or shortcut menu. See the
DoCmd.RunCommand example.
RunCode
Runs a Visual Basic Function procedure.
RunMacro
Runs a macro.
StopAllMacros
Stops all currently running macros.
StopMacro
Stops the currently running macro. Use to stop a macro when
a certain condition is met.
Quit
Quits Microsoft Access.
Requery
Refreshes the data in a control that is bound to a query
(such as a list box, combo box, subform).
Use requery without an argument to refresh the data in the
active object (form or datasheet).
SendKeys
Sends keystrokes to Microsoft Access or another active
application. If you intend to send keystrokes
to a modal form or dialog box, you must execute SendKeys
before opening the modal form or dialog box.
SetValue
Sets the value for a control, field, or property on a form
or report. For example, can use SetValue to
calculate a new total in an unbound control or to affect the
Visible property of a control (which determines
whether you can see that control).
ApplyFilter
Applies a filter or query or SQL WHERE clause to a table,
form, or report.
FindNext
Finds the next record that meets the criteria specified with
the most recent FindRecord action or Find
dialog box.
FindRecord
Finds the first or next record that meets the specified
criteria. Records can be found in the active form
or datasheet.
GoToRecord
Moves to a different record and makes it the specified
record the current record in a table, form, or
query. Can move to the first, last, next, or previous
record.
Echo
Hides or shows the results while a macro runs.
GoToControl
Sets the focus to the specified control. It means selecting
the specified field on the active datasheet or form.
GoToPage
Selects the first control on the specified page of the
active form.
Hourglass
Changes the mouse pointer to an hourglass while the macro
runs.
Maximize
Maximizes the active window.
Minimize
Minimizes the active window.
MoveSize
Moves and sizes the active window.
RepaintObject
Completes any pending screen updates or pending
recalculations of any formula in the controls on the
specified object, or on the active object if none is
specified.
Restore
Restores a maximized or minimized window to its previous
size.
Requery
Refreshes the data in a control that is bound to a query
(such as a list box, combo box, subform, or a
control based on an aggregate function such as DSum). When
other actions (such as inserting or deleting a row in the
underlying query) might affect the contents of a control
that is bound to a query, use Requery to update the control
values. Use requery without an argument to refresh the data
in the active object (form or datasheet).
SelectObject
Selects the window for the specified object. It restores the
window if it was minimized. You can then run
an action that applies to that object.
ShowAllRecords
Removes any filters previously applied to the active table,
query, or form.
ShowToolbar
Shows or hides a built-in toolbar or a custom toolbar.
SetMenuItem
Sets the state of menu items (enabled or disabled, checked
or unchecked) on custom menus. Works only on custom menus
created using menu bar macros.
AddMenu
Adds a drop-down menu to a custom menu bar for a form or
report. Each menu on the menu bar requires
a separate AddMenu action. Each AddMenu macro action must
have a name that corresponds to the menu name on the menu
bar of the custom menu. An AddMenu action can also refer to
another macro that uses an AddMenu action to build submenus.
Beep
Causes the computer to beep.
MsgBox
Displays a message box containing a warning or informational
message.
SetWarnings
Turns all system messages on or off. This has the same
effect as clicking OK or Yes in each message box. It does
not halt the display of error messages. Use this macro with
Echo set to Off to avoid displaying the messages.