423,851 Members | 1,043 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,851 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Apr 9 '06 #1
Share this Question
Share on Google+
15 Replies


P: n/a
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.

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" <pu****@hasnoneedtoknow.com> wrote in message
news:g4********************************@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


Apr 9 '06 #2

P: n/a
> 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.

HTH.
Gunny

I don't know whether "Gunny", "'69" or "Camaro" appear on your driving
licence.
Neither "Anthony" nor "England" appear on my mine, so I don't believe people
are obliged to post full passport details or a valid e-mail address, but I
think what's going on here is different - and you're right to point it out.
Falsely claiming you've never posted before when you have already received
many hours of peoples time is not really fair.
Perhaps you will claim this is not true, but do a Google search with phrases
like "fine folks" "lauren wilson". Is it just coincidence that both Lauren
and Cheryl use such a phrase and are both seeking help with similar topics?
Apr 9 '06 #3

P: n/a
Hi, Anthony.
I don't know whether "Gunny", "'69" or "Camaro" appear on your driving
licence.
Many people just call me Gunny, for short, because my full name requires 64
characters and is difficult to pronounce correctly. And I bought an old '69
Camaro so that I could learn how to drive and get my first driver's license.
Does that count?

As for the following:
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.
.. . . I was being facetious, and I doubt her name is really Lauren. She's
used so many different names when she's posted messages in the newsgroups
that it might help her to remember which one to use if she just references a
single name that she has written down and that she can easily find at all
times, such as her driver's license.
Falsely claiming you've never posted before when you have already received
many hours of peoples time is not really fair.


That's exactly what caught my attention and prompted me to look more closely
at who the poster was. Most brand new posters post a brief question that's
only one or two sentences, and often don't use the correct terminology. Her
post provided much detail about what she's after and was obviously well
thought out. It didn't look like a first-timer's post. And then I
remembered Lauren's posts from last month that requested virtually the same
thing, but she stopped posting to those threads when she was confronted with
what she didn't want to hear regarding her philosophies. Ergo, she's
baa-aack!

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.
"Anthony England" <ae******@oops.co.uk> wrote in message
news:D5******************************@bt.com... 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.

HTH.
Gunny

I don't know whether "Gunny", "'69" or "Camaro" appear on your driving
licence.
Neither "Anthony" nor "England" appear on my mine, so I don't believe
people are obliged to post full passport details or a valid e-mail
address, but I think what's going on here is different - and you're right
to point it out. Falsely claiming you've never posted before when you have
already received many hours of peoples time is not really fair.
Perhaps you will claim this is not true, but do a Google search with
phrases like "fine folks" "lauren wilson". Is it just coincidence that
both Lauren and Cheryl use such a phrase and are both seeking help with
similar topics?

Apr 10 '06 #4

P: n/a
On Sun, 9 Apr 2006 01:21:14 -0700, "'69 Camaro"
<Fo**************************@Spameater.orgZERO_SP AM> 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....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.


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.
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" <pu****@hasnoneedtoknow.com> wrote in message
news:g4********************************@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


Apr 10 '06 #5

P: n/a
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/ex...ributors2.html for contact
info.
"Cheryl Langdon" <pu****@hasnoneedtoknow.com> wrote in message
news:2s********************************@4ax.com... On Sun, 9 Apr 2006 01:21:14 -0700, "'69 Camaro"
<Fo**************************@Spameater.orgZERO_SP AM> 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....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.


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" <pu****@hasnoneedtoknow.com> wrote in message
news:g4********************************@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


Apr 10 '06 #6

P: n/a

"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM> wrote in
message news:8M******************************@adelphia.com ...
Hi, Anthony.
I don't know whether "Gunny", "'69" or "Camaro" appear on your driving
licence.


Many people just call me Gunny, for short, because my full name requires
64 characters and is difficult to pronounce correctly. And I bought an
old '69 Camaro so that I could learn how to drive and get my first
driver's license. Does that count?


Absolutely.
In case it seemed otherwise, I 100% approve of your reply.
I only meant that I didn't even think it was wrong to use a bogus name -
especially if you are providing help rather than asking for it.

I also seem to have anticipated the next stage - the denial. Now the poster
claims it is a different person but the same machine. I wonder whether it
is coincidence that the 'new person' also uses the expression 'you fine
folks' as does 'Lauren Wilson'. Perhaps they also dress in a similar
fashion.
Apr 10 '06 #7

P: n/a
> 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.


Am I just cynical, or do others find that the sadder the story gets, the
harder it is to believe?

desperate family crisis
unemployed for almost two years due to serious illness
charity projects
local volunteer group
disadvantaged kids

Perhaps it would be better to appeal to us for some money with which you
could hire an experienced developer.
Apr 10 '06 #8

P: n/a
Hi, Anthony.
I also seem to have anticipated the next stage - the denial.
Yup. You saw that one coming.
Perhaps they also dress in a similar fashion.
Right down to the same shoe size. They even use the same toothbrush.

Gunny
"Anthony England" <ae******@oops.co.uk> wrote in message
news:5d********************@bt.com...
Absolutely.
In case it seemed otherwise, I 100% approve of your reply.
I only meant that I didn't even think it was wrong to use a bogus name -
especially if you are providing help rather than asking for it.

I also seem to have anticipated the next stage - the denial. Now the
poster claims it is a different person but the same machine. I wonder
whether it is coincidence that the 'new person' also uses the expression
'you fine folks' as does 'Lauren Wilson'. Perhaps they also dress in a
similar fashion.

Apr 10 '06 #9

P: n/a
Cheryl Langdon <pu****@hasnoneedtoknow.com> wrote in
news:2s********************************@4ax.com:
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.


Can we send money? How much would be enough?

--
Lyle Fairfield
Apr 10 '06 #10

P: n/a
Anthony England wrote:
Am I just cynical, or do others find that the sadder the story gets, the
harder it is to believe?


They are actually pretty standard excuses/fabrications used by garden
variety net-loons.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Apr 10 '06 #11

P: n/a
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" <Fo**************************@Spameater.orgZERO_SP AM> wrote in
message news:u6******************************@adelphia.com ...
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/ex...ributors2.html for contact
info.
"Cheryl Langdon" <pu****@hasnoneedtoknow.com> wrote in message
news:2s********************************@4ax.com...
On Sun, 9 Apr 2006 01:21:14 -0700, "'69 Camaro"
<Fo**************************@Spameater.orgZERO_SP AM> 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....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.


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" <pu****@hasnoneedtoknow.com> wrote in message
news:g4********************************@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


Apr 23 '06 #12

P: n/a
Br
'69 Camaro wrote:
... 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.


It's as easy as... (SQL Server example)

Dim myConnection As New Connection
Dim mySQL As String
Dim myRS As New ADODB.Recordset
myConnection.ConnectionString = "Provider=SQLOLEDB;Data
Source=my.host.com;Initial Catalog=myDatabase;User
ID=myUser;Password=myPass;"
myConnection.Open
'clear company data
mySQL = "DELETE FROM tblEmployee WHERE CompanyNumber = 10"
myConnection.Execute mySQL

If you want you can directly connect your ADP to a web based SQL Server for
example.

<>
--
regards,

Br@dley
Apr 24 '06 #13

P: n/a
Hi, Br@dley.
It's as easy as... (SQL Server example)
Thanks for that. While connecting to SQL Server over the Internet is
straightforward, connecting to a MySQL database on a Web server behind a
firewall where the port is blocked is a horse of a different color. MySQL
database servers connected to the Internet are configured this way, because
few Web hosts will allow their database servers to be exposed to security
risks.

And now that I reread my previous post, I realize that I plumb forgot to
mention that this was the show stopper that I alluded to in my first message
in this thread. Yeah, it's crystal clear in my mind, but totally invisible
to everyone else reading my message, unless they've tried to overcome the
same hurdles. Sorry for any confusion.

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.
"Br@dley" <do***********@google.com> wrote in message
news:e2**********@news-02.connect.com.au... '69 Camaro wrote:
... 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.


It's as easy as... (SQL Server example)

Dim myConnection As New Connection
Dim mySQL As String
Dim myRS As New ADODB.Recordset
myConnection.ConnectionString = "Provider=SQLOLEDB;Data
Source=my.host.com;Initial Catalog=myDatabase;User
ID=myUser;Password=myPass;"
myConnection.Open
'clear company data
mySQL = "DELETE FROM tblEmployee WHERE CompanyNumber = 10"
myConnection.Execute mySQL

If you want you can directly connect your ADP to a web based SQL Server
for example.

<>
--
regards,

Br@dley

Apr 24 '06 #14

P: n/a
I don't know if that's helpful for Lauren or Charmaine but it's
interesting to me. I have MySQL (over the net) available to me free
through the company that hosts my site, so next rainy afternoon I'll
review your comments and experiment with it, maybe, (but it's all
windows :-().
As for Charmaine, I think it would be grand if she came back to the
group, cut with the disingenuous stuff, participated, got help and
helped.
I've suggested this to others previously but it seems many leopards do
not change their spots. Pity!

Apr 24 '06 #15

P: n/a
Hi, Lyle.
I don't know if that's helpful for Lauren or Charmaine
Probably not. Since Lauren's goal is to have the application remain
incognito while it "phones home" for the licensing information, having a
System Administrator configure the workstation is going to be a dead
giveaway, because he'll know exactly what the secure tunnel does. OTOH,
Lauren's company could have this configuration done automatically as part of
the application's install, but there are so many variables to contend with
that I wouldn't have high hopes for success. Or her company could manually
install future versions of their application on customers' workstations and
just have this special configuration on the list of steps to carry out.

But the first time it "phones home," the software firewall will catch it and
either the user will ask management, "What's this?!!" or automatically hit
the "No" button to prevent some unknown application from connecting to the
Internet. So much for incognito . . . .
but it's
interesting to me.
Excellent! Glad I could offer something of value.
I have MySQL (over the net) available to me free
through the company that hosts my site, so next rainy afternoon I'll
review your comments and experiment with it, maybe, (but it's all
windows :-().
You may get lucky and have a secure shell server running on the Windows Web
server, but if you don't, then this technique won't work. However, there
are free Web hosting sites that offer MySQL on Linux, and I can't say that
they're reliable enough to make one want to host a real Web site on them,
but one could apply for the free hosting to experiment with. I've never
checked myself, so I don't know of any that have secure shell servers
running, but it might be worth investigating.
As for Charmaine, I think it would be grand if she came back to the
group, cut with the disingenuous stuff, participated, got help and
helped.
Hmm. Do you think perhaps it's my fault she left? She may have read only
the first paragraph or two in my post two weeks ago, stopped reading any
further, and not realized that we weren't going to refuse to help her.

Personally, I think she'll eventually come back, but not as Lauren. She'll
use one of her aliases or may even create a new one and fool us again. ;-)

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.
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...I don't know if that's helpful for Lauren or Charmaine but it's
interesting to me. I have MySQL (over the net) available to me free
through the company that hosts my site, so next rainy afternoon I'll
review your comments and experiment with it, maybe, (but it's all
windows :-().
As for Charmaine, I think it would be grand if she came back to the
group, cut with the disingenuous stuff, participated, got help and
helped.
I've suggested this to others previously but it seems many leopards do
not change their spots. Pity!

Apr 24 '06 #16

This discussion thread is closed

Replies have been disabled for this discussion.