dbSheetClient is a tool that makes it easy to transfer Access to a Web-enabled system. If you are using Access for business systems or considering development of a new system using Access, you can easily build a Web-enabled Line-of-business (LOB) system by using the Access version. Moreover, by utilizing your existing Access assets, you can build your system at lower cost in a shorter time.
* Please also browse You can easily utilize the same Access and convert it to a Web-enabled system
* For a more detailed explanation of dbSheetClient, please browse What is dbSheetClient?
Functions that Access does not have, such as reliable concurrent access control over the database or log output, are called out via Application Programming Interface (API), so the Access system can be easily upgraded to a Web-enabled 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.
Access version can do the following:
You can realize a Web-enabled system of a multi-site system such as company-wide deployment by merely adding a little effort to the already developed Access system.
You can fully use the functions of resource management of dbSheetClient Server component.
When building a full-scale Web-enabled system, even if you want to add functions such as user authentication, authority management, log management , and intrusion prevention, there is no need to develop a new program.
The Server component of dbSheetClient manages the database. The Access file containing the Access program is downloaded and executed on the client computer.
Even if you execute in such an operating environment, dbSheetClient can automatically and centrally manage data and programs on the server.
Therefore, unlike sharing Access files, you can maintain Access files at any time.
In the case of the Web-enabled system, since multiple users update shared data in the server, a mechanism of exclusive control is essential.
Access version provides an exclusive control function of optimistic/pessimistic locks through API.
* Access file: It means an MDB file in which the Access program is implemented.
The following features are for development using dbSheetClient Access version.
A Web-enabled system can be developed by Access VBA programming.
All the application section (forms, queries, reports, modules, etc.) of the Access file can be reused. As for the data section (tables) of Access database, the table of the identical definitions is on the server database and data is stored on the server.
The Access tables on the client computer can be used as temporary storage tables when communicating with server database.
Reference and update of data in 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.
In the dbSheetClient Development component for Access, only the server database used in the system (project) and the Access file (Access program) are specified.
* Client computer ⇒
At this time, dbSheetClient Runtime component + Microsoft Access runtime is installed on the client computer.
Description of the API functions provided by Access version.
When using API from Access, make sure that dbsAccess in Reference setting is checked and write the VBA code in the following format.
Example:
Dim blnStatus As Boolean
blnStatus=dbsAccess.CopyLocalfromSvr(parameter)
The following tables provide a description of the API functions.
| No. | API function names | Description |
|---|---|---|
| 1 | GetUserGroup | Function that acquires the dbSheetClient group ID to which the login user belongs |
| 2 | SetSvr_UserLog | Function that sends the user log information acquisition to DBS server |
| 3 | dbs_DownloadFile | Function that forwards (copies) a file from the server to the client |
| 4 | dbs_UploadFile | Function that forwards (copies) a file from the client to the server |
| 5 | DispDataMainte | Function that displays the defined data maintenance screen in a browser |
| No. | API function names | Description |
|---|---|---|
| 1 | GetAllTables | Function that acquires all the table names registered in the server DB |
| 2 | GetAllViews | Function that acquires all the view names registered in the server DB |
| 3 | ClearLocalData | Function that deletes all the data of a specified table registered in the local DB |
| 4 | CheckDataExists | Function that confirms the existence of a specified condition data in the server DB |
| 5 | ExecQuerySvr | Function to execute SQL statement onto the Server DB |
| 6 | CopySvr_fromLocal | Function that updates the data that meets specified conditions from local to Server DB |
| 7 | CopySvr_FromLocal_No | Function that updates (or adds if it does not exist/optimistic lock) the specified condition data from the local DB on the server DB |
| 8 | CopyLocal_fromSvr | Function that copies the data that meets specified conditions from Server DB to local DB |
| 9 | ImportLocal_fromSvr | Function that imports the definition and data of the specified table on Server DB to local DB |
| 10 | LockSvrRecords | Function that locks/releases the record that meets specified conditions on Server DB |
| 11 | InsertSvr_FromLocal | Function that inserts records that meet specified conditions from local DB to Server DB |
| 12 | GetCSV_FromSvr | Function that exports the result of SQL execution on Server DB in CSV. |
| 13 | ChgDBConnect | Function that switches the server DB to be connected to |
Use API from Access VBA to define the data communication section with the server database.
The program of the existing Access system is virtually reused and the server linkage section is additionally defined using API.
Exclusive control is performed on the server's shared database so that contradictions in the data is prevented due to data update by multiple users.
The following is a sample code to import necessary data from the server database to the client computer. When the Access form is opened, the record extracted by the "SELECT" statement is imported into the table of the Access file.
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
Me.Requery
The following is a sample to write the data in the client computer to the server database. It rewrites the record(s) in the table ("ORDER") based on the data on the client computer 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
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."
Else
Execution result text. = "Log output failed"
End If
The benefits of using the Access version are as follows:
By using existing Access assets, forms, queries, reports and other existing application sections effectively, you can easily create a Web-enabled system. The data section can also be diverted by converting the table definitions to the server-side (Function to upsize the Database). Therefore, it is possible to drastically shorten development man-hour.
Access technicians only need to develop the Access application for the client computer, so there is no need to learn Java, PHP, etc. to build a new server-side program.
Because updating Access files is done automatically by only uploading from dbSheetClient Development component, there is no risk associated with distribution of updated work, and maintenance and operational costs can be reduced.
Access files are fragile, or the file size can expand rapidly each time a file is overwritten.
In Access version, the latest Access file is always downloaded to the executing computer, so you can avoid such problems.