Re: Re: Need info, books, articles,code, etc: Access VBA to XML to/from MySQL via HTTP



Hello, Lauren Wilson.

I hate to rush you while you're researching the answers to the questions I
asked, but people are growing impatient. Okay, only one is, but I suspect
that there are others who have read the posts in this thread and have
silently been waiting for the answer to the same question, "Can I use VBA to
read, update, add and delete records located in tables in a MySQL database
on a Web server on the Internet directly from my desktop Access application,
without building a Web application?"

The answer is: "Of course you can, provided you have the know-how and
permissions to configure your own workstation and the Web server correctly."
But why would you? Web applications can easily access Web-based databases,
which is why there are so many job opportunities for Web developers with
database experience.

But since some Access developers don't yet have Web application development
experience, but are at the mercy of managements that have a habit of putting
the cart before the horse, they're tasked with building an Access desktop
application that connects to a backend on a Web server, something that
Access wasn't designed to do. One solution is to make the workstation act
as if it's connected to the Web server via a network, which enables Access
to connect to a database on that Web server using ordinary ODBC protocols
and drivers. The speed performance will only be as good as the upload and
download speeds of your Internet connection, so don't expect the same
results you'd see on your LAN.

A System Administrator needs to configure the computers to use port
forwarding to build a secure tunnel between your workstation and the Web
server. To do this, the Web server needs a secure shell server running, and
the workstation needs a secure shell client running in order to enable the
secure tunnel to connect the computers. Most Linux distributions have
secure shell servers installed by default, but the Web server needs to have
the secure shell server daemon start the process so that it's running when
you need it. Many Web hosts provide secure shells that allow customers to
connect to their Web space to upload/download files, so this may not even be
an issue for those using Linux hosting. However, Windows Servers don't have
secure shell servers installed by default, so one would have to be installed
before you could try to tunnel to it from your own workstation if your Web
host is only providing Windows Servers for their Web servers. An
alternative is to use a Linux computer networked to the Windows Server to
act as a secure shell proxy.

To configure your workstation, if you don't have a MyODBC driver already
installed on your workstation, then download it from the following Web page
and install it:

http://dev.mysql.com/downloads

If you don't have a secure shell client and administration tools installed
on your workstation, then download PuTTY and Plink and install them. Please
see the following Web page for these free downloads:

http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html

A System Administrator needs to configure these applications for port
forwarding with a secure tunnel from your workstation. If you already have
MySQL installed on your workstation, then configure a port other than 3306
for the port forwarding. Otherwise, the MySQL database on your workstation
will never receive future requests for database access if it's configured to
listen on the default port 3306.

Once the port forwarding is set up, open the secure tunnel and then use ODBC
to connect to the MySQL database. You can have separate VBA procedures that
use Plink to open and close the tunnel, so that you can do this at a push of
a button on a form. In a VBA procedure, use the connection string listed on
the following Web page to connect to your remote MySQL database:

http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForMySQL

Use VBA for the Recordsets to read, update, add, or delete records in the
tables. Once your Access database application has finished its data
manipulation and data transfers, close the secure tunnel.

Some of the problems you'll likely run into involve using a commercial Web
host for your Web server, because you won't have access to the operating
system to install and configure any needed software or to find out the IP
address and port (you can bet it's not 3306) of the database server if the
MySQL database isn't running on the Web server, which is the usual
configuration, since databases on Web servers are security risks.

There are many obstacles to overcome, so if you can't get port forwarding to
work with an ODBC connection, then there are other alternatives, but unless
you purchase a third-party utility, the best one is to build a Web
application to connect to the MySQL database on the Web to do all the
reading, adding, updating and deleting of the records. For more information
on connecting to remote databases on the Web, please see the following Web
page:

http://www.Access.QBuilt.com/html/articles.html#DBsOnTheWeb

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


"'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@xxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:u6-dnZNDep0Fa6TZnZ2dnUVZ_tqdnZ2d@xxxxxxxxxxxxxxx
Hello, Lauren Wilson.

I'm reluctant to say this, because it's going to make me look like the
biggest schmuck this side of the Mississippi. So be it.

I know certain things that you don't realize I know, Lauren, so I can say
with confidence that most, if not all, of what you've just told me to gain
my sympathies is bogus. I will not accept your apology unless you come
clean -- publicly, and in this newsgroup.

What we're trying to do is create
a means of imbedding a form into an application that can be updated to
display certain user instructional announcements from records stored
on a MySQL database on a web server. The application is an
educational tool for a local volunteer group that mentors and guides
disadvantaged kids in our area.

Can the Access database application be co-located with the MySQL database
on the same network? If not, what operating systems (and versions) are
the Web server and MySQL database on? Which Web server (and version) is
being used?

What type of Web space are we talking about? Is it the organization's Web
space (where they have permission to configure the server and install
software), the organization's ISP's Web space or a commercial Web host's
Web space (where they may have limited permissions on configuring and
installing software)? Is there a database size limit or Web disk space
limit that we should know about?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


"Cheryl Langdon" <public@xxxxxxxxxxxxxxxxxxx> wrote in message
news:2s9j32d3bi4t2sost130ooh0vgv22dpqim@xxxxxxxxxx
On Sun, 9 Apr 2006 01:21:14 -0700, "'69 Camaro"
<ForwardZERO_SPAM.To.69Camaro@xxxxxxxxxxxxxxxxxxxxxx> wrote:

Hello, Lauren Wilson.

This is my first attempt at getting help in this manner.

It's your first time pretending to be Cheryl, but you've posted hundreds
of
messages in this newsgroup when you asked for help as Lauren Wilson. We
didn't fall off the onion truck yesterday, you know. It was the day
before.

Please
forgive me

A full tank of gas in my car might help sway me, but I think Lyle has
bigger
aspirations.

I suddenly find myself in urgent need of instruction on how to
communicate with a MySQL database table on a web server, from inside
of my company's Access-VBA application.

That hidden DAP/ASP idea didn't work out? The one where you wanted to
manipulate records in the remote Access/SQL Server database on the Web
from
your customers' MDE files, unbeknownst to the customer, in order to get
the
licensing info to protect your intellectual property? Back to the
drawing
board, I guess.

I need to be able to create new records, read from and update records
stored in a MySQL database on a web server FROM a client based Access
2003 application using VBA. The communication needs to be via HTTP or
HTTPS and the data can be transferred as XML.

So, with these requirements, the only thing that has changed is the
database
engine. Correct? When you attempted to do this with SQL Server, did you
run into any problems? If not, then the only things you need to change
are
the database driver (MyODBC), the connection string, and the SQL, because
SQL Server's T-SQL isn't quite the same dialect, but for simple adds,
deletes, and updates, it's not a stretch to learn MySQL's syntax.

You'll find an example connection string on the following Web page:

http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForMySQL

Download the MySQL reference manual for the SQL syntax from the following
Web page:

http://dev.mysql.com/doc

that
will help me learn to do these functions in the shortest possible
time.

I know that you don't want this advice, Lauren, but I'm going to offer it
anyway. Take the time to learn the technologies you are using. When
someone's approach to software development is "just spoon feed me enough
to
get by," that developer doesn't get much background knowledge of the
topic,
so can't foresee likely problems or decide on the best solutions. And
when
a slightly complex problem pops up, the spoon-fed developer is going to
be
stumped, with no idea what's wrong, let alone how to fix it.

To give you a head's up, your current strategy is most likely going to
run
into a problem with port 3306. If it does, this will be a show stopper
for
you. Start thinking of alternatives. And if any of your customers
"spoof"
your online database on their own networks, your efforts will be for
naught,
because your application won't be able to recognize that it's getting
bogus
data from a bogus database, unless you specifically code for this.

I have been searching the web for this stuff but I am simply
overwhelmed with lots of loosely related items.

You're looking for shrink-wrapped VBA code snippets to do these online
database tasks, and you'll have a very hard time finding them. VBA isn't
the language of choice for most developers working with online MySQL
databases. Did you notice all those PHP/MySQL and Perl/MySQL tutorials
on
the Web during your search? There's a good reason why there are so many.

I'm sorry to admit that my job is on the line with this project.

I have no idea if you're stretching the truth on this, too, but your
deadline must be looming, since you were assigned these tasks a month
ago.
Has your company considered contracting with an experienced developer
instead of burdening you with these tasks? The job would be completed in
very little time. Unfortunately, if the developer is worth his salt,
he'll
also advise your company on the wisdom of trying to protect your
intellectual property in this way, which it sounds like your company is
not
interested in hearing.

Oh, and another piece of advice, Lauren: next time you post a message in
this newsgroup, check the name on your driver's license first. It may
show
you which name to use.

Thank you Gunny. Actually, I'm using Lauren's PC. She is helping me
recover from a desperate family crisis. Lauren is out of the country
for about a month on business. She allowed me to use her office for
that time if I would help her with one of her projects. She told me I
might get some help from this group if I needed it. I hope I have not
offended anyone.*

As to the goal behind my question, it has nothing to do with any kind
of license thing at all. I'm really not sure what all that talk is
about. Lauren has many projects. What we're trying to do is create
a means of imbedding a form into an application that can be updated to
display certain user instructional announcements from records stored
on a MySQL database on a web server. The application is an
educational tool for a local volunteer group that mentors and guides
disadvantaged kids in our area. I think this is one of Lauren's
charity projects. I agreed to help her with this part of the project
in her absence. I hope I can get it done before she returns because
if I can, I will have a permanent job with one of Lauren's clients who
is also helping the school we're doing this for. I have been
unemployed for almost two years due to serious illness. This may be a
way to help pickup the pieces.

I apologize if I offended you.

"Cheryl Langdon" <public@xxxxxxxxxxxxxxxxxxx> wrote in message
news:g4ug329brg2jgg10qm7jvmv0fch4qotg0r@xxxxxxxxxx
Hello everyone,

This is my first attempt at getting help in this manner. Please
forgive me if this is an inappropriate request.

I suddenly find myself in urgent need of instruction on how to
communicate with a MySQL database table on a web server, from inside
of my company's Access-VBA application. I know VBA pretty well but
have never before needed to do this HTTP/XML/MySQL type functions.

I need to be able to create new records, read from and update records
stored in a MySQL database on a web server FROM a client based Access
2003 application using VBA. The communication needs to be via HTTP or
HTTPS and the data can be transferred as XML. The table on the web
server is very simple. It has only 10 data fields with a single
column index.

Can any of you fine folks point me in the right direction for
self-tutorials in the form of sample code, articles, books, etc that
will help me learn to do these functions in the shortest possible
time. I'm sorry to admit that my job is on the line with this project.
I have been searching the web for this stuff but I am simply
overwhelmed with lots of loosely related items. Any help or guidance
will be very much appreciated.

--- Cheryl






.



Relevant Pages

  • Re: Re: Need info, books, articles,code, etc: Access VBA to XML to/from MySQL via HTTP
    ... manipulate records in the remote Access/SQL Server database on the Web from ... stored in a MySQL database on a web server FROM a client based Access ... get by," that developer doesn't get much background knowledge of the topic, ...
    (comp.databases.ms-access)
  • Re: Need info, books, articles,code, etc: Access VBA to XML to/from MySQL via HTTP
    ... manipulate records in the remote Access/SQL Server database on the Web from ... stored in a MySQL database on a web server FROM a client based Access ... get by," that developer doesn't get much background knowledge of the topic, ...
    (comp.databases.ms-access)
  • Re: MySQL HTTP XML ODBC
    ... > Web hosting providers normally supply a MySQL database as part of the ... You can access your database by connecting to a MySQL server ... > * a custom ODBC driver would be created to process SQL and talk to our ... > web server using the XML messaging we have defined. ...
    (comp.programming)
  • Re: Nested PHP
    ... And at the third level I can set SQL permissions so that if a program bug renders than accessible, they cant be loaded with the SQL permissions the user has either. ... Simply uploading malware into the web server hierarchy wont change the way the site behaves. ... Code has to be uploaded through a custom interface that isn't even on the same site as the web server, and all code changes can be recorded in the same database. ... Code and data are separately modified through secure programs designed to allow just enough access to do the job. ...
    (comp.lang.php)
  • Re: Auto_new VBA to autoname a document and autosave to a specific
    ... Window's Registry. ... You say that it errors when coming across the first PrivateProfileString ... When a database is on a different server from the Web server. ...
    (microsoft.public.word.vba.general)