Get MySQL data from the web via access

MySQL is currently the most popular network database, including the following reasons:


running Fast


Free


can run on Linux and some other platforms


can be perfectly integrated with Apache (a popular web server)


can be perfectly combined with PHP


MySQL has an ODBC interface. If you are working with a Web server or MySQL server and want to associate data with a desktop application, it is a good way to use the ODBC interface. However, if you are not operating on the server, access to the MySQL port is likely to be invalid, at this time can only find other ways to manipulate your data.




The problems of


The problems mentioned by
are for the client. For security reasons, the client program typically chooses the network representative through a single host, avoiding frequent logons to its own system on the network. Many hosts can provide the right conditions and MySQL, scalable frontpage,cgi and other site-making tools. They can also meet hardware and software matching problems. This unique host can even provide a variety of tools to help you add or remove your MySQL database, but it is only in the basic MySQL repository format, which cannot be read directly into Access or other desktop programs.





the client program does not want to install MySQL on its own unit server, you can pass data through my program, just run MySQL on my machine. I can manually retrieve data from the host repository. This process involves loading data into the database, transferring it to access through ODBC, and then sending the database content to the client. I don't mind the work done, but we should simplify some unnecessary work rather than relying too much on manual manipulation.





let me explain the above process. First the host on the Web page to display data storage, through simple code and registration I get the data of the database, registration is due to security considerations. Now, I'm using Microsoft's Web browser ActiveX control to directly manipulate the Access database, which allows users to easily access the MySQL data through the registration process, and then pass the data to the corresponding table in the Access database. As long as the data is in an Access database, users can pass data to an ERP system or other proprietary systems using ODBC or their usual methods.





Microsoft Access Database

The
Access database is fairly simple, consisting of data tables that enumerate the data you want to add. In this section, I use the datasheet named Sales_data, which is made up of car data from a virtual website. Again, the data table should be exactly the same as the MySQL data table on the host, both in name and structure. In fact, we first build a datasheet in Access (Figure A), and then transfer the data to MySQL on the local server via ODBC.





Figure A










The
Access application opens with the Web Page Control Panel form open. In my routines, the program was registered with PHP on my server.





Figure B














uses an ActiveX control named WebBrowser1, which executes the following code:





Private Sub Form_Load ()


Webbrowser1.navigate2 http://192.168.192.1/~stew/mysql/


End Sub





Figure C














MySQL screen shot




The other parts of the
form include two buttons, and the first button (pop-up control Panel) displays the MySQL screen in the following code (Figure C):





Private Sub Loadcontrolpanel_click ()


Webbrowser1.navigate2 http://192.168.192.1/~stew/mysql/mysql_dump.php


End Sub





the second button to get the data, which is the main part of the code.


then you'll see a drop-down list of Access databases, and there's already data in that list. In this example, the list is named Sales_data.





Also, check boxes let you choose whether to delete old data before adding data, you can do some useful work here, but these operations must be legal.





Finally, the two text interfaces contain the unit name and the MySQL database name.





Summary


above is the whole process. The user must run the database query on the host through the network interface. When the MySQL repository is displayed in the form browser (Figure D), click the Fetch Data button to get the data and store it in the access datasheet. The program also detects whether the data store is based on a certain format of MySQL and ensures that the database and datasheet are correct. If these rules are met, the Insert command in the program is used to add data to the destination datasheet, and a message dialog box appears to display the process. Returns to the form if an error occurs. Listing A contains the complete code.





Figure D














MySQL Screen Dump