By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,850 Members | 990 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,850 IT Pros & Developers. It's quick & easy.

Calling a remote PHP script from within Access?

P: n/a
jj
Is it possible to call a remote php script from within Access? I'm thinking
something like:

DoCMD... http://www.domain.com/scripts/dataquery.php
DoCmd.OpenQuery "update_data", acNormal, acEdit
.....

So the PHP script does something on the server database, then when a linked
table is viewed within access, the data changes have been made?

The problem is, ODBC is too slow for certain things and if I can call a php
script and have it run the more intense data queries on the server, it'd be
a lot faster.

Is this possible?

Nov 12 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
You are sort of mixing apples and oranges (I think). I do ASP and JSP
which operate in web servers. I believe PHP is the same thing. I am
assuming your data server is either Oracle or Microsoft Sql Server. In
either case you can use ADO to invoke stored procedures on your data
Databases. That would be the fastest way to get a result set. It is
true that you can invoke stored procedures from a webpage, ASP, JSP,
PHP, but that would be a hands on ineraction. You are asking if you
could invoke the PHP command from Access, that would be like using send
keys to type something in your webpage (assuming the browser is up and
running). If you want to do this behind the scenes (most logical
choice), ADO is the way to do. This you can do straight from Access.
Just make a reference to MDAC2.6 in tools/References. But this would
not have anything to do with PHP. The only interaction between PHP and
Access I could think of would be to write data to Access from the PHP or
read data from Access to the PHP. If I am completely off base here,
hopefully someone will correct me.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #2

P: n/a
jj
I should be more clear. There's a web server running Apache/PHP/MySQL where
the web data resides. But on the desktop they have Access with a mirrored
set of data and also linked tables to the web server to keep the web site up
to date.

The problem is, it's so slow running large DELETE or INSERT queries via
MySQL's myodbc that I'd like to simply have access call to a php script/page
that would at least delete the necessary records and let access take care of
the rest. Cutting the process time in half.

So the call to the php script would simply be something like
http://whatever.com/script.php?id=1
that could run in the background, not require opening a web browser, etc.

php would run through and delete all records where ID = 1. That would
happen almost instantly on the server. Then access could run it's update or
insert query and being to upload the records to the web server.

Does that make sense? In theory is sounds simple and I'd be surprised if it
couldn't be done in access. Since it's a MySQL database on the server, I
probably can't use ADO --- right?

"Rich P" <rp*****@aol.com> wrote in message
news:3f*********************@news.frii.net...
You are sort of mixing apples and oranges (I think). I do ASP and JSP
which operate in web servers. I believe PHP is the same thing. I am
assuming your data server is either Oracle or Microsoft Sql Server. In
either case you can use ADO to invoke stored procedures on your data
Databases. That would be the fastest way to get a result set. It is
true that you can invoke stored procedures from a webpage, ASP, JSP,
PHP, but that would be a hands on ineraction. You are asking if you
could invoke the PHP command from Access, that would be like using send
keys to type something in your webpage (assuming the browser is up and
running). If you want to do this behind the scenes (most logical
choice), ADO is the way to do. This you can do straight from Access.
Just make a reference to MDAC2.6 in tools/References. But this would
not have anything to do with PHP. The only interaction between PHP and
Access I could think of would be to write data to Access from the PHP or
read data from Access to the PHP. If I am completely off base here,
hopefully someone will correct me.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #3

P: n/a
Can't you use a PASS THROUGH query?

P
"jj" <jj@test.net> wrote in message
news:qh*******************@twister.rdc-kc.rr.com...
I should be more clear. There's a web server running Apache/PHP/MySQL where the web data resides. But on the desktop they have Access with a mirrored
set of data and also linked tables to the web server to keep the web site up to date.

The problem is, it's so slow running large DELETE or INSERT queries via
MySQL's myodbc that I'd like to simply have access call to a php script/page that would at least delete the necessary records and let access take care of the rest. Cutting the process time in half.

So the call to the php script would simply be something like
http://whatever.com/script.php?id=1
that could run in the background, not require opening a web browser, etc.

php would run through and delete all records where ID = 1. That would
happen almost instantly on the server. Then access could run it's update or insert query and being to upload the records to the web server.

Does that make sense? In theory is sounds simple and I'd be surprised if it couldn't be done in access. Since it's a MySQL database on the server, I
probably can't use ADO --- right?

"Rich P" <rp*****@aol.com> wrote in message
news:3f*********************@news.frii.net...
You are sort of mixing apples and oranges (I think). I do ASP and JSP
which operate in web servers. I believe PHP is the same thing. I am
assuming your data server is either Oracle or Microsoft Sql Server. In
either case you can use ADO to invoke stored procedures on your data
Databases. That would be the fastest way to get a result set. It is
true that you can invoke stored procedures from a webpage, ASP, JSP,
PHP, but that would be a hands on ineraction. You are asking if you
could invoke the PHP command from Access, that would be like using send
keys to type something in your webpage (assuming the browser is up and
running). If you want to do this behind the scenes (most logical
choice), ADO is the way to do. This you can do straight from Access.
Just make a reference to MDAC2.6 in tools/References. But this would
not have anything to do with PHP. The only interaction between PHP and
Access I could think of would be to write data to Access from the PHP or
read data from Access to the PHP. If I am completely off base here,
hopefully someone will correct me.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Nov 12 '05 #4

P: n/a
jj
I tried that and oh wow that was fast!!

Weird, it's using the same ODBC driver yet on takes 2 seconds via
pass-through, same statement via normal query takes 10-15 minutes.
Thanks!
"Phobos" <la******@spammers.co.uk> wrote in message
news:KN********************@brightview.com...
Can't you use a PASS THROUGH query?

P
"jj" <jj@test.net> wrote in message
news:qh*******************@twister.rdc-kc.rr.com...
I should be more clear. There's a web server running Apache/PHP/MySQL where
the web data resides. But on the desktop they have Access with a mirrored set of data and also linked tables to the web server to keep the web site up
to date.

The problem is, it's so slow running large DELETE or INSERT queries via
MySQL's myodbc that I'd like to simply have access call to a php script/page
that would at least delete the necessary records and let access take

care of
the rest. Cutting the process time in half.

So the call to the php script would simply be something like
http://whatever.com/script.php?id=1
that could run in the background, not require opening a web browser,
etc.
php would run through and delete all records where ID = 1. That would
happen almost instantly on the server. Then access could run it's update or
insert query and being to upload the records to the web server.

Does that make sense? In theory is sounds simple and I'd be surprised
if it
couldn't be done in access. Since it's a MySQL database on the server,

I probably can't use ADO --- right?

"Rich P" <rp*****@aol.com> wrote in message
news:3f*********************@news.frii.net...
You are sort of mixing apples and oranges (I think). I do ASP and JSP
which operate in web servers. I believe PHP is the same thing. I am
assuming your data server is either Oracle or Microsoft Sql Server. In either case you can use ADO to invoke stored procedures on your data
Databases. That would be the fastest way to get a result set. It is
true that you can invoke stored procedures from a webpage, ASP, JSP,
PHP, but that would be a hands on ineraction. You are asking if you
could invoke the PHP command from Access, that would be like using send keys to type something in your webpage (assuming the browser is up and
running). If you want to do this behind the scenes (most logical
choice), ADO is the way to do. This you can do straight from Access.
Just make a reference to MDAC2.6 in tools/References. But this would
not have anything to do with PHP. The only interaction between PHP and Access I could think of would be to write data to Access from the PHP or read data from Access to the PHP. If I am completely off base here,
hopefully someone will correct me.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Nov 12 '05 #5

P: n/a
jj
The only strange thing is, if I leave it to default ODBC; and I select the
ODBC DSN when running the query, it works fine. When I use the ... build
connection method and try to store the connection info, I get an error back
about it not being to find the connection or table.

It's almost acting like there are too many characters in the field or
something.

Is there a trick to getting this to work? I'd like to have this process
automated and NOT have to select the ODBC DSN every time they run the query.

Thanks!
"Phobos" <la******@spammers.co.uk> wrote in message
news:KN********************@brightview.com...
Can't you use a PASS THROUGH query?

P
"jj" <jj@test.net> wrote in message
news:qh*******************@twister.rdc-kc.rr.com...
I should be more clear. There's a web server running Apache/PHP/MySQL where
the web data resides. But on the desktop they have Access with a mirrored set of data and also linked tables to the web server to keep the web site up
to date.

The problem is, it's so slow running large DELETE or INSERT queries via
MySQL's myodbc that I'd like to simply have access call to a php script/page
that would at least delete the necessary records and let access take

care of
the rest. Cutting the process time in half.

So the call to the php script would simply be something like
http://whatever.com/script.php?id=1
that could run in the background, not require opening a web browser,
etc.
php would run through and delete all records where ID = 1. That would
happen almost instantly on the server. Then access could run it's update or
insert query and being to upload the records to the web server.

Does that make sense? In theory is sounds simple and I'd be surprised
if it
couldn't be done in access. Since it's a MySQL database on the server,

I probably can't use ADO --- right?

"Rich P" <rp*****@aol.com> wrote in message
news:3f*********************@news.frii.net...
You are sort of mixing apples and oranges (I think). I do ASP and JSP
which operate in web servers. I believe PHP is the same thing. I am
assuming your data server is either Oracle or Microsoft Sql Server. In either case you can use ADO to invoke stored procedures on your data
Databases. That would be the fastest way to get a result set. It is
true that you can invoke stored procedures from a webpage, ASP, JSP,
PHP, but that would be a hands on ineraction. You are asking if you
could invoke the PHP command from Access, that would be like using send keys to type something in your webpage (assuming the browser is up and
running). If you want to do this behind the scenes (most logical
choice), ADO is the way to do. This you can do straight from Access.
Just make a reference to MDAC2.6 in tools/References. But this would
not have anything to do with PHP. The only interaction between PHP and Access I could think of would be to write data to Access from the PHP or read data from Access to the PHP. If I am completely off base here,
hopefully someone will correct me.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Nov 12 '05 #6

P: n/a
jj" <jj@test.net> wrote in message
news:Rn******************@twister.rdc-kc.rr.com...
I tried that and oh wow that was fast!!

Weird, it's using the same ODBC driver yet on takes 2 seconds via
pass-through, same statement via normal query takes 10-15 minutes.
That's because PASS THROUGH queries run on the server instead of over the
network.
Thanks!
You're welcome.
"Phobos" <la******@spammers.co.uk> wrote in message
news:KN********************@brightview.com...
Can't you use a PASS THROUGH query?

Nov 12 '05 #7

P: n/a
"jj" <jj@test.net> wrote in message
news:Gu******************@twister.rdc-kc.rr.com...
The only strange thing is, if I leave it to default ODBC; and I select the
ODBC DSN when running the query, it works fine. When I use the ... build
connection method and try to store the connection info, I get an error back about it not being to find the connection or table.
Strange, are you sure that your connection string is correct?

Also, read the help files as pass-through queries can be a bit
temperamental.

Thanks!


You're welcome.

P
Nov 12 '05 #8

P: n/a
jj
Here's what it creates in the ODBC Connect Str field:
ODBC;DSN=XXXX;DESC=MySQL ODBC 3.51 Driver
DSN;DATABASE=develop;SERVER=111.111.111.118;UID=de velop;PASSWORD=pass223;POR
T=3306;OPTION=3;STMT=;

Does that look right?

The error it returns is:
The Microsoft Jet database engine cannot find the input table or query
'ODBC;DSN=XXXX;DESC=MySQL ODBC 3.51 Driver DSN;DATABASE=develop;SE'. Make
sure it exists and that its name is spelled correctly.
Hmmm.... it's like it's reading the connection string as the query?

"Phobos" <la******@spammers.co.uk> wrote in message
news:xo********************@brightview.com...
"jj" <jj@test.net> wrote in message
news:Gu******************@twister.rdc-kc.rr.com...
The only strange thing is, if I leave it to default ODBC; and I select the ODBC DSN when running the query, it works fine. When I use the ... build connection method and try to store the connection info, I get an error

back
about it not being to find the connection or table.


Strange, are you sure that your connection string is correct?

Also, read the help files as pass-through queries can be a bit
temperamental.

Thanks!


You're welcome.

P

Nov 12 '05 #9

P: n/a
To automate ODBC connections in Access you can use the TableDef object.
First Drop (delete) the ODBC table. Then

Dim tdf As TableDef
Set tdf = Currentdb.TableDefs("yourODBCtbl")
tdf.Connect = "ODBC;DSN=ODBCdsnInControlPannel;UID=joe;PWD=s hmo"
tdf.SourceTableName = "NameOfSourceTblLinkingTo"
CurrentDB.TableDefs.Append tdf

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #10

P: n/a
jj
After I closed and opened access again, the pass through query is working.
Kind of odd but that did the trick.

I guess there isn't a quick way to do an INSERT from the desktop to the
remote database? There's around 3,000 records and it runs for about 5-10
minutes. But that can't be done by a pass-through query since it's going
desktop --> server. Any other suggestions to speed that process up?

"jj" <jj@test.net> wrote in message
news:jk******************@twister.rdc-kc.rr.com...
Here's what it creates in the ODBC Connect Str field:
ODBC;DSN=XXXX;DESC=MySQL ODBC 3.51 Driver
DSN;DATABASE=develop;SERVER=111.111.111.118;UID=de velop;PASSWORD=pass223;POR T=3306;OPTION=3;STMT=;

Does that look right?

The error it returns is:
The Microsoft Jet database engine cannot find the input table or query
'ODBC;DSN=XXXX;DESC=MySQL ODBC 3.51 Driver DSN;DATABASE=develop;SE'. Make
sure it exists and that its name is spelled correctly.
Hmmm.... it's like it's reading the connection string as the query?

"Phobos" <la******@spammers.co.uk> wrote in message
news:xo********************@brightview.com...
"jj" <jj@test.net> wrote in message
news:Gu******************@twister.rdc-kc.rr.com...
The only strange thing is, if I leave it to default ODBC; and I select the ODBC DSN when running the query, it works fine. When I use the ... build connection method and try to store the connection info, I get an error

back
about it not being to find the connection or table.


Strange, are you sure that your connection string is correct?

Also, read the help files as pass-through queries can be a bit
temperamental.

Thanks!


You're welcome.

P


Nov 12 '05 #11

P: n/a
rp*****@aol.com (Rich P) wrote in
<3f*********************@news.frii.net>:
You are sort of mixing apples and oranges (I think). I do ASP and
JSP which operate in web servers. I believe PHP is the same
thing. I am assuming your data server is either Oracle or
Microsoft Sql Server. In either case you can use ADO to invoke
stored procedures on your data Databases. That would be the
fastest way to get a result set. It is true that you can invoke
stored procedures from a webpage, ASP, JSP, PHP, but that would be
a hands on ineraction. You are asking if you could invoke the PHP
command from Access, that would be like using send keys to type
something in your webpage (assuming the browser is up and
running).


While it is certainly better to use the pass-through query, as you
suggested, there is absolutely nothing wrong with executing a
remote script on a web server from Access. I've done it in cases
where I had no way of actually connecting to the remote server, and
it's accomplished in exactly the same way as if a user wanted to
open any old web page, you just execute a URL. I don't let Access
handle it, but instead use the Windows ShellExecute API, because
this will use the default browser, whereas the native Access
functions will use Internet Explorer even if a good browser such as
Mozilla is the default browser.

In any event, conceptually, there is nothing wrong with the idea of
executing a remote PHP script from Access. I've done it in order to
remotely initiate a text file data dump a remote MySQL database
that is then FTP'd to the local system.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #12

P: n/a
ss
Cool, do you have an example of this?

Is there a way to call the remote URL without opening a browser?

Is there also a way for Access to call this remote web page and look for a
specific variable? Say the remote PHP runs it's script then returns a var =
yes and if the var = yes access would run another query? if var = no Access
wouldn't do anything else at that point?

Thanks in advance.

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78.. .
rp*****@aol.com (Rich P) wrote in
<3f*********************@news.frii.net>: While it is certainly better to use the pass-through query, as you
suggested, there is absolutely nothing wrong with executing a
remote script on a web server from Access. I've done it in cases
where I had no way of actually connecting to the remote server, and
it's accomplished in exactly the same way as if a user wanted to
open any old web page, you just execute a URL. I don't let Access
handle it, but instead use the Windows ShellExecute API, because
this will use the default browser, whereas the native Access
functions will use Internet Explorer even if a good browser such as
Mozilla is the default browser.

In any event, conceptually, there is nothing wrong with the idea of
executing a remote PHP script from Access. I've done it in order to
remotely initiate a text file data dump a remote MySQL database
that is then FTP'd to the local system.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Nov 12 '05 #13

P: n/a
I think the Windows port of the wget program should do exactly what you're
asking. See http://unxutils.sourceforge.net/. If you try this, it would
be nice if you post back and tell us if it worked for you.

On Thu, 09 Oct 2003 04:51:33 GMT, "ss" <ss@email.net> wrote:
Cool, do you have an example of this?

Is there a way to call the remote URL without opening a browser?

Is there also a way for Access to call this remote web page and look for a
specific variable? Say the remote PHP runs it's script then returns a var =
yes and if the var = yes access would run another query? if var = no Access
wouldn't do anything else at that point?

Thanks in advance.

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78. ..
rp*****@aol.com (Rich P) wrote in
<3f*********************@news.frii.net>:

While it is certainly better to use the pass-through query, as you
suggested, there is absolutely nothing wrong with executing a
remote script on a web server from Access. I've done it in cases
where I had no way of actually connecting to the remote server, and
it's accomplished in exactly the same way as if a user wanted to
open any old web page, you just execute a URL. I don't let Access
handle it, but instead use the Windows ShellExecute API, because
this will use the default browser, whereas the native Access
functions will use Internet Explorer even if a good browser such as
Mozilla is the default browser.

In any event, conceptually, there is nothing wrong with the idea of
executing a remote PHP script from Access. I've done it in order to
remotely initiate a text file data dump a remote MySQL database
that is then FTP'd to the local system.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc


Nov 12 '05 #14

P: n/a
"jj" <jj@test.net> wrote in message
news:c5*******************@twister.rdc-kc.rr.com...
After I closed and opened access again, the pass through query is working.
Kind of odd but that did the trick.

I guess there isn't a quick way to do an INSERT from the desktop to the
remote database? There's around 3,000 records and it runs for about 5-10
minutes. But that can't be done by a pass-through query since it's going
desktop --> server. Any other suggestions to speed that process up?


Other than saving the records to a floppy and sending it by post? <g>

5-10 minutes is not that bad to be honest.

P
Nov 12 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.