Create two VB apps transporting data between a MS-Access and a MySQL database using web services.

Create two VB apps transporting data between a MS-Access and a MySQL database using web services.? The applications need to run as a service, one on a central server with MySQL, the other to run as a service on remote computers interfacing with MS-Access tables.? The remote installs will use Web Services to request missing information from the central application? & server. ## Deliverables Project Overview: This project includes three MS-Access tables for reference.? Table [login to view URL] represents a data structure at a centralized data repository except that the database will be MySQL rather than MS-Access.? Tables [login to view URL] and [login to view URL] are examples of two MS-Access tables that will be installed at two (of potentially many) remote sites.? The overall project will be for one centralized data repository running MySQL and any number of remote sites all running MS-Access tables. The VB 2008 application to be installed at remote sites is a continuously running Windows service application that looks for missing data in a local (remote) MS-Access tables, initiates a web services call to the centralized data repository to retrieve any missing data, then "sleeps" for a specified time until the next processing cycle. The VB2008 application to be installed at the centralized data site is a web service hosting application installed as a Windows service that awaits web service requests from remote applications and delivers requested data back to the remote requesting applications.? This centralized application authenticates requesting remote queries against an internal authorized users table, and also creates new records in the internal master table for requests for data that are not yet part of the master table. An interesting feature of this project is that the column names of the data requested is dynamic - applications on both centralized and remote sides must be able to work with tables regardless of the column names. ? Central Processing Site Files: [login to view URL] contains sample data for the centralized processing center, but is representative only of what will be running on MySQL in actual production. ? You must first create the tables and columns in your development environment using MySQL 5.1.? They include: Permissions : Authorized User? - contains the user IDs of remote users authorized to access the master system for data retrieval. Permissions : Expires? - contains the last date on which access the master system for data retrieval is permitted for this remote user. Permissions : Password? - contains the password of the authorized user for access the central system for data retrieval. Permissions : ID? - Auto-Incrementing table Primary Key MasterFile : SomeField1 (thru ...n) - Text fields containing the lookup and response data? requested by the remote applications. MasterFile : Context - Text field containing a value as part of the query for data from the remote. MasterFile : RequestedField - In the event that a requested for data is not found or in the event of an error situation, the centralized site will INSERT a new record and insert data into this column (more on this in the logic section below). MasterFile : Requests - is a counter field that is incremented each time any remote asks for data from this record. MasterFile : ID? - Auto-Incrementing table Primary Key. ? Remote Site Files: NFRemote1 and [login to view URL] are sample data provided are examples or remote tables which will always be MS-Access 7.0 as defined here: Permissions : RemoteUserID? - contains the user ID of this remote site used to submit transaction requests to the central processing center. Permissions : MasterPassword? - contains the password for accessing the central processing site. Permissions : SleepFor? - contains a value representing the number of seconds for the process to sleep between processing cycles. Permissions : ID? - Auto-Incrementing table Primary Key. RemoteFile : SomeField1 (thru ...n) - Text fields containing values entered by other applications or supplied by this remote application.? Each remote site RemoteFile table will have varying numbers of columns.? RemoteFile : Context - Text field containing a value as part of the query for data from the remote.? External applications may enter a value in this field when creating a record.? RemoteFile :ID ? - Auto-Incrementing table Primary Key. ? Processing Logic - Remote Sites: The remote site application is to be installed as a Windows service with a single form UI that can be activated as desired to monitor the status of the application.? Editing of the contents of the RemoteFile and Permissions tables is done by other applications that are not part of this project. ? There can be any number of columns in the RemoteFile table (usually 2-5) and they can be named anything except "Context" or "ID".? External applications will add new records to the RemoteFile table, inserting one text value into any one of the "SomeField..." columns.? The external application may also insert a text value into the "Context" field.? The standard MS-Access Auto-Numbering feature will increment the "ID" field. The objective of this application is to search the RemoteFile table for any blank fields in any of the "SomeField..." columns, and if found, for each blank field, query the Centralized Processing application for values to be inserted into the blank field by submitting a WS query containing (in addition to the authentication fields) (1) the Column Name containing a value (the first column name if more than one happens to contain a value), (2) the field value of the Column Name being submitted as a query, (3) the field value from the "Context" field (even if blank), and the (4) Column name of the blank field for which this individual query is seeking a response.? The WS requests will use the User ID and the password from the local (remote) "Permissions" table for authentication by the central processing service. If a match exists, the Central Processing Web Service will respond with requested value (see logic below).? If returned, the requested text will be inserted into the blank field being processed.? In the case the requested data is not yet available, a blank response will be returned and processing will continue to the next blank field, either in the record currently being worked, or in the next record.? In the case that the login credentials are invalid or expired a message will be returned to the Remote Application triggering the application to stop processing and display an "authentication failure" message on the monitoring form. A process monitoring form must also be provided that can optionally be displayed from the remote processing application which will indicate the following application running parameters: "Authentication Status:" - displays either "Authentication OK" or "Authentication Failure" or "Cannot Locate Central Processing" messages depending on the authentication response or non-response from the central processing host. ? "Run Time:" the length of time since service was last launched (updated at the end of each processing cycle) ? "Total Run Queries:" - the number of queries submitted since the application/service was last launched ? " Cycles:" - the number of queries cycles run since the application/service was last launched ? "Average Response Time:" - the time amount of time it took for the most recent cycle to run divided by the number of queries submitted during the last cycle. ? "Blanks Last Cycle:" - the number of queries from the last completed processing cycle (between sleep cycles) that were returned as blank responses (matches not found at central site) ? Processing Logic - Central Site: The central site application is to be installed as a Windows service with a single form UI that can be activated as desired to monitor the status of the application.? Editing of the contents of the MasterFile and Permissions tables is done by external applications and is not part of this project. The installation of the this application presumes that the MySQL database is separately installed and configured.? This application must contain a means of storing information required to connect to the MySQL database.? There can be any number of columns in the MasterFile table (expect 4-12) and they can be named anything except for "Context", "ID", "RequestedField", or "Requests".? The objective of this application is to respond to requests from remote applications, returning values from requested matching columns. ? The processing at the Central Site must be multi-threaded, in that it must be able to process multiple remote queries asynchronously. ? Remote application installations will submit Web Service queries containing the (1) LoginID, (2) password, (3) Query Column Name, (4) Query Column text value, and (5) Context field value (possibly blank) and (6) the name of the column from which it is requesting data be returned.? ? The central processing logic will validate the LoginID and password against the NFMaster Permissions table, including the "Expires" field and will respond only if the LoginID and ID are correct and the "Expires" date has not expired. If deemed to be a valid requestor, the application will look up the record in the MasterFile, querying the specified data column with the provided data value plus querying the "Context" column with the provided Context value, looking to retrieve the value of the field in the column requested from the remote. If there is no matching record found, the centralized application will first INSERT a new record into the MasterFile table, inserting the Query value into the appropriate Query Column field, inserting the Context value into the Context field, inserting the Name of Column being requested into the "RequestedField" column, and inserting a 1 into the "Requests" counter.? It will then respond to the remote Web Service query with a blank response, indicating it could not find a match. In the event that either the look-up column name or the requested-data column name submitted from the remote user is NOT one of the existing columns in the MasterFile, the centralized application will INSERT a new record into the MasterFile table, leaving all columns blank except inserting text into the "RequestedField" column identifying the requesting user UserID, the LookUp Column name, and the requested data column name.? It will then respond to the remote with a blank response, indicating it could not find a match. If a valid match IS found but the requested field value is blank, the centralized application will NOT increment the "Requests" counter, but will issue a return Web Service response containing no value, indicating that it count not find a match (actually could not return a value). If a valid match IS found and the requested column contains a value, the centralized application will extract the value of the requested field from the MasterFile requested column, increment the "Requests" counter, then return the value to the requesting remote application. A process monitoring form must also be provided that can optionally be displayed from the Centralized Processing application which will indicate the following application running parameters: "Run Time:" the length of time since service was last launched ? "Total Run Queries:" - the number of queries processed since the application/service was last launched