How to send data from local database to MySQL db on website

#11

Bob Alston <bobalston9 (AT) yahoo (DOT) com> wrote in
news:WomZn.7395$Lj2.3278 (AT) newsfe05 (DOT) iad:

Quote:

I meant to say convert the database portion of Access, the Jet
database part, to Mysql. That way you can still use the Access
forms, queries and reports just have the data all in one place.

This is generally not possible with a MySQL database on most web
hosts, which don't expose the database on an external port (for
obvious reasons).

It's also not necessarily desirable, as you may not want the two
databases to have the same data in them.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

#12

Banana <Banana (AT) Republic (DOT) com> wrote in
news:4C35F383.3080106 (AT) Republic (DOT) com:

Quote:

On 7/8/10 8:46 AM, Bob Alston wrote:
I meant to say convert the database portion of Access, the Jet
database part, to Mysql. That way you can still use the Access
forms, queries and reports just have the data all in one place.

Ahh, I was under the impression that the OP already had all data
in MySQL, but yes, if that is not the case, then that's even worse
as there'll be synchronization issues that can be best avoided
although.

Have any of you ever worked with clients who have an Access database
and a MySQL-driven website? It really doesn't sound like it.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

#13

Bob Alston wrote:

Quote:

David W. Fenton wrote:

Have any of you ever worked with clients who have an Access database
and a MySQL-driven website? It really doesn't sound like it.
Actually, no. I have only used MySQL for PHP based web apps. I have
used Access/jet in windows apps. Have you? do you have experience that
would suggest against doing this?

Bob
For anyone interested, this is a link to a paper on linking Access front

ends to MySQL.

http://www.mysql.com/why-mysql/white...sual_guide.php

Bob

#14

David W. Fenton wrote:

Quote:

Rick Brandt <rickbrandt2 (AT) hotmail (DOT) com> wrote in
news:i14ccv$7fr$1 (AT) news (DOT) eternal-september.org:

JeffP wrote:

Can't seem to find a start on this.

I have a hosted website with a MySQL database.

Locally, on my PC, I have an Access database.

I would love to be able to click a button and have data sent to
the MySQL database on the website.

Assumedly, the database needs to send this like a form data post
from a web page and have a PHP page on the website that receives
it and updates the MySQL database. But how do you get Access to
mimic data submitted from a website form?

All help appreciated.

You can use the Windows MSXML dll to make HTTPRequests from VBA
code.

But without something on the server side to consume those, what
would you be accomplishing? That is, there has to be a script to
handle the HTTPRequests you are sending to the website.

If there is currently a web page that can post data to the database it would
be doing so via HTTP would it not? I am assuming that the person who is
creating the Access app is also familiar with the design of the web site and
would therefore be able to construct the same POST from Access that they do
from the web page.

#15

One of the easiest ways to achieve database inserts / updates /
deletes is to have a web service on the website that exposes those
methods. What you also need to be aware of is accidental or deliberate
tampering via usage of that web service. There are many tools that
talk to web services and sync data between databases (Pentaho Data
Integration for one - my preferred as it is reliable, effective, free,
and has great community support and professional support if you need
it).

If it were me attempting to achieve this I would plan a web service
and the methods it requires as well as a 'holding' area for incoming
data - ie/ dont dump it directly to the active tables. Security needs
to be designed into the web service such that not just anybody can
access it. Data is then pulled and pushed to the website holding
tables in batches which need to be numbered. You also need a secure
page to allow approval of these batches of data to the active tables.
It sounds like a lot of work, but it really isnt nearly as much as it
sounds.

If you want to implement a really standardised approach that makes
maintenance of teh system relatively easy then I would suggest to use
an XML-RPC based web service. If you want to push and pull from Access
you could, and do it securely too. If you want to use third party
tools like the Pentaho tool you can do that as well. I would probably
get a professional web services designer to build the service and its
security, then if you want to make changes it is (if properly built)
simply a matter of adding / editing / removing the 'methods' in the
XML-RPC document (I think it is called a WSDL file), and adding the
database commands to the server side to receive those methods (and
their arguments).

There is a great example of this at the upc database
(www.upcdatabase.com). You can see the stages of development they have
gone through in building a more and more sophisticated web service.
What they have is pretty much what you want to achieve - although they
are still implementing their 'push' part of the service (they seems to
progress slowly, I am guessing it is a personal / private project).
You will find there is loads of data on XML-RPC on the web of course,
but if you havent done this sort of thing before then just pay someone
to craft the code you need for the server, and maybe even an online
service editor so you can just play with the service settings via a
secure web page.

I hope this helps

Cheers

The Frog

#16

On 7/8/10 1:06 PM, David W. Fenton wrote:

Quote:

Have any of you ever worked with clients who have an Access database
and a MySQL-driven website? It really doesn't sound like it.

