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/~s.../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....DriverForMySQL
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/ar...ml#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/ex...ributors2.html for contact
info.
"'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@Spameater.orgZERO_SP AM> wrote in
message news:u6-dnZNDep0Fa6TZnZ2dnUVZ_tqdnZ2d@adelphia.com...[color=blue]
> 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.
>[color=green]
>> 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.[/color]
>
> 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/ex...ributors2.html for contact
> info.
>
>
> "Cheryl Langdon" <public@hasnoneedtoknow.com> wrote in message
> news:2s9j32d3bi4t2sost130ooh0vgv22dpqim@4ax.com...[color=green]
>> On Sun, 9 Apr 2006 01:21:14 -0700, "'69 Camaro"
>> <ForwardZERO_SPAM.To.69Camaro@Spameater.orgZERO_SP AM> wrote:
>>[color=darkred]
>>>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....DriverForMySQL
>>>
>>>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.[/color]
>>
>> 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.
>>[color=darkred]
>>>"Cheryl Langdon" <public@hasnoneedtoknow.com> wrote in message
>>>news:g4ug329brg2jgg10qm7jvmv0fch4qotg0r@4ax.com ...
>>>> 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
>>>
>>>[/color][/color]
>
>[/color]
