How do I Exchange data with web server

Hello,

I have an Access program that needs to upload some data to MySQL database on
a web site. I want it to be as transparent to the user as possible and I
only want the user to have to just press a button and it all happents
without further input from them. It needs to be sent in 2 different batches
because the first part will load a table and an id is assigned which needs
to come back to the Access program and update a table and then generate the
next pieces which will have the id assigned from the first part.

The fist part is working and the MySQL table is loaded and the id is
assigned using some PHP code. How can I get this number back to the Access
program? Following is the Access code and it is using FSO but it doesn't
look like I can use an HTTP address as it complains there is a bad file name
or number.

Would someone be so kind as to offer a solution! I would certaily
appreciate any input anyone would care to provide.

Thanks,

Vic
..........................

Public Sub ExportShowDetails(creatorAddress as String, showName as String)
Dim resp As Long
Dim url As String
Dim creatorAddress As String
Dim ShowName As String
Dim lShowID As Long
Dim fs As New FileSystemObject
Dim ts As TextStream

DoCmd.TransferText acExportDelim, "qryEventDetails Specs",
"qryEventDetails", _
CurrentProject.Path & "\newshow.csv", False

url = "http://localhost/mysite/uploads/upload.php?arguments=newshow.csv;" &
creatorAddress & ";" & ShowName

Call ShellExecute(0&, vbNullString, url, vbNullString, vbNullString,
vbNormalFocus)

' Now we must wait untill we can get the ShowID that would have been
assigned in part 1
' After the show has been loaded, it will put a file in the uploads folder
which contains the showID

url = "http://localhost/mysite/uploads/showid.txt"

Set ts = fs.OpenTextFile(url, ForReading) ===== **
Error occurs here **
lShowID = ts.ReadLine

' ** Now send the remaining table data to the server **

End Sub

I not sure at all what you code is trying to do, but it not going to work,
and it not even close...

However, any reason why you don't just open the tables directly on the MySql
database?

Can you not have a connection setup to the database? (it would be MUCH MUCH
MUCH easer to simply using linked tables).

Further, how does the web site know to read those text files? (and, worse,
what happens if the web site is trying to read that text file,a d you are
trying to overwrite that text file...things likely will hang).

So, you can use the odbc driver for mySql with ms-access,a nd skip all of
the http + text file crap you are trying.

Just use the mysql tables directly as linked tables. It will also save
effort on the web side of things, as it will now not have to read/load some
text file into the database...

It is not clear if you are hosting this web server, or using a provider. In
many cases, to use mysql *OVER* the web, you have to setup a ssl connection.
A few people have pointed out that some providers actually do allow a open
socket connection to the mysql (but, I can't recommend you open up your
database server to the whole internet due to security problems).

So, don't try and communicate with the web site..but communicate with the
database tables directly. It will be MUCH MUCH easier, and you desktop
application can then share the same tables with the web site (ie: your
tables can be on the database server..not your desktop -- both the web site
+ ms-access can use the tables from mysql).

If you MUST upload a text file, then use ftp, a simple way is explained
he
http://www.mvps.org/access/modules/mdl0015.htm

So, you generate you text file on the local hard disk, and then use the
above to transfer to web site..

I used the above to transfer files to my web site...it is simple, and works
well...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Albert,

The reason that I am doing it this way is because the MySQL database is
being hosted by an ISP and they require that the IP address be white listed.
Many of the users are on dial-up access so their IP address changes so they
are not able to use it so I'm trying to build a solution for these people. I
already have it working with authorized IP address which connects directly
to the database and posts the data.

You're wrong when you say it is not going to work because it is already
working to upload a csv file and populate the MySQL table. The problem as I
stated is that I need the Access program to be able to retrieve the value of
the record id assigned in this first upload.

Vic



"Albert D. Kallal" wrote in message
...
I not sure at all what you code is trying to do, but it not going to work,
and it not even close...

However, any reason why you don't just open the tables directly on the
MySql database?

Can you not have a connection setup to the database? (it would be MUCH
MUCH MUCH easer to simply using linked tables).

Further, how does the web site know to read those text files? (and, worse,
what happens if the web site is trying to read that text file,a d you are
trying to overwrite that text file...things likely will hang).

So, you can use the odbc driver for mySql with ms-access,a nd skip all of
the http + text file crap you are trying.

Just use the mysql tables directly as linked tables. It will also save
effort on the web side of things, as it will now not have to read/load
some text file into the database...

It is not clear if you are hosting this web server, or using a provider.
In many cases, to use mysql *OVER* the web, you have to setup a ssl
connection. A few people have pointed out that some providers actually do
allow a open socket connection to the mysql (but, I can't recommend you
open up your database server to the whole internet due to security
problems).

So, don't try and communicate with the web site..but communicate with the
database tables directly. It will be MUCH MUCH easier, and you desktop
application can then share the same tables with the web site (ie: your
tables can be on the database server..not your desktop -- both the web
site + ms-access can use the tables from mysql).

If you MUST upload a text file, then use ftp, a simple way is explained
he
http://www.mvps.org/access/modules/mdl0015.htm

So, you generate you text file on the local hard disk, and then use the
above to transfer to web site..

I used the above to transfer files to my web site...it is simple, and
works well...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

the code of:

url = "http://localhost/mysite/uploads/showid.txt"

Set ts = fs.OpenTextFile(url, ForReading) ===== **
Error occurs here **
lShowID = ts.ReadLine

You can't open that file over http. You have to either transfer the file to
your local machine, and THEN open it.

Many of the users are on dial-up access so their IP address changes so
they are not able to use it

the IP address of the users don't matter!!! What matters is the fixed ip
address of the hosted web site.

I was/am suggesting that you connect to the mysql database via odbc, and not
the web site. You can use a url, or ip
address to connect to the mysql tables. This would also eliminate you upload
of the text file.

You're wrong when you say it is not going to work because it is already
working to upload a csv file and populate the MySQL table.

Well, that simply means that the web site has a upload ability, and software
to read
the csv file on the server side. However, really, connecting to the
mysql database would be far better solution.

Anyway, I was suggesting that you can't open a file OVER the internet via
http when I said can't work.

The problem as I stated is that I need the Access program to be able to
retrieve the value of the record id assigned in this first upload.

You mean the ID supplied by mysql? As mentioned, you can't open the file via
http. You have to either upload the file, or in this case, it seems you need
to download a file.

If you are talking about the ID generated on the ms-access side, then simply
grab it from the qryEventDetails.

eg:

id = dlookup("id","qryEventDetails")

Is the id you looking for from qeryEventDetails, or from MySql?

You could also open the text file you just made....eg:

Set ts = fs.OpenTextFile(CurrentProject.Path & "\newshow.csv")

I am thinking that since you can use dlookup, or open the text file you just
made with such ease, that your question *does* center around needing to open
the file from the web server. And, if this is the case, you can't "open" the
file OVER the internet..you have to transfer it (eg: use ftp, or have the
web site build you something that can send back a text file). Like in say,
since dlookup, or opening the text file is so easy, me thinks you need a
number generated by the web site...

So, a odbc connection to mysql would eliminate both the transfer of text,
and also give you the ability to read/use the mysql tables in ms-access
directly. Barring this, then you need to have the web site send you back a
text file if you need the id from the web server. And, if it is the ID from
ms-access you need, then you can well grab that from the above dlookup, or
open the text file.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Albert,

Well, that simply means that the web site has a upload ability, and
software to read
the csv file on the server side. However, really, connecting to the
mysql database would be far better solution.

Right - the software is the PHP script that I wrote to do this. Connecting
to the database is not an option because the web site requires the IP
address be white listed and these are dial-up users whose IP address changes
every time they connect. As I've said I'm already doing it by connecting to
the database but this only works for cable and DSL users so I'm trying to
come up with a solution for dial-up users.

The ID that I'm trying to get back from the server is so when I send the
next set of files they will have the record ID inserted so the various
tables can be related. This is just a number and it would seem that I could
show it on the browser window (which I'm doing) and then do a screen scrape
in the Access program.

Vic

This is just a number and it would seem that I could show it on the
browser window (which I'm doing) and then do a screen scrape in the Access
program.


You can consider using the Winsock control. I have used it before, and it
works with ms-access. That way, you could use a get (and even puts) from
inside of ms-access.

And, my conclusion was right..(whew!!). So, consider using ftp to download
the file. A cleaner solution would be Winsock, and a web page that returns
the id.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Thanks Albert - I appreciate your help ...

Vic

Albert,

As I suspected there was a simple solution to this problem. All I wanted to
do was pass a number (MySQL recordID) back to the Access client and it turns
out to be very simple with a few lines of coded added to the PHP script.

$sql = "select ShowID FROM showinfo WHERE ShowName = '$showName'
AND ShowCreator = '$creator'";
$result = mysql_query($sql) or die(mysql_error());
$selectedRow = mysql_fetch_row($result);
if (isset($selectedRow))
{
$showID = $selectedRow[0];
}
// Now send the recordid to the client
$handle = fopen("/Program Files/Show Program/ShowID.txt","w");
if (fwrite($handle, $showID) === FALSE) {
echo "Cannot write to file - Assigned ShowID is ($showID)";
exit;
}

The folder where the file is to be saved requires write permissions.

I just wanted to document this in case someone else is trying to accomplish
the same thing.

Vic




"Albert D. Kallal" wrote in message
...
This is just a number and it would seem that I could show it on the
browser window (which I'm doing) and then do a screen scrape in the
Access program.


You can consider using the Winsock control. I have used it before, and it
works with ms-access. That way, you could use a get (and even puts) from
inside of ms-access.

And, my conclusion was right..(whew!!). So, consider using ftp to download
the file. A cleaner solution would be Winsock, and a web page that returns
the id.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada