About Access Version
It is a development support tool that is used to build a Web-enabled system using Microsoft Access which is one of Microsoft Office products. Generally speaking of a Web-enabled system, you think of a Web application that works with a Web browser, but dbSheetClient is a rich client type Web-enabled system construction tool that adopts Microsoft Access on the user's front screen.
In addition to Runtime component used by users, dbSheetClient consists of Development component for developing business systems and Server component for providing server functions.
Windows (OS) is Vista / 7 / 8.1 / 10. Microsoft Access 2007 or later, SQL Server 2008 or later, Oracle 9i or later.
For more details, please refer to dbSheetClient function overview and operating environment on the Newcom website.
Microsoft Access is provided with a form for data input, a table for storing data, and a report function for printing, which makes it easy to construct a system. On the other hand, there is no function as database server, and simultaneous connection from multiple client computers causes data inconsistency and response to be extremely slow, and it is not suitable for simultaneous use from many client computers. Therefore, dbSheetClient adopts SQL Server (or Oracle) which supports simultaneous connection from multiple client computers and can handle large capacity of data without deteriorating response.
Yes. It is possible.
As a flow of development, it is necessary to first convert existing Access system into standalone operation.
If link tables exist, convert them to a local table and reconstruct them as one complete system. Then use the API to describe data communication processing between the client (Access) and the server database in VBA. I think that there are objects (forms, queries, macros, reports, modules, tables) in the existing Access file (MDB or ACCDB), but you can reuse these program assets.
dbSheetClient develops, manages and operates individual business applications as projects.
When you start the dbSheetClient Runtime component and login with user ID and password, the list of projects available to that user is displayed. When you select the target project, Access starts up, copies server data to the local table when the form is opened and writes to the server at the time of record update (Form_AfterUpdate).
No, it is not necessary.
If you are an experienced technician with experience developing in Access development language VBA (Visual Basic for Applications), you can develop it without trouble. When building Web-enabled systems usually you need to learn server programming language such as Java, ASP.NET, PHP, but it is not necessary for dbSheetClient. Since dbSheetClient has a Server component and server management functions such as user authentication, database management, operation log management, privilege management, therefore developers can use Access VBA for development.
By reference setting the COM component (dbsAcNetClass.dll) provided by Newcom Inc. from the Access VBA screen, data communication is performed between the client and server. Specifically, it communicates between the client's DLL and the Web service provided by the dbSheetClient Server. About 40 types of API (Application Programming Interface) are provided in this DLL. As an example, to copy the latest data from the server to the Access table of the client computer, use the API CopyLocal_fromSvr. When copying data edited on the client-side to the server-side, describe API CopySvr_fromLocal with parameters. In addition, since we have all kinds of functions necessary for constructing Web-enabled system, we can proceed with system development with reference to "API reference manual".
Yes, it is necessary for the developer / user to install DLL on each computer.
Specifically, execute RegAccessDll2.exe in Access version Suite folder with Administrator authority.
Since it is registered in the Windows registry as a COM component (ActiveX DLL), reference setting from Access VBA is now possible.
When trying to load the latest data of the server database to the client computer, it requests from the client computer to the server database using an SQL statement such as SELECT * FROM table name WHERE ~. Therefore, it is possible to obtain the results narrowed down from the target table. This is an effective way to reduce the amount of data flowing through the network. Next, since dbSheetClient adopts non-connection type as the connection method to the database, it connects only at the timing requested from a client to the server. This is an effective way to prevent a response from being dropped when multiple users access MDB at the same time. In addition, by incorporating the logic of compressed communication between client and server, it helps to further reduce network traffic.
The developer uploads the Access file (MDB) to the server using the Upload Access File function from the Edit Definition screen of the Development component. When the end user starts the Runtime component and selects the project after login, it downloads the latest Access file (MDB) from the server. However, the end user does not download the Access file every time it is started but places it in the local cache. Therefore, download it from the server only when the updated date of the Access file (MDB) placed on the server is newer than the one on your computer.
Q10:When the end user selects the target project after logging in, it means that the Access file (MDB) is downloaded from the server and Microsoft Access starts up. However, when downloading the file every time it is executed, it will not only put pressure on the network traffic but also will adversely affect the response?
As a basic idea of dbSheetClient, it is assumed that the data entered by the user is on the server database only and Access file (MDB) data is empty. In other words, downloading from the server is only part of the application of Access and data is stored in the server database. Also, it does not download the Access file (MDB) every time the user executes the project. Even if it takes a while for the first time (just after the latest Access file (MDB) has been uploaded from the developer's computer), the Access file (MDB) in the cache folder will open after the second time, so that its startup time will be shortened.
Q12:The client computer is assumed to have various components of Microsoft Access installed. In such internal environment, what component of the Access file should be distributed to the Runtime component?
Yes. It is possible.
Since ADP performs data processing on the SQL Server-side, there was a merit that the load on the network can be reduced and a system with good performance can be constructed. For companies with many Access-ADP assets, I think that there are sections that are confused as to how best to improve and continue the system.
1. System configuration of Access-ADP
* Features of Access-ADP are as follows:
1. The client-side file format is ADP.
2. There are no tables or queries on the client-side.
3. Compared with the link table, the processing speed is fast (minimum traffic)
4. To extract and refer to data, use the view of SQL Server.
5. We use stored procedures for addition, update and deletion.
2. System configuration of dbSheetClient
* Features of dbSheetClient are as follows:
1. The front-end file format is MDB (ACCDB).
2. Secure security by login authentication by AP server.
3. Privilege management by group is possible.
4. Central management of user operation log on the server.
5. Multiple server databases can be switched from the local MDB.
6. It has simultaneous execution control (exclusive control) function when updating data by multiple users.
In dbSheetClient 2015, an API ExecQuerySvr that causes the server database to directly execute native SQL and receive the result, an API that executes a stored procedure and imports the result into a local temporary table: ImportSvrProcedure, or alternatively, even if you do not locally have a table, we have a rich set of functions such as GetCSV_fromSvr API that takes SQL results into variables, so we can make ADP dbSheetClient without much development effort.
Q15:I want to convert an existing Access system to a Web-enabled system, but there is a table's primary key with an "AutoNumber type". If multiple users try to add new records, doesn't the duplicated value of the primary key fail to register on the server?
Consider a sample case where Mr. A and Mr. B additionally register a new customer almost at the same time to the customer master of the server database. First, when Access's Customer Master form is opened, 100 customer records registered in the server database are imported into Access. When Mr. A and Mr. B try to additionally register a new customer on the form, since the primary key ("customer ID") is AutoNumber type, both A and B have [customer ID = 101] and a new customer is registered.
When the information of the two customers is reflected on the server database using the API ("CopySvr_fromLocal") together with the server update button, the primary key (customer ID) of the server database is AutoNumber type, so the operation is slight, Mr. A is registered earlier by [customer ID = 101], and Mr. B is registered with [customer ID = 102].
That is, Mr. B's new customer ID matches between the server database and the client computer (Access), whereas Mr. B's new customer ID does not match. Therefore, both new customer records registered by two people are registered in the server database.
However, inconsistency will occur in the data. In order to avoid this problem, dbSheetClient recommends the following idea. First, if the primary key of the Access table is AutoNumber type, basically change it to numeric type (long integer type). The primary key of the server database is also set to int type accordingly. Next, just before the user tries to register a new record, it obtains the maximum number of automatic number assignment ("customer ID") + 1) by the server. Specifically, a table MaxValueManager that manages the maximum value of the primary key ("customer ID") of the target table is added to the server database beforehand and then the maximum value + 1 is set as the API: GetIncrementID Paste it on the Form's Customer ID label in the Before_Insert event of the form. After that, we will perform normal local storage ⇒ server update processing.
Since dbSheetClient performs concurrency control for updating by multiple users, the data that was updated first will be reflected in the server database. Consider, for example, a case where Mr. A and Mr. B correct the above customer master. At the time of form opening, customer master data is copied locally from the server database. Then two people will modify the record with customer ID = 48. Mr. A first updates the server database. ("CopySvr_fromLocal"), then when Mr. B tries to update, a warning is displayed saying Update was not possible because another user updated the record. In this case, Mr. B needs to locate the latest data of the server locally. This method is concurrency control by optimistic locking, but if there are many items on the form, all contents modified by Mr. B will be reset.
In order to avoid this, as another means, A simultaneous execution control with pessimistic locking that Mr. A locked the target data just before starting modification and Mr. B only refers to the data, API It can be realized by using.
As a prerequisite for simultaneous execution control in dbSheetClient, add 5 DBS control fields to all tables on the MDB side and server database side, all records. The names of the DBS control fields are (1) DBS_STATUS (record state), (2) DBS_CREATE_USER (record creator ID), (3) DBS_CREATE_DATE (record creation date and time), (4) DBS_UPDATE_USER (record updater ID), and (5) DSB_UPDATE_DATE (record update date and time) It is string type.
Yes, the content executed by the user is basically accumulated in the server database as an operation log.
For default log includes login, confirm existence of the file, download file, start execution, end execution, etc. However, errors generated when using the API are automatically saved as logs. Also, if you incorporate the API SetSvrUserLog, you can save the log (log title and log item) at random timing. Run the Windows application called Server Log Viewer when browsing the logs accumulated on the server. Since you can narrow down the search by log period, user ID, project name, processing outline, etc., the System Administrator can grasp and manage contents such as who, when, which project was launched and what kind of processing was executed in a unified way.
Q18:When developing a business application in Microsoft Access, you probably use a form consisting of header (parent form) and item (child form), but in dbSheetClient you use API When trying to reflect it in the server database, how should it be implemented?
Consider an Order Management System that manages the order of a certain item, the header section of the form is composed of the table "Order" and the detail section is composed of the table "Order content".
The primary key of "Order" table is order number, and that of "Order content" table is order number and order content. In other words, the header and item are linked by order number.
At this point, the data will be downloaded from the server database to the local tables "Order" and "Order Details" along with the opening of the Order form. For an order record on a form, the user modifies section of the header and adds two items to the statement. In this case, use the event of Update Server button in the header form and write it in VBA as follows. (Error handling is omitted)
Private Sub btn Server Update _Click
Dim blnStatus As Boolean
'Reflect the data changes in the local table.
'Update header section with exclusive control with optimistic locking.
blnStatus = dbsCls.CopySvr_fromLocal ("order", "Where order number = '" & Me. Order number & "', False)
'If successful, we will replace the item' order content' linked to the 'order number' of the form.
If blnStatus = True Then
blnStatus = dbsCls.ReplaceSvr_fromLocal ("Order content", "Where order number = '" & Me. Order number &' '")
If blnStatus = True Then
MsgBox "Server update completed", vbOkOnly + vbInformation, "Update"
MsgBox "Update failed", vbOkOnly + vbExclamation, "Update"
Yes, it is feasible with dbSheetClient.
About 1,500 users are connected to one server and more than 30 projects are in operation. One of the features of dbSheetClient is that business logic is often handled by client computers, therefore not much load on dbSheetClient Server (Application Server). As a processing example of dbSheetClient Runtime component, after editing various data with VBA, query, macro etc. from the form in Access, confirm data is reflected on (exclusive control) to the server database.
In other words, the dbSheetClient server handles the complicated data processing on each client computer side and the processing for storing the definite data in the server database. This is because a general Web application that runs on a Web browser concentrates processing on one Web server, load is well dispersed (also on the client-side), therefore it will be possible to construct a well-balanced Web-enabled system as a whole.
Q20:Is it possible to develop Web-enabled systems from Microsoft Office products (Excel, Word, PowerPoint, etc) other than Microsoft Access and Windows Desktop Applications using COM component ("dbsAcNetClass.dll")?