How to send data from local database to MySQL db on website
in [Help]
|
From: JeffP on 14 Jul 2010 19:39 Hi David Sorry for not answering anybody earlier on this but after posting I had to attend to off-site matters. Anyway, my situation is this - There is an existing Access database in the office. It is used daily for fairly major business activities. There is a hosted website, actually built using Joomla, that has a MySQL database. We want to simply take a subset of data from the Access database and copy it up to the website database. There is no functionality on the website for entering this data, and there never will be. It is data for read only purposes on the website. Like a product list. Once the functionality is developed it will be scheduled to happen out of hours without human input, and maybe manually as I previously suggested. No problems with that. The only thing I am not sure on how to do is actually get the data from the Access database to the hosted website (hosted being the problem). Sounds like you are very familiar with the situation and the XMLHTTP calls are the solution. I just now need to work out how to do that. Thanks for your help Jeff "David W. Fenton" <XXXusenet(a)dfenton.com.invalid> wrote in message news:Xns9DAF9FED9C0B7f99a49ed1d0c49c5bbb2(a)74.209.136.97... > Banana <Banana(a)Republic.com> wrote in > news:4C35F1DF.1060909(a)Republic.com: > >> On 7/8/10 8:31 AM, Bob Alston wrote: >>> Why not fully convert to MySQL? >>> >>> bob >> >> It should be pointed out that MySQL is just a database and doesn't >> have any forms or reports... like SQL Server, so one cannot "fully >> convert to MySQL" from an Access as an application. Sure, you >> could move data from Access to MySQL (or any other RDBMS) but you >> still have to deal with the application aspect that's not provided >> by any RDBMS. >> >> Thus it would make more sense to say "convert to a PHP >> application"*. However, I think the OP already indicate that >> there's a website so there's already a PHP application. There is >> nothing wrong with using Access as a front-end to MySQL, and as >> Karl mentioned, one could use ODBC to connect directly to MySQL >> and thus avoid the need to send a POST request to the PHP >> application. That would certainly be much simpler than writing VBA >> to build the POST request via MSXML library. > > Everyone assumes to be assuming way too much about the environment. > > I happen to be working right now for a client whose inventory is > their Access app (that I built for them in 1997 and have been > updating and enhancing on a regular basis since then), and their > website has a MySQL database behind it. They don't want their > inventory database on their website, because they only put a subset > of their inventory on the website. So, the MySQL database is a slave > of the Access database. > > Now, with normal Linux-based web hosting, you don't get an open port > to connect to the database externally, and that's a good thing! So, > it would be impossible to use ODBC to upload data to the website > database. > > Instead, I have written PHP pages for adding and editing data that I > call via MS XMLHTTP calls. > > And when the MySQL database is not on your local LAN/WAN or on a > website without a port that is publicly accessible from the Internet > (a gaping security hole, of course), then you must do the > interaction exactly as I described unless you want to do it > manually. > > -- > David W. Fenton http://www.dfenton.com/ > usenet at dfenton dot com http://www.dfenton.com/DFA/
From: Bob Alston on 14 Jul 2010 20:18 JeffP wrote: > Hi David > > Sorry for not answering anybody earlier on this but after posting I had to > attend to off-site matters. > > Anyway, my situation is this - > > There is an existing Access database in the office. It is used daily for > fairly major business activities. > > There is a hosted website, actually built using Joomla, that has a MySQL > database. > > We want to simply take a subset of data from the Access database and copy it > up to the website database. There is no functionality on the website for > entering this data, and there never will be. It is data for read only > purposes on the website. Like a product list. > > Once the functionality is developed it will be scheduled to happen out of > hours without human input, and maybe manually as I previously suggested. No > problems with that. > > The only thing I am not sure on how to do is actually get the data from the > Access database to the hosted website (hosted being the problem). > > Sounds like you are very familiar with the situation and the XMLHTTP calls > are the solution. I just now need to work out how to do that. > > Thanks for your help > Jeff > > "David W. Fenton" <XXXusenet(a)dfenton.com.invalid> wrote in message > news:Xns9DAF9FED9C0B7f99a49ed1d0c49c5bbb2(a)74.209.136.97... >> Banana <Banana(a)Republic.com> wrote in >> news:4C35F1DF.1060909(a)Republic.com: >> >>> On 7/8/10 8:31 AM, Bob Alston wrote: >>>> Why not fully convert to MySQL? >>>> >>>> bob >>> It should be pointed out that MySQL is just a database and doesn't >>> have any forms or reports... like SQL Server, so one cannot "fully >>> convert to MySQL" from an Access as an application. Sure, you >>> could move data from Access to MySQL (or any other RDBMS) but you >>> still have to deal with the application aspect that's not provided >>> by any RDBMS. >>> >>> Thus it would make more sense to say "convert to a PHP >>> application"*. However, I think the OP already indicate that >>> there's a website so there's already a PHP application. There is >>> nothing wrong with using Access as a front-end to MySQL, and as >>> Karl mentioned, one could use ODBC to connect directly to MySQL >>> and thus avoid the need to send a POST request to the PHP >>> application. That would certainly be much simpler than writing VBA >>> to build the POST request via MSXML library. >> Everyone assumes to be assuming way too much about the environment. >> >> I happen to be working right now for a client whose inventory is >> their Access app (that I built for them in 1997 and have been >> updating and enhancing on a regular basis since then), and their >> website has a MySQL database behind it. They don't want their >> inventory database on their website, because they only put a subset >> of their inventory on the website. So, the MySQL database is a slave >> of the Access database. >> >> Now, with normal Linux-based web hosting, you don't get an open port >> to connect to the database externally, and that's a good thing! So, >> it would be impossible to use ODBC to upload data to the website >> database. >> >> Instead, I have written PHP pages for adding and editing data that I >> call via MS XMLHTTP calls. >> >> And when the MySQL database is not on your local LAN/WAN or on a >> website without a port that is publicly accessible from the Internet >> (a gaping security hole, of course), then you must do the >> interaction exactly as I described unless you want to do it >> manually. >> >> -- >> David W. Fenton http://www.dfenton.com/ >> usenet at dfenton dot com http://www.dfenton.com/DFA/ > > Why not just use some of the MySQL data loading utilities to delete the Mysql database and load it nightly?? bob
From: David W. Fenton on 15 Jul 2010 19:09
Bob Alston <bobalston9(a)yahoo.com> wrote in news:cts%n.13545$0A5.955(a)newsfe22.iad: > Why not just use some of the MySQL data loading utilities to > delete the Mysql database and load it nightly?? Er, do you know anything about Joomla? Likely the data table involved is part of the larger Joomla data store, so you don't want to do that. On the other hand, you might be onto something if the website offers phpMyAdmin, which makes it pretty easy to truncate a table and load new data from an uploaded text file. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |