Data capture via online form

[David Anderson]

I have created an Access 2003 database solution for an annual international
photographic competition. In it's first year of operation, all the entry
forms were paper-based. With well over 6000 images submitted by nearly 800
entrants, we had a major data entry task to complete. For the 2009
competition we would like to gather all this information via an online form.
That way, all the entrants do the typing rather than us and they can't then
complain if we spell their names, addresses and image titles incorrectly! We
also want to incorporate payment via PayPal.

My problem is that while I have some website design skills, I have never
designed any online forms and little or no knowledge on how best to link
such forms with an Access database. I also have a limited time to get this
up and running (the target is Sept 2008).

I would appreciate some guidance on the best approach to this task. I'm
running Access 2003 under Windows XP Professional and also have Microsoft
VSTO 2003. Do I have to start learning all about ASP, IIS and a bunch of
other unfamiliar acronyms or are there any commercial online forms design
tools that would simplify the job?

David

 

[Albert D. Kallal]

Do I have to start learning all about ASP, IIS and a bunch of other
unfamiliar acronyms or are there any commercial online forms design tools
that would simplify the job?

Unfortunately MS access has little to do with the web. you're pretty much
gonna have to use some web based technologies to develop the system, and MS
access is not going to help you.

I would suggest you consider using some of the web based design tools,
either FrontPage, or visual studio and asp.net.

I suppose you could also consider using one of the many online photo
systems like flicker or whatever.

You could also perhaps do this with facebook, and have people join face book
and become a group of friends, and that way they can each post their own
photos into their own photo albums.

Anyway, since this is not an MS access project, then you're in the wrong
place and in the wrong newsgroup....

 

[Wayne-I-M]

Sorry just re-read your post.

Go to the pay-pal site and open the merchant section. Fill in your details
then go to "create the button" page. The code will be written for you. Just
paste in the script where you need it on your form.

Note - it will take a few weeks to set up the pay-pal account to work OK.
They will send a small amount to an account then you need to fill in another
form to verify this amount.

You should "not" use the asp form to upload the pictures. It is best to
link to them (if they are online) - format a field as a hyperlink. If not
then you need to find another method of loading them, or your DB will get to
a silly size "very" quickly. I would suggest than you only take scanned
pictures up to a certain limit - use another form for this to be emailed to
you (outlook is good for this).

Something like this (which will open your default mail programme)

<script language=javascript>
<!--
var AandB = "some";
var CandD = "person";
var EandF = ".";
var GandH = "name";
var IandJ = ".";
var KandL = "com";
var linktext1 = "Se";
var linktext2 = "nd";
var linktext3 = " e ";
var linktext4 = "M";
var linktext5 = "a";
var linktext6 = "i";
var linktext7 = "l";
var subj = "Picture attached"
var bod = "Attach your picture here."
document.write("<a href=" + "mai" + "lto:" + AandB + "@" + CandD + EandF +
GandH + IandJ + KandL + '?subject=' + escape(subj)+ '&body=' + escape(bod)
+">"
+ linktext1 + linktext2 + linktext3 + linktext4 + linktext5 + linktext6 +
linktext7 + "</a>")
//-->
</script></td>

Use this instead of your e mail address or you will get LOTS of spam very
quickly

- Of course change this to your real (broken up) e mail address "Don't" use
the @ sign in this code or you will be spamed.

var AandB = "some";
var CandD = "person";
var EandF = ".";
var GandH = "name";
var IandJ = ".";
var KandL = "com";

 

[David Anderson]

Hi Wayne,
I'm a bit further down the learning curve that you have assumed. Where do I
find this thing called ASP and it's associated wizards?

Regards,
David

 

[Wayne-I-M]

Hi Albert

Access works fine as an on-line database (we have 7 sites using access to
collect form results that users input)

As long as you're OK with access and have just a little web design ability
its not difficult

 

[David Anderson]

Hi Albert,
I forgot to mention it, but you correctly assumed that I would also want my
online form to provide a facility to upload digital images. We also accept
prints but it's a little trickier to squeeze them into a PC....

Are you suggesting that MS Access cannot form any part of my solution and
that I have to redevelop my entire application from scratch using different
web based design tools? Would it make a difference if I limited my
objectives so that the online data capture was a purely one-way process,
i.e. with no interaction with the database while the form is being filled
out (with the resulting information being imported into Access at a later
date)?

Regards,
David

 

[Wayne-I-M]

Which web design programme are you using - frontapge is really good with
access (and has lots of wizards).

I have just made a page to show you an example

http://www.folgarida.co.uk/sample_form.asp

I will delete this today so please copy the code if you want to as it will
not be there long

I have used the names
Wayne
Albert
David

Note there is no send button on the form as I don't want the information
(but you will !!)

 

[Albert D. Kallal]

Hi Albert

Access works fine as an on-line database (we have 7 sites using access to
collect form results that users input)

As long as you're OK with access and have just a little web design ability
its not difficult

You're absolutely correct, but on the other hand you're confusing the
absolute hell out of this poster because they don't really understand the
difference between the jet database engine and MS access the design tool.

You can most certainly use a jet based database engine on that web site, but
you do not have to install MS access on that web site to use get, and I'm
willing to bet bottoms to dollars, ms-access is in fact not on the web site.

That person will STILL have to adopt some web based design tools. There is
not a whole lot of extra time saving or efforts by using a jet back in this
case.

I'm really just try to stress to the end user, and not get their hopes up
that they're somehow going be able to use the forms, reports, and
programming language we have in MS access to create a web based application,
because that's simply not the case....

 

[David Anderson]

Wayne,
Thanks for taking the trouble to make the sample form (and the advice ref
disguising email address).

I have got a copy of FrontPage 2000 that I use to maintain one website, but
one or two more recent sites were created using Adobe GoLive CS2. I will
probably update my copy of Adobe's Creative Suite to the CS4 version when it
comes out and I will then gain access to Dreamweaver. However, I would not
have a problem in updating my copy of FrontPage if it was going to provide
the easiest way to code links to Access.

David

 

[Albert D. Kallal]

Are you suggesting that MS Access cannot form any part of my solution

Well access can form part of your solution, but the part that access
contributes is not worth anything to you at all.

Many web providers allow you to palce/put the mdb file on a web site, and
you can write web based code to use data from that database. There's a
significant distinction between that of the jet database engine that most of
us use with a MS access, and that of the MS access design tools.

As the other poster pointed out, you can use the backend mdb file on the web
based solution, but any of the code, forms, and reporting system is not
going to work through the web site at all. In other words, the only thing
you really get by doing this is some tables on your website, but none of
your coding or design tools can be used to create forms or reports for that
web site.

that I have to redevelop my entire application from scratch using
different web based design tools?

Yes, the above is pretty much the case. Since you're not really using the MS
access design tools in this case, there's little if any advantage to using
the jet back end mdb file, or that of using SQL server. We're talking about
a data repository here. Remember, when you use the jet database engine (the
one we use with ms-access), or you use SQL server, in both cases you're not
able to create forms or reports with that database engine. That data engine
(JET or sql server) is only a place where the tables reside, and then you
can use visual basic, c++, your web based tools, or even MS access to
extract the data from those tables.

If you go to a web based solution, then you can use the backend mdb database
for the web site, but you can not use any of the front and tools to build
and design forms and a user interface in MS access for the web.

At the end of the day this really means that you really don't get to use any
of the programming and design tools in MS access to help you build that web
site, and therefore there is little if any advantage to using the jet
database engine for a web based site.

MS access is simply the wrong tool and inappropriate in this case. You can
as the other posters suggested find some web providers that allow you to
place the mdb data file on that web site, but any interface to that data
will be done through the web based design and programming tools, not the MS
access design tools.

 

[David Anderson]

Albert,
I'm aware of the distinction between Jet and the Access design tools and
some years ago I played around with MSDE as an alternative back end.
However, you are right to assume that I am unclear on what software would
need to be installed on a website in order to facilitate a database enabled
online form. Nor do I know if I need to ask our ISP for some additional
services to make it all work.

David

 

[Wayne-I-M]

You are correct that it not on the sample form - (now deleted) - but there
are many DB's my sites we have (I made the html, asp and access sections). I
can't give you the address here (that would be a big mistake on a public
forum).

David can I make one suggestion that Albert has refered to. My 1st answers
assumed you had some expeience in creating these projects so I would suggest
that as it has to work 1st time. Get someone to make it for you.

I know thats not the idea of these forums (learning is grea if you have
time) but in this case the time your would speand learning would be less than
the cost involved. It's quite a simple thing your looking "if" you know how
to do it. If not, it will take you quite a long time and it will need a
great deal of testing. There are many many people around (not me by the way
- I am not touting for work) who would only charge a small amount to do this
for you.

 

[David Anderson]

Albert,
I think I probably knew already that Access would not help me to create the
online form itself. That's what I assumed ASP, etc, would facilitate. I'm
not expecting my existing Access database to run online. Instead, I was
envisaging that a new mdb file might be created for use on the website - a
mdb file that simply recorded the data entered via the online form. I then
imagined that I would find some way to extract the tables from this mdb file
and import them into my main Access database, residing on my own PC.

I was also hoping that data in the tables within the web-based mdb file
might be used for various validation purposes in the online form, though I
appreciate that such coding cannot be done in Access.

David

 

[Albert D. Kallal]

Albert,
I think I probably knew already that Access would not help me to create
the online form itself. That's what I assumed ASP, etc, would facilitate.
I'm not expecting my existing Access database to run online. Instead, I
was envisaging that a new mdb file might be created for use on the
website - a mdb file that simply recorded the data entered via the online
form. I then imagined that I would find some way to extract the tables
from this mdb file and import them into my main Access database, residing
on my own PC.

To be fair, in this situation if I had a fairly rich application with all
kinds of reporting abilities etc, and want to salvaging keep that
application running, but integrate it with a web based system, then what I
would do in this case is move the backend data to SQL server, and place that
on my website.

I would then designed a web based system to intervista SQL server, and if
that point you'll have both a web based system part, and your MS access part
can pretty much wealth function as it does now, except that the data for the
backend would be from SQL server, and not a jet based mdb file.

in theory if you're running your own machine in your own web based server,
then you could perhaps connect directly to the back end mdb file at the same
time the web site is using this file, but for the most part it's not the
really recommended approach. Since the chances are not good that you be
hosting a web site, then you really don't have a way to connect to the mdb
file over the Internet, and that's why I'm suggesting SQL server.

So there's no question that you can continue to use your application by
simply moving the backend data to SQL server, because SQL server works well
thought web site, and can both the web site + you + ms-access can connect to
the database egine at the same time in this case...

 

[David Anderson]

Albert,
SQL Server does have some appeal because I could then have several people
using my main Access application from their own homes and sharing a common
data repository. However, as a non-profit organisation, we cannot afford to
spend about 1,400 GBP on SQL Server Standard Edition. Is there any reason
why the free version, SQL Server Express, should not work in this scenario
while also being accessed by an online form application? The database size
limit of 4GB is unlikely to be an issue (we're not going to be storing any
digital images in the database).

I seem to remember looking into this a year ago and hitting some problem
with ISPs being prepared to support the full version of SQL Server (for a
fee) but not being interested in supporting SQL Server Express, thus
limiting the free product to use on your local network. Is this also your
understanding?

David

 

[David Anderson]

Wayne,
It's a valid suggestion, but I think I would like to persevere a little
longer in pursuing a solution I can code myself. I don't mind a bit of an
intellectual challenge. Given that this is Day 1 of my project, my first
objective is to find out the most effective ways of achieving my objective.
I will then look at the recommended process more closely to see if I think
it is within my capability.

David

 

[Albert D. Kallal]

Is there any reason why the free version, SQL Server Express

A great choice, and I never really made the distinction between 'express',
and the corporate edtiion. The express edition now about as good as the full
paid version was some years ago.

I seem to remember looking into this a year ago and hitting some problem
with ISPs being prepared to support the full version of SQL Server (for a
fee) but not being interested in supporting SQL Server Express, thus
limiting the free product to use on your local network. Is this also your
understanding?

