Prev: After Delete Confirm problem
Next: Simple Conditional Macro
From: The Frog on 9 Jul 2010 03:57
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
From: Banana on 9 Jul 2010 16:38
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.

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.
From: David W. Fenton on 9 Jul 2010 19:04
Bob Alston <bobalston9(a)yahoo.com> wrote in
news:W0qZn.15240$4B7.13492(a)newsfe16.iad:

> 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.

> 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/
From: David W. Fenton on 9 Jul 2010 19:05
Bob Alston <bobalston9(a)yahoo.com> wrote in
news:2prZn.10059$0A5.7994(a)newsfe22.iad:

> 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-papers/mysql_wp_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/
From: David W. Fenton on 9 Jul 2010 19:08
Banana <Banana(a)Republic.com> wrote in
news:4C3788AE.7060303(a)Republic.com:

> 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.

> 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/
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: After Delete Confirm problem
Next: Simple Conditional Macro