MS SQL or MS Access vs. MS Access

 Kform Project Manager Version 2.5

DAO was used to access data and OLE Provider was used to establish a connection between the Presentation Layer (client) and Database Layer (server).  Under the two-tier architecture, Microsoft Access was the simple and low-cost database solution.

 Kform Project Manager 2007

The logic necessary to access data in Kform Project Manager 2007 is in a separate layer of data access logic components. Taking this approach it centralizes data access functionality and makes it easier to configure and maintain.The Kform Data Access Layer has a wrapper class which has a generic function for getting the queries as a parameter coming from the client over the network through the Web Service Wrapper. This function cannot distinguish between a query and a stored procedure. This function takes the parameters and based upon the connection already selected and opened by client (using the application “Login.exe”) selects the right data access path, currently the two paths available.

1. Microsoft Access
2. MS SQL Server
   

The Kform query execution process for every database has been separated. The reason for doing this is the availability of different features in different databases. For instance, Microsoft Access does not support stored procedures where as MS SQL Server does and because of this difference different data commands have to be used in VB to execute the query and also because of syntax difference in different queries between Access and SQL Server. Due to the described issue the execution process has been separated.  These functions have been written in a separate module and placed in the form of a compiled DLL in the Data Access Layer, increasing the extendibility of the Data Access Layer. In the future, if any other database such as Oracle is required by a client as a Database only DLL has to be written and compiled independently and placed on the server.  The new DLL will process the queries and stored procedures written for Oracle.  The purpose behind this approach is plugging in a new module the whole architecture is not altered and disturbed.