In a past project I did, there was access to a MySQL-driven website via
secure connection so there was no need for exposing a port via WAN and
thus had ODBC connectivity available. I'd have thought several hosting
accounts likewise provide a SSL or SSH access for certain type of
accounts and thus consider ODBC connectivity a non-issue except in case
of most basic and bare-bone hosted account.

There are undoubtedly advantages to using POST requests out of an Access
application, just as there are advantages to using ODBC linked tables
inside an Access application with a secure line. At least the OP now
know about all possible choices available.

#17

Bob Alston <bobalston9 (AT) yahoo (DOT) com> wrote in
news:W0qZn.15240$4B7.13492 (AT) newsfe16 (DOT) iad:

Quote:

David W. Fenton wrote:

Have any of you ever worked with clients who have an Access
database and a MySQL-driven website? It really doesn't sound like
it.

Actually, no. I have only used MySQL for PHP based web apps. I
have used Access/jet in windows apps. Have you?

I've done both, but more use of MySQL via PHP on a website than from
Access.

Quote:

do you have experience that
would suggest against doing this?

Against doing what?

I am not proposing ODBC linking at all, since my bet is that it's
not possible. I'm proposing what I've already got experience with,
which is programming it server-side in PHP (ASP, or whatever your
server-side scripting language is), and using MS XMLHTTP to call
those scripts from Access.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

#18

Bob Alston <bobalston9 (AT) yahoo (DOT) com> wrote in
news:2prZn.10059$0A5.7994 (AT) newsfe22 (DOT) iad:

Quote:

Bob Alston wrote:
David W. Fenton wrote:

Have any of you ever worked with clients who have an Access
database and a MySQL-driven website? It really doesn't sound
like it.
Actually, no. I have only used MySQL for PHP based web apps. I
have used Access/jet in windows apps. Have you? do you have
experience that would suggest against doing this?

Bob
For anyone interested, this is a link to a paper on linking Access
front ends to MySQL.

http://www.mysql.com/why-mysql/white...access_visual_
guide.php

I'm not going to set up an account to read that.

Is it just talking about ODBC links? If so, it's not really relevant
to my understanding of the scenario involved in this discussion.

But I could be wrong about the inapplicability of ODBC, as the
original poster does not seem to have returned to answer what the
situation is.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

#19

Banana <Banana (AT) Republic (DOT) com> wrote in
news:4C3788AE.7060303 (AT) Republic (DOT) com:

Quote:

On 7/8/10 1:06 PM, David W. Fenton wrote:
Have any of you ever worked with clients who have an Access
database and a MySQL-driven website? It really doesn't sound like
it.

In a past project I did, there was access to a MySQL-driven
website via secure connection so there was no need for exposing a
port via WAN and thus had ODBC connectivity available. I'd have
thought several hosting accounts likewise provide a SSL or SSH
access for certain type of accounts and thus consider ODBC
connectivity a non-issue except in case of most basic and
bare-bone hosted account.

I think that for your standard hosting company providing inexpensive
LAMP tools, that is the exception rather than the rule.

Quote:

There are undoubtedly advantages to using POST requests out of an
Access application, just as there are advantages to using ODBC
linked tables inside an Access application with a secure line. At
least the OP now know about all possible choices available.

I'd think ODBC would be loads easier, though whether or not it was
practical would depend on the quality/speed of the connection.

The suggestion to use the MySQL database on the website as the
principle database is, I think, quite misguided for the most common
scenarios like this. When there are already two independent
databases, it's very unlikely they have identical structures and
contain the same data.

At least, in my experience...

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

#20

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 (AT) dfenton (DOT) com.invalid> wrote

Quote:

Banana <Banana (AT) Republic (DOT) com> wrote in
news:4C35F1DF.1060909 (AT) Republic (DOT) 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/

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

How to send data from local database to MySQL db on website

comp.databases.ms-access comp.databases.ms-access

Re: How to send data from local database to MySQL db on website - 07-08-2010 , 04:05 PM

Re: How to send data from local database to MySQL db on website - 07-08-2010 , 04:06 PM

Re: How to send data from local database to MySQL db on website - 07-08-2010 , 05:27 PM

Re: How to send data from local database to MySQL db on website - 07-09-2010 , 01:50 AM

Re: How to send data from local database to MySQL db on website - 07-09-2010 , 03:57 AM

Re: How to send data from local database to MySQL db on website - 07-09-2010 , 04:38 PM

Re: How to send data from local database to MySQL db on website - 07-09-2010 , 07:04 PM

Re: How to send data from local database to MySQL db on website - 07-09-2010 , 07:05 PM

Re: How to send data from local database to MySQL db on website - 07-09-2010 , 07:08 PM

Re: How to send data from local database to MySQL db on website - 07-14-2010 , 07:39 PM