Move Access-based-jobs to a Web-enabled system

dbSheetClient is a tool that can easily transfer Access to a Web-enabled system. If you are using Access, you can build a web-enabled LOB system. Moreover, by utilizing your existing Access assets, you can build your system at lower cost and in a shorter time.

What the Access version of dbSheetclient can do

Functions that Access does not have, such as reliable concurrent access control over the database or log output, are called out via API, so the Access system can be easily upgraded to web system. After being web-enabled, an in-house engineer who can develop Access apps can conduct system maintenance, so development and maintenance that meets the request from the workplace can be achieved under a rapidly changing business environment.

Features of development

Build a Web-enabled system with Access VBA programing.

The above mentioned API, which our company produced, is called out in Access VBA. By doing so, a web enabled system can be developed by Access.

Utilize the existing application part of the Access database (i.e. form, query, report, etc.) effectively.

All the application parts (form, query, report, module, etc.) of the Access file can be reused. As for the data part (table) of Access database, the table of the identical definitions is on the Server database and data is stored on the Server. The table in the Access file at client PC is used as a temporary table (work table) when it communicates with Server database.

The server function is utilized by Web service API, which is called out by VBA.

The data inquiry and data update to the Server Database are implemented by the Access VBA. The interface with the dbSheetClient Server Component is facilitated by API, “dbsAccess,”, which our company provides.

The Definition File of the Development Component for Access is comprised of information about the database and the file path of the MDB file.

In the Development Component for Access, the Definition File only designates the Server Database to be used in the program (Project) and the Access file (Access program).

Program (Project) development by VBA with API

Coordination with the dbSheet Server is established by the Web service API call.

The data communication with Server Database is implemented by API which is called out from AccessVBA.

The function of data coordination with the Server is added to the existing Access program.

The existing Access system program will be reused almost as it is, and the function of coordination to the Server is added to it by coding with API.

Concurrent access control by multiple users at update timing is supported.

An exclusive access control over shared data on the Server is applied to prevent a contradiction in data when it is updated by multiple users.

Program example of VBA

Acquisition of data from the Server Database when the form is opened: Example of VBA code

The following is a sample code to import necessary data on the Server Database to the Client PC. It imports the records extracted by the "SELECT" statement to the table of the Access file when the Access form is open.

Private Sub Form_Open()
Dim blnStatus As Boolean

'Start import
blnStatus = dbsAccess.CopyLocal_fromSvr(“ORDER”, “SELECT * FROM [ORDER]”, True, True) ‘Import Header section
blnStatus = dbsAccess.CopyLocal_fromSvr(“Order contents”, “SELECT * FROM [Order contents]”, True, True) ‘Import Details section
If blnStatus = False Then
Then MsgBox “Failed to import from the Server”, vbOKOnly + vbExclamation, “Order management system”
End If

Upon the data update, reflect it in the Server Database: Example of VBA code

The following is a sample to write the data in the Client PC to the Server Database. It rewrites the record(s) in the table ("ORDER") based on the data on the Client PC as the updated data of the Server Database.

Private Sub server Update ()
Dim blnStatus As Boolean

‘Update Server Database
blnStatus = dbsAccess.CopySvr_fromLocal(“Order”, “WHERE Order ID =” & Me.Order ID, False)
If blnStatus = True Then
MsgBox “Server update is completed”, vbOKOnly + vbExclamation, “Order management system”
End If

Log output to Server: Example of VBA code

This is a sample to output the user's operation history or processing content as a log of the Server Administration. By inserting the API, "SetSvr_UserLog" in the code where the processing log is to be outputted, the system administrator can browse the operation log of the user.

Private Sub log output _ Click()
Dim blnStatus As Boolean

blnStatus = dbsAccess.SetSvr_UserLog (“Log output”, “This is a test log.”)
If blnStatus = True Then
Execution result text. = "Log output is completed."
Execution result text. = "Log output failed"
End If