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_dat a", 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?

[Rich P]

Re: Calling a remote PHP script from within Access?

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!

[jj]

Re: Calling a remote PHP script from within Access?

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" <rpng123@aol.co m> wrote in message
news:3f845605$0 $196$75868355@n ews.frii.net...[color=blue]
> 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![/color]

[Phobos]

Re: Calling a remote PHP script from within Access?

Can't you use a PASS THROUGH query?

P


"jj" <jj@test.net> wrote in message
news:qhZgb.2493 8$832.24684@twi ster.rdc-kc.rr.com...[color=blue]
> I should be more clear. There's a web server running Apache/PHP/MySQL[/color]
where[color=blue]
> 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[/color]
up[color=blue]
> 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[/color]
script/page[color=blue]
> that would at least delete the necessary records and let access take care[/color]
of[color=blue]
> 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[/color]
or[color=blue]
> 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[/color]
it[color=blue]
> couldn't be done in access. Since it's a MySQL database on the server, I
> probably can't use ADO --- right?
>
>
>
> "Rich P" <rpng123@aol.co m> wrote in message
> news:3f845605$0 $196$75868355@n ews.frii.net...[color=green]
> > 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![/color]
>
>[/color]

[jj]

Re: Calling a remote PHP script from within Access?

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" <laughing@spamm ers.co.uk> wrote in message
news:KNqdncyJJo PH-hmiU-KYhw@brightview .com...[color=blue]
> Can't you use a PASS THROUGH query?
>
> P
>
>
> "jj" <jj@test.net> wrote in message
> news:qhZgb.2493 8$832.24684@twi ster.rdc-kc.rr.com...[color=green]
> > I should be more clear. There's a web server running Apache/PHP/MySQL[/color]
> where[color=green]
> > the web data resides. But on the desktop they have Access with a[/color][/color]
mirrored[color=blue][color=green]
> > set of data and also linked tables to the web server to keep the web[/color][/color]
site[color=blue]
> up[color=green]
> > 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[/color]
> script/page[color=green]
> > that would at least delete the necessary records and let access take[/color][/color]
care[color=blue]
> of[color=green]
> > 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,[/color][/color]
etc.[color=blue][color=green]
> >
> > 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[/color][/color]
update[color=blue]
> or[color=green]
> > 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[/color][/color]
if[color=blue]
> it[color=green]
> > couldn't be done in access. Since it's a MySQL database on the server,[/color][/color]
I[color=blue][color=green]
> > probably can't use ADO --- right?
> >
> >
> >
> > "Rich P" <rpng123@aol.co m> wrote in message
> > news:3f845605$0 $196$75868355@n ews.frii.net...[color=darkred]
> > > 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.[/color][/color][/color]
In[color=blue][color=green][color=darkred]
> > > 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[/color][/color][/color]
send[color=blue][color=green][color=darkred]
> > > 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[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> > > Access I could think of would be to write data to Access from the PHP[/color][/color][/color]
or[color=blue][color=green][color=darkred]
> > > 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![/color]
> >
> >[/color]
>
>[/color]

[jj]

trouble with ODBC connection via pass-thru

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" <laughing@spamm ers.co.uk> wrote in message
news:KNqdncyJJo PH-hmiU-KYhw@brightview .com...[color=blue]
> Can't you use a PASS THROUGH query?
>
> P
>
>
> "jj" <jj@test.net> wrote in message
> news:qhZgb.2493 8$832.24684@twi ster.rdc-kc.rr.com...[color=green]
> > I should be more clear. There's a web server running Apache/PHP/MySQL[/color]
> where[color=green]
> > the web data resides. But on the desktop they have Access with a[/color][/color]
mirrored[color=blue][color=green]
> > set of data and also linked tables to the web server to keep the web[/color][/color]
site[color=blue]
> up[color=green]
> > 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[/color]
> script/page[color=green]
> > that would at least delete the necessary records and let access take[/color][/color]
care[color=blue]
> of[color=green]
> > 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,[/color][/color]
etc.[color=blue][color=green]
> >
> > 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[/color][/color]
update[color=blue]
> or[color=green]
> > 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[/color][/color]
if[color=blue]
> it[color=green]
> > couldn't be done in access. Since it's a MySQL database on the server,[/color][/color]
I[color=blue][color=green]
> > probably can't use ADO --- right?
> >
> >
> >
> > "Rich P" <rpng123@aol.co m> wrote in message
> > news:3f845605$0 $196$75868355@n ews.frii.net...[color=darkred]
> > > 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.[/color][/color][/color]
In[color=blue][color=green][color=darkred]
> > > 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[/color][/color][/color]
send[color=blue][color=green][color=darkred]
> > > 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[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> > > Access I could think of would be to write data to Access from the PHP[/color][/color][/color]
or[color=blue][color=green][color=darkred]
> > > 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![/color]
> >
> >[/color]
>
>[/color]

[Phobos]

Re: Calling a remote PHP script from within Access?

jj" <jj@test.net> wrote in message
news:Rn_gb.2494 1$832.4239@twis ter.rdc-kc.rr.com...[color=blue]
> 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.[/color]

That's because PASS THROUGH queries run on the server instead of over the
network.
[color=blue]
> Thanks![/color]

You're welcome.
[color=blue]
> "Phobos" <laughing@spamm ers.co.uk> wrote in message
> news:KNqdncyJJo PH-hmiU-KYhw@brightview .com...[color=green]
> > Can't you use a PASS THROUGH query?[/color][/color]

[Phobos]

Re: trouble with ODBC connection via pass-thru

"jj" <jj@test.net> wrote in message
news:Gu_gb.2494 4$832.1194@twis ter.rdc-kc.rr.com...[color=blue]
> 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[/color]
back[color=blue]
> about it not being to find the connection or table.[/color]

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

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

[color=blue]
> Thanks![/color]

You're welcome.

P

[jj]

Re: trouble with ODBC connection via pass-thru

Here's what it creates in the ODBC Connect Str field:
ODBC;DSN=XXXX;D ESC=MySQL ODBC 3.51 Driver
DSN;DATABASE=de velop;SERVER=11 1.111.111.118;U ID=develop;PASS WORD=pass223;PO R
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=de velop;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" <laughing@spamm ers.co.uk> wrote in message
news:xoqdnY9Zcd Ys4hmiU-KYhw@brightview .com...[color=blue]
> "jj" <jj@test.net> wrote in message
> news:Gu_gb.2494 4$832.1194@twis ter.rdc-kc.rr.com...[color=green]
> > The only strange thing is, if I leave it to default ODBC; and I select[/color][/color]
the[color=blue][color=green]
> > ODBC DSN when running the query, it works fine. When I use the ...[/color][/color]
build[color=blue][color=green]
> > connection method and try to store the connection info, I get an error[/color]
> back[color=green]
> > about it not being to find the connection or table.[/color]
>
> Strange, are you sure that your connection string is correct?
>
> Also, read the help files as pass-through queries can be a bit
> temperamental.
>
>[color=green]
> > Thanks![/color]
>
> You're welcome.
>
> P
>
>[/color]

[Rich P]

Re: trouble with ODBC connection via pass-thru

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.Table Defs("yourODBCt bl")
tdf.Connect = "ODBC;DSN=ODBCd snInControlPann el;UID=joe;PWD= shmo"
tdf.SourceTable Name = "NameOfSourceTb lLinkingTo"
CurrentDB.Table Defs.Append tdf

Rich

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

[jj]

now it's working...

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%gb.2494 9$832.4404@twis ter.rdc-kc.rr.com...[color=blue]
> Here's what it creates in the ODBC Connect Str field:
> ODBC;DSN=XXXX;D ESC=MySQL ODBC 3.51 Driver
>[/color]
DSN;DATABASE=de velop;SERVER=11 1.111.111.118;U ID=develop;PASS WORD=pass223;PO R[color=blue]
> 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=de velop;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" <laughing@spamm ers.co.uk> wrote in message
> news:xoqdnY9Zcd Ys4hmiU-KYhw@brightview .com...[color=green]
> > "jj" <jj@test.net> wrote in message
> > news:Gu_gb.2494 4$832.1194@twis ter.rdc-kc.rr.com...[color=darkred]
> > > The only strange thing is, if I leave it to default ODBC; and I select[/color][/color]
> the[color=green][color=darkred]
> > > ODBC DSN when running the query, it works fine. When I use the ...[/color][/color]
> build[color=green][color=darkred]
> > > connection method and try to store the connection info, I get an error[/color]
> > back[color=darkred]
> > > about it not being to find the connection or table.[/color]
> >
> > Strange, are you sure that your connection string is correct?
> >
> > Also, read the help files as pass-through queries can be a bit
> > temperamental.
> >
> >[color=darkred]
> > > Thanks![/color]
> >
> > You're welcome.
> >
> > P
> >
> >[/color]
>
>[/color]

[David W. Fenton]

Re: Calling a remote PHP script from within Access?

rpng123@aol.com (Rich P) wrote in
<3f845605$0$196 $75868355@news. frii.net>:
[color=blue]
>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).[/color]

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

[ss]

Re: Calling a remote PHP script from within Access?

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" <dXXXfenton@bwa y.net.invalid> wrote in message
news:940ED38A7d fentonbwaynetin vali@24.168.128 .78...[color=blue]
> rpng123@aol.com (Rich P) wrote in
> <3f845605$0$196 $75868355@news. frii.net>:[/color]
[color=blue]
> 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[/color]

[Steve Jorgensen]

Re: Calling a remote PHP script from within Access?

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:
[color=blue]
>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" <dXXXfenton@bwa y.net.invalid> wrote in message
>news:940ED38A7 dfentonbwayneti nvali@24.168.12 8.78...[color=green]
>> rpng123@aol.com (Rich P) wrote in
>> <3f845605$0$196 $75868355@news. frii.net>:[/color]
>[color=green]
>> 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[/color]
>[/color]

[Phobos]

Re: now it's working...

"jj" <jj@test.net> wrote in message
news:c50hb.2496 0$832.11685@twi ster.rdc-kc.rr.com...[color=blue]
> 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?[/color]

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