Japanese (日本語)

dbSheetClient Access Version

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?

What 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 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:

  1. Use Access as a front end to build a Web-enabled system
  2. 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.

  3. Functions of resource management are fully supported by dbSheetClient server
  4. 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.

  5. Automated distribution of developed Access program (MDB)
  6. 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.

  7. Supports exclusive control for similtaneous access to the server
  8. 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.

Features of development

The following features are for development using dbSheetClient Access version.

  1. Build a Web-enabled system with Access Visual Basic for Applications (VBA) programming language.
  2. A Web-enabled system can be developed by Access VBA programming.

  3. Utilize the existing application section of the Access database (i.e. forms, queries, reports, etc.) effectively.
  4. 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.

  5. The server function is utilized by Web service API, which is called out by VBA.
  6. 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.

  7. The definition file of the Development component for Access is comprised of information about the database and the file path of the MDB file.
  8. 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 Web service API functions

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.

Dim blnStatus As Boolean

The following tables provide a description of the API functions.

Connection with dbSheetClient

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

Database processing

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

Program (Project) development with VBA using API

  1. Use the Web-service API call to achieve linking with the dbSheet server.
  2. Use API from Access VBA to define the data communication section with the server database.

  3. Add definitions for the data linking section with the server to the existing program.
  4. The program of the existing Access system is virtually reused and the server linkage section is additionally defined using API.

  5. Supports simultaneous execution control when updating data by multiple users.
  6. 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.

Example of programs using VBA

  1. To open the form: Example of VBA code for retrieving data from the server DB
  2. 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

  3. To update data: Example of VBA to update server DB
  4. 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

  5. To output log to Server: Example of VBA code
  6. 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

    Advantages of Access Version

    The benefits of using the Access version are as follows:

    1. You can easily convert existing Access assets into a Web-enabled system.
    2. 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.

    3. Access technicians do not need to master new programming languages.
    4. 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.

    5. Through automation of Access program updates, operational costs can be reduced.
    6. 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.

    7. It is released from the problem of becoming fragile and heavy in ACCDB (MDB).
    8. 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.