sharing info between Access and a website

[JamesDeckert]

Information about interaction between Access and the internet seems to be
sparse. I'm not wanting to put the back end on the net. What I'm wanting to
do is share information between my Access system and a website (probably as a
batch process once a day). I've some stores who use my system, and another
company is developing a web based system open to customers to update their
orders. I'd like to download those changes into Access, and vice versa (when
a store makes changes).

I've found an http possibility for receiving data, and some ftp code (which
I haven't tried yet) for downloading/uploading. But my question is, is there
a "best" process for this kind of thing. Also, the site may want security
verification (user name, password). Is there some way to "post", or talk back
and forth briefly "real-time" between the two systems?

I envision the files being shared to be small (a few K) XML/text/CSV type
files.

Of all my Access books, none discuss sharing info with the internet.

Thoughts? Ideas?

thanks,
James Deckert

 

[Mr B]

James,

I have recently developed a system for a client where their end user logs on
to a web site that I developed using asp.net to view (read only) data
specific to that user. The data is stored in an Access 2003 format database
file. This stroage file is actaully created by users in my client's office
using another Access 2003 Application that utilizes a split file design.

The users at the clients office make the needed modifications to the data
and then when they have completed their modifications, they click a button to
initiate code that creates a special .mdb file containing the needed data for
a specified end user. The user is then prompted if they would like to have
this special .mdb file uploaded to the server. The file is uploaded to the
server via FTP, replacing the existing file.

In our application we are not allowing the end users to make any
modifications to the data, thus we have no need to retrieve any existing data
before the upload process. If you plan to have your processes run in off
hours (when users are not activly using the web site) then you could actually
download a copy of the exiting database file using FTP, do what ever
processing you need to do to acquire any updated data. After downloading the
current data, you could then at some point upload any new data or records or
whatever.

You might actully want to at least consider moving the entire process to
asp.net. Using asp.net you can maintain any and all data in real time
without having to have the uploading and downloading with FTP going on.

These are just some of my thoughts on the subject, but I hope that some of
these thoughts help you in trying to find a solution to your project.

If you have questions or comments concerning the asp.net thing, you can
email me from my website shown here below my signature.

 

[JamesDeckert]

Thanks for the input. Your scenario is similar to what I want to do.

If I was doing the entire job I'd go with Asp.Net, but I'm not developing
the website software. It's another company which is developing their own
software for their own purposes. It just so happens that we are using the
same kind of info and it would be beneficial to both companies to "trade"
info back and forth. The website is mostly updated by end users with a store
being able to see the info. My software is updated by stores only keeping
track of customer orders. Optimally I'd like to synchronize changes between
the two databases.

The other company is thinking that there needs to be a login process with
user name/password similar to a "post" in HTML. I've not found anywhere a
discussion on if this is possible within Access.

Thanks again.
James Deckert

 

[Mr. B]

James,

As far as the login, if you are talking about through FTP, then, Yes, you
can and will need to provide a UserName and Password when you are uploading
or downloading from the FTP site using Access.

Feel free to email me direct relative to the uploading through FTP as this
is a little off topic for this discussion group. The address is: mrbpolk at
suddenlink dot net.

Mr. B
(askdoctoraccess.com)

 

[Sylvain Lafontaine]

The information is sparse because it's not a good idea to use Access as the
backend either for a web site or for replication/synchronisation or for both
(web + R/S). If it was a good idea, you would have found a lot of people
using this configuration and with a lot of information about this.

The best you can some is probably with a half baked solution like FTP-ing
some mdb files over the Internet (WAN) in a batch process - like the
solution provided by Mr B. - but even then, the simple fact of using Access
on a web server is not a good idea in the first place.

You can put money into developping such a solution but if it's for a
commerce and that you are expecting to make money from its usage, you should
go into looking for a more professional solution than copying mdb files over
the internet once a day.

There is no Easy button for doing database processing on the Internet but
people often ask for it.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Mr. B]

Sylvain,

Rarely do I ever voice any disagreement with any MVP. I have a great deal
of respect for all of you guys and certainly do not wish to raise any issues.

However, with that said, this is one time that I feel that I must voice my
disagreement with your optinion that it is not a good idea to use Access (Jet
enging) as a backend for a web site.

While I would agree that for enterprise wide or many use type websites it
would not be a good idea, I would just like to point out that under the right
conditions, the use of a Jet database (Access file) as a backend to a website
is very appropriate. I think this is evidenced by the face that amoung other
options, Microsoft has made the use of an Access database file available from
their newest web development tool, Visual Web Developer 2008 Express.

I want to be very clear that I would never recommend the use of an Access
database as the backend file for a high traffic website. However, I just
want to make others understand that there are situations where the use of
this type of backend file is not only acceptable but is actually quite
appropriate.

Again, Sylvain, sorry to have to disagree with you on this one.

Mr. B
(askdoctoraccess dot com)

 

[Sylvain Lafontaine]

The question here is not only about you (your database and your website) but
also about other people using the same web server for serving their own web
site. Today, most web servers are heavily used with many hundreds,
thousands or more requests per minute and the simple fact of accessing the
MDB file will have some serious impact on the global performance of the web
server for serving any site. Access/JET have been designed to be used on a
Desktop (or Workstation), not a Server. Here the very own advice from MS:

http://support.microsoft.com/kb/299974

Of course, you can say - or it might be true - that this is only about you
and your web site - for example, when your web site is the only one on the
web server and that you don't need to have the best performance but even in
these cases, it's probably still a bad idea to use JET in most case
scenarios and not only for the stability - the possibility of corruption -
of the database.

First, about every time you collect information from someone and
particularly when this is commercial and business information - you need a
safe solution for backuping and recovering your data in case of a database
corruption (either software or hardware, for example from a disk failure).
Simply put, there is no way to do this with a JET database file on a web
server in a safe way at a safe interval. The best you can do is probably to
copy it once or twice a day while praying that the database file is not open
at that time. In the case of a failure, in the best case scenario, you'll
lose your entries for that day; in the worst case scenario, the database
file was opened when the copy was made and you'll find yourself with a
corrupted version of the database file; with impredictable consequences.

Second, unless it's some sort of guestbook that you have found the code
somewhere, your application will evoluate with time and you'll need to bring
change to the schema (design) of your database; while knowing at the same
that there is probably new data on the web copy so you can't just copy the
new version over the old one. Again, you can't do this easily with a JET
database file. Of course, you can choose to shut down your application,
copy the file, make the updates, copy back the file and restart your
application but for a commercial application, this lacks a lot of finish and
enven if you choose to do so, the etape of updating the changes will
probably prove itself to be much more difficult and error prone that you
might have thought in the first place.

Like I said in my first post, if it was a good idea to use JET on a web
server, you would see a lot of people using this solution with a lot of
information about it.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)