473,287 Members | 1,463 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Calling a remote PHP script from within Access?

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
14 8933
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
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
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
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
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
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
"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
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
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
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
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
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
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: steve | last post by:
Hi, I finally figured out the best way to synch local and remote script folders, containing many php script files. What I used to do before was try to ftp all the changed files, etc. which was...
4
by: Razzbar | last post by:
I need to be able to conditionally load a remote script, using the "<script src=..." syntax. Or some other way. I've seen people writing about using the document.write method, but it's not working...
9
by: Karl Irvin | last post by:
Does anyone have an example of an FTP script or module for use with Access 2003. I need to send a file from by computer to my web host from within Access. The from folder may or may not be the...
1
by: Tim Hunter | last post by:
Hi, I am using Access 2003 with WinXP/sp#2. I have built a Help file using MS HTML Help Workshop that includes a TOC as well as an Index file. My problem is how do I call this from Access. I do...
1
by: clteh9 | last post by:
There is no error or exception for the code below. It works fine with the calc.exe. But it fails when i call the script. For your information, strScript is the remote script and strFileName is the...
3
by: johnny | last post by:
I have a remote script on a local network and I need to make Web App in PHP, call this remote script on a different machine. How do I do this? Many Thanks.
5
by: Normann | last post by:
Ok I have been searching for days now, so I hope there is someone here who can help me. Is there anyway to call a sql script from within a sql script under MS SQL-Server 2005, i.e. a master...
1
by: bbammes | last post by:
I'm writing a click tracking program which calls a remote PHP script when a user clicks a link. My javascript is: <script type="text/javascript"> //<! ); ?>&clientaddress=<?php echo urlencode(...
1
by: AngieMP | last post by:
Hi Guys I have inherited an Access DB in a very poorly state! I have offered to make some changes to it for a local charity but have got stuck on this error it seems to be creating for all new...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.