Most ISPs often charge a little bit more to host SQL server. It's not the
fact that the database engine is free as much as it's simply that you're
using extra processing bandwidth, memory and resources of the server. I
don't think it really ever was they will not charge to use sql server
express vs that of the corporate edition.

If you're not hosting your own web site with your own server that you've
purchased (which would mean that you can put anything you want on the
server), then you don't have much choice. The cost of having SQL server per
month by some ISP's is so dirt cheap as to not really change the cost of
your monthly bill from that provider anyway.

I do know that some ISPs did say they don't want to use SQL server express
because it does not scale well in their server farms (supposedly uses too
much CPU, and this may actually be true, because you're not going to use of
sql express in a server firm to service 200 companies each with 20
employees). Thus, the ISP can't hang fifteen to twenty customers off of one
box if you're purchasing a virtual server from them and using express. A
virtual server means that have your own copy of windows, and can install +
run just about anything you want on that box. However if you start
installing things that gobbles up too much processing time, they're not
gonna be happy with you. on the other hand which edition of Internet
services are you planning to use now anyway? (are you actually choosing the
type of server, and software you going to run on the server at this point
time, or simply leaving the choice up your ISP).

In place of purchasing a virtual server, you can also purchase a hosted box.
Thus you can add additional hard drives, install more memory, and install
any piece of software you want on that server. You won't incur additional
charges because you're actually paying for the whole box, it's just not on
location at your business anymore. (and they often provide a dialup phone
line for when the internet is down so you can dial up into the server to do
things like reboot the server, and do offline maintenance).

It's not clear if you're going to purchase and set up a server at your
business location, and simply hook it up to the Internet. Again, in this
scenario you can install and run any piece of software you want without
additioal charges.

The advantages of having your own complete hosted box is you can do whatever
you want to that box, and often when you add additional things like more
memory or additional disk space you can do so without incurring a monthly
additional charges. On the other hand, this also means you'd better have
somebody who has experience running and setting up a server and Internet
services.

A virtual hosted server is a little bit cheaper, because in that case you
still have a full computer and operating system (you can log into), but
you're sharing your hardware and memory with other people.

And of course the most common is simply that you get a hosted web site and
some database space, and how that shared is not exactly clear in my mind,
but you are sharing that resources with other people, and this is usually
the cheapest monthly solution you will purchase from a provider (and for
people that simply purchase a cheap monthly website space, this is what most
of them use).

You should contact your current ISP as to what kind of services they offer,
or perhaps find someone who can help you work through these issues and
ideas. This MS access group is certainly not the right place, and
unfortantly ms-access don't have much to do with the web...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)


, you don't incur monthly charges to do that

 

[David Anderson]

Albert,
Thanks a million for your extremely helpful reply. Despite having a
background in IT, the whole topic of web hosting is outside my personal
experience and I have much to learn before it all makes sense to me. I
realise that this discussion is no longer relevant to this forum but your
answer has been more useful than I got a year ago when I asked about SQL
Server Express on the relevant MSDN forum!

I will seek advice elsewhere as you suggest (I have already emailed my ISP)
but perhaps you could first clarify some remaining points. My current
understanding is that some version of SQL Server is installed by me on the
webspace provided by an ISP. That ISP then charges me an additional monthly
fee to cover the costs of my presumed increase in processing load on their
server. If that is correct, then I didn't quite understand your comment
"....because you're not going to use sql express in a server firm to service
200 companies each with 20 employees". That comment seems to imply that it
is the ISP who buys and installs SQL Server and customers then pay for a
share of their SQL Server facilities. Both scenarios cannot be true, so
which is it?

My requirement is associated with a non profit organisation, so we would be
looking for the cheapest available solution that does not require us to have
a lot of specialist technical skills. I guess that this would be the virtual
server option from an ISP. We were not considering the option of buying our
own web server. You asked about which edition of Internet services we were
planning to use. I presume you mean software called Microsoft IIS. Is that
something I need to learn about or can I leave that to the ISP?

David

 

[Albert D. Kallal]

Of course another possibility is to use share point services, and open up a
place where users can drop their files on the server. (and, with access
2003, or 2007, you can place your data on sharepoint also).

but perhaps you could first clarify some remaining points. My current
understanding is that some version of SQL Server is installed by me on the
webspace provided by an ISP.

No, you don't install sql server your self. In most cases they sell you a
montly hosting package that includes SQL server as part of a monthly fee. In
some cases you'll find that some providers offer linux based hosting, and
you get MySql in place of Microsoft's SQL server. In many cases you thus
actually get to choose the platform and tools you're going to use or adopt.
For example my provider offers both linux and windows web hosting packages.
(don't confuse a hosted web site with a hosted box, or a virutal box you pay
for...all 3 are differnt).

I didn't quite understand your comment "....because you're not going to
use sql express in a server firm to service 200 companies each with 20
employees". That comment seems to imply that it is the ISP who buys and
installs SQL Server and customers then pay for a share of their SQL Server
facilities. Both scenarios cannot be true, so which is it?

Yes, it is the ISP that installs sql server for you (the only exception here
is if you're buying your complete own hosted box, or virutal box).
Furthermore, I don't think they really install it for you, they just *allow*
you to use the one that they have running already. When I used the term
"server firm", that was actually a typeo, and I meant to say "server farm".
However the meaning is actually the same in both cases! So, what I'm saying
is that if you need a couple hundred people to use your SQL server database
application, you can't use sql express because it does not scale well. The
express edition will max out the comptuer faster then the
Enterprise/corporate edition of SQL server will.

SQL server express cannot utilize multiple processors for example. So, the
express edtion is fine for 50 users no problem. However don't expect a SQL
server express to service 500 users. It's the difference between a car and a
truck, the car and the truck can go the same speed, but the truck is
designed to carry a heavier load.

This simply means that some ISPs don't want to use and installed SQL server
express on their boxes, because they can NOT hang a lot of users off of it.
(it starts gobbling up too much processing, and if you have a server with
four or eight processors in it, SQL server express can only utilize one of
the processors). The express edition just doesn't scale as well the big
version, so some of these providers simply don't want people running on
their boxes. However, as I just finished pointing out, in most cases you not
running a hosted server, but only buying web space and use of SQL server.
you actually don't install anything at all these cases.

My requirement is associated with a non profit organisation, so we would
be looking for the cheapest available solution that does not require us to
have a lot of specialist technical skills. I guess that this would be the
virtual server option from an ISP.

no, actually it would be the third solution I suggested, and that is simply
just a hosted web site, with some SQL server space thrown in there. For
example for one of my sites, I'm www.dotster.com

The have thier plans here:

http://www.dotster.com/hosting/plans/

For $5.95 you get 5GB disk space, 10 email accounts, and MySql for your sql
server.
It linux based, and it supports pearl + php for the programming lanauge.

The windows plans are here:
http://www.dotster.com/hosting/plans/windows

For $6.25 you get a plan, and I see they offer MySql *or* MsSql for that
price. So, for these low cost monthly hosting plans, you don't install any
software, you simply choose the services you want just like choosing food at
a restaurant.

And keep in mind as the other poster suggested, some of these companies will
also allow you to copy (upload) a mdb file to your web site and use that.
You of course then will be using ADO code in whichever programming language
you decide for that web site to read that data from the mdb file.

 

[David Anderson]

Albert,
I will read up about Sharepoint services to see if it is relevant for my
needs.

You made the statement "keep in mind as the other poster suggested, some of
these companies will also allow you to copy (upload) a mdb file to your web
site and use that. You of course then will be using ADO code in whichever
programming language you decide for that web site to read that data from the
mdb file". The programming language I associate with a website is HTML but I
suspect that you are referring to PHP or ASP. Is that correct?.

It seems to me that if I have to go to the trouble to find or write a script
that enables an online form to dump its data into a database I might as well
do it for SQL Server (rather than just an mdb file) so that I could then web
enable my whole application by using Access only as a front end.

Thanks again for so kindly providing me with all this assistance.

David