Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 2 / May 2008

Tip: Looking for answers? Try searching our database.

Data capture via online form

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
David Anderson - 14 May 2008 13:48 GMT
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
Wayne-I-M - 14 May 2008 13:56 GMT
No - asp will do it for you.  (use the wizard).

Create the form as you need.
Save the form to a databse (use the wizard) "keep it small"
Open the on-line DB and set the formats

As and when you need just import this data into an off-line DB via a temp
table and update query.

Signature

Wayne
Manchester, England.

> I have created an Access 2003 database solution for an annual international
> photographic competition. In it's first year of operation, all the entry
[quoted text clipped - 17 lines]
>
> David
David Anderson - 14 May 2008 14:11 GMT
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

> No - asp will do it for you.  (use the wizard).
>
[quoted text clipped - 32 lines]
>>
>> David
Wayne-I-M - 14 May 2008 14:40 GMT
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 !!)

Signature

Wayne
Manchester, England.

> Hi Wayne,
> I'm a bit further down the learning curve that you have assumed. Where do I
[quoted text clipped - 39 lines]
> >>
> >> David
David Anderson - 14 May 2008 15:07 GMT
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

> Which web design programme are you using - frontapge is really good with
> access (and has lots of wizards).
[quoted text clipped - 67 lines]
>> >>
>> >> David
Albert D. Kallal - 14 May 2008 13:56 GMT
"David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message

> 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....

Signature

Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

Wayne-I-M - 14 May 2008 14:14 GMT
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

Signature

Wayne
Manchester, England.

> "David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message
>
[quoted text clipped - 18 lines]
> Anyway, since this is not an MS access project, then you're in the wrong
> place and in the wrong newsgroup....
Albert D. Kallal - 14 May 2008 14:57 GMT
> Hi Albert
>
[quoted text clipped - 3 lines]
> 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....

Signature

Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

David Anderson - 14 May 2008 15:21 GMT
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

> 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
[quoted text clipped - 13 lines]
> programming language we have in MS access to create a web based
> application, because that's simply not the case....
Wayne-I-M - 14 May 2008 15:39 GMT
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.

Signature

Wayne
Manchester, England.

> > Hi Albert
> >
[quoted text clipped - 20 lines]
> programming language we have in MS access to create a web based application,
> because that's simply not the case....
David Anderson - 14 May 2008 17:51 GMT
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

> 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).
[quoted text clipped - 51 lines]
>> application,
>> because that's simply not the case....
David Anderson - 14 May 2008 14:22 GMT
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

> "David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message
>
[quoted text clipped - 18 lines]
> Anyway, since this is not an MS access project, then you're in the wrong
> place and in the wrong newsgroup....
Albert D. Kallal - 14 May 2008 15:08 GMT
> 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.

Signature

Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

David Anderson - 14 May 2008 15:39 GMT
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

>> Are you suggesting that MS Access cannot form any part of my solution
>
[quoted text clipped - 40 lines]
> will be done through the web based design and programming tools, not the
> MS access design tools.
Albert D. Kallal - 14 May 2008 16:23 GMT
> Albert,
> I think I probably knew already that Access would not help me to create
[quoted text clipped - 5 lines]
> 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...

Signature

Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

David Anderson - 14 May 2008 17:30 GMT
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

>> Albert,
>> I think I probably knew already that Access would not help me to create
[quoted text clipped - 28 lines]
> 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...
Albert D. Kallal - 15 May 2008 01:10 GMT
> 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...

Signature

Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

, you don't incur monthly charges to do that

David Anderson - 15 May 2008 09:56 GMT
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

>> Is there any reason why the free version, SQL Server Express
>
[quoted text clipped - 70 lines]
> 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 - 15 May 2008 12:00 GMT
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.

Signature

Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

David Anderson - 15 May 2008 17:47 GMT
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

> 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
[quoted text clipped - 75 lines]
> 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.
Albert D. Kallal - 15 May 2008 21:31 GMT
"David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message

> The programming language I associate with a website is HTML but I suspect
> that you are referring to PHP or ASP. Is that correct?.

HTML is not a programming language. You can't declare variables, run loops,
and
call subroutines. It is ONLY a display system. (they could have used word
for the web, but they used HTML). So, HTML is how you set your fonts size
and display text on a web site, but you still have to write code to
generate
that HTML (or, just like a word doc, or PDF, or HTML, you can have a static
web site that don't run code). Remember, word has a "save as HTML" option
built in. You would not say that when you "save as HTML" in word you now
generating computer code.

> 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.

Yes, that is a good idea. if you had SharePoint running, that would be
another good canadate here...

Signature

Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

David Anderson - 15 May 2008 23:50 GMT
Albert,
I tried to read a few of Microsoft's web pages describing SharePoint. There
was a lot of enterprise level marketing guff about all the miraculous
benefits but very little to help you understand exactly what it is and where
the software should be installed and by whom. Microsoft also appears to sell
several different products all using the SharePoint name, which adds to the
confusion. One of those products is 'Microsoft Office SharePoint Server 2007
for Internet Sites'. Is that the relevant one in this context?

Because this software runs under Windows Server 2003 and is associated with
the Internet, I assumed that that this is another service you could buy from
an ISP for a monthly fee (like SQL Server). However, I have checked the
websites of three different ISPs and none of them mention SharePoint. If
SharePoint is only relevant to users with their own web server then it would
certainly be much too expensive for me to consider.

If I were to go down the SQL Server route, which is looking more likely, do
you know any good sources of scripts that would assist me to extract
information from an online form and store it in an SQL Server database?

David

> "David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message
>
[quoted text clipped - 21 lines]
> Yes, that is a good idea. if you had SharePoint running, that would be
> another good canadate here...
Albert D. Kallal - 16 May 2008 00:50 GMT
> Albert,
> I tried to read a few of Microsoft's web pages describing SharePoint.
[quoted text clipped - 5 lines]
> SharePoint Server 2007 for Internet Sites'. Is that the relevant one in
> this context?

Yes. MOSS as it often called is what you would be looking at. I recommend
SharePoint just in case you/they had it running already (it would be a
crying shame for me to give you all this information, and you had a share
point server sitting around that was available for your use).

You can also look at and try office live, it is free right now:

http://www.officelive.com/default.aspx

> If SharePoint is only relevant to users with their own web server then it
> would certainly be much too expensive for me to consider.

There are companies offering hosted share point systems.

> If I were to go down the SQL Server route, which is looking more likely,
> do you know any good sources of scripts that would assist me to extract
> information from an online form and store it in an SQL Server database?

Those so called script or programming languages is a decision that you make
yourself. Just like when I run a computer, I can choose to use MS access,
SQL server, FoxPro, or Sybase, Filemaker etc.

I can use c++, vb, vb.net etc to write code in. You have the same wide range
of choices when you write and build software on a web site. You have as many
variety of programming languages and web servers that you can choose use
just like on the desktop. You just have to choose one particular programming
language (likely the one that your most familiar with, or one that suits
your style of development).

I think in your case your best bet would be visual studio and .net. There
are free editions available for download.

Signature

Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

David Anderson - 16 May 2008 12:39 GMT
Albert,
> I recommend SharePoint just in case you/they had it running already (it
> would be a crying shame for me to give you all this information, and you
> had a share point server
> sitting around that was available for your use).

We are an amateur photographic society that happens to run an annual
international photo competition. We have no servers of any kind.

> You can also look at and try office live, it is free right now

I had never heard of Office Live before, so thanks for that suggestion. It
appears to offer a simple website generation tool and some business services
based on SharePoint. I've signed up for the Small Business version but have
not yet found an Access related facility (it must be in there somewhere
because I viewed a Microsoft video on Office Live that makes a passing
reference to Access). I will keep looking....

> I think in your case your best bet would be visual studio and .net. There
> are free editions available for download.

When I asked you for any good sources of scripts that would assist me to
extract information from an online form and store it in an SQL Server
database, I was really meaning pre-written scripts rather than suggestions
for languages in which I could write my own. For example, at the link
http://mindpalette.com/scripts/index.php#PFAnchor, you will find a PHP
script called ProcessForm that can take info from an online form and store
it in a MySQL database. I was hoping to find something similar for a Windows
hosting environment that connected to SQL Server. I've found a few sources
of ASP scripts so maybe I'll find something suitable there.

I'm trying to put off the day when I have to learn all about Visual Studio
and .Net. Access itself swallows enough of my time as it is!

Best regards,
David
David Anderson - 16 May 2008 23:52 GMT
Despite what I said at the end of my last post, I've now downloaded
Microsoft Visual Web Developer 2008 Express Edition and SQL Server 2005
Express Edition and I'm working my way through a 1 hour training video
entitled 'Connecting your Web Application to SQL Server Express Edition'
(http://www.microsoft.com/sql/editions/express/learnvideos.mspx#lv2). It
looks like I might be getting somewhere!
David Anderson - 19 May 2008 15:29 GMT
Albert,
I have now done some testing to prove that I could store data entered via an
online form in an SQL Server Express database. A SQL Server solution
therefore was looking promising, mainly because of my other wish to enable
the users in my own group to use this SQL Server database as a web-located
back end to my Access 2003 front end - an approach that you described below
as "a good idea".

However, I did have some concerns about response time in this scenario and I
asked questions about this in the sqlserver forum.
(http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/t
hread/514a46576a5f5492
)
I have had a response there that throws doubt on the wisdom of this
approach. Has your own experience with accessing a SQL Server back end over
the Internet been more positive?

David

> "David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message
>
[quoted text clipped - 6 lines]
> Yes, that is a good idea. if you had SharePoint running, that would be
> another good canadate here...
Albert D. Kallal - 21 May 2008 01:12 GMT
"David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message

> However, I did have some concerns about response time in this scenario and
> I asked questions about this in the sqlserver forum.
> >(http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/t
hread/514a46576a5f5492
)
> I have had a response there that throws doubt on the wisdom of this
> approach. Has your own experience with accessing a SQL Server back end
> over the Internet been more positive?

I have personally talked to some people that created workable performance
over a dialup modem with MS access to SQL server.

You certainly have to pay attention to the amount of data you transfer into
a form. However any good access developer will do that even when not using
SQL server.

For example if you have a table with 5000 records in it, you must never ever
open up a form bound to that table of that many records *without* using the
where clause to restrict the number of records loaded.

In the case of a web based application, what's the difference between
displaying a web page that gets transferred to your computer with some
fields of information to be displayed, as opposed to telling SQL server to
send one record down the wire, and you display locally? (The answers is very
little if any difference here and performance). The problem is you'll never
see that website display 5000 records in a webpage will you?

Time and time again I see MS access applications that simply have a form
bound to a large table, you open the form, and then you let the user have at
it with this large table with thousands of records. This type of design
approach is bad in MS access, or in ***any*** development environment. One
of the reasons why some many developers state that MS access doesn't perform
well is not because MS access is any slower than vb or whatever when pulling
data from SQL server, it's just that we tend to have poor designs that
transfer a bunch of records into a form, and then say the person OK search
the form.

SQL server doesn't know if you're using the vb.net, MS access, c++ etc, or
even if a web site that's pulling data from SQL server. The server don't all
of a sudden say "hey", this is MS access let's transfer data slower!. Things
just simply don't work this way at all.

I recall some time ago I asked an 88 year old grandmother if it makes sense
to download all the names into instant teller machine, and then have the
person enter their account number? Or, is it better to simply ask for the
account the data have the number first, and then download one account
number? This old lady was able to perfectly and crystal clear answer this
question correctly. If a old lady can answer this question, then why do I
see so many ms-access applcations that go against the advice of a old lady
who don't even work in IT?

So will the application perform well? the answers it depends, and if you
have a design that limits the number of records transferred into a form,
then just have no problem at all.

I think the more difficult problem to solve here is where you plan to put
the pitchers, since you can't put them inside of MS access, and they'll
likely be files placed on your web server. What this means is you have a
difficult challenge to have those pictures related or displaed inside of an
MS access form on the client side of this application.

Signature

Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

David Anderson - 21 May 2008 11:37 GMT
Albert,
I can't thank you enough for all your assistance. You are shedding a great
deal of light on what is an unfamiliar topic for me. Because of comments
elsewhere I was getting very worried about possibly spending a lot of time
on converting my current Access/Jet app and then finding that response time
over an Internet connection made it unusable. You are now offering me a
clear focus for my upsizing work so as to make the performance acceptable.

I have been faced with the difficult decision about whether to pursue an
Internet-connected SQL Server solution or replication via the Internet. The
trade-off is between always having only one version of the data tables
versus response time. Since either method would take me a lot of time to
implement, my worst fear was going with SQL Server and then being forced to
change to replication and thus taking twice as long to reach a workable
conclusion! However, you have now given me the confidence to pursue an
Internet-connected SQL Server solution.

I will now have to start the process of converting my Access mdb front end
into an ADP and checking that I make full use of 'where' clauses. I will
probably start by testing with SQL Server Express on my own PC before
signing up for a Windows hosting agreement with SQL Server 2005. What about
security with such a solution. Do I also need to learn all about VPNs?

BTW, there is no issue with the storage of digital images as it was never
the intention to display the pictures on the website. The online entry form
is intended to take the place of our previous paper entry form, which was
posted to us together with the related prints or digital images on a CD. We
will now have to design a process for linking the online entry form with the
images received via other channels. We do have to consider ways to make it
easy for digital images to be submitted electronically but I have not begun
to think about that yet (though I will look into your earlier suggestions
ref Flickr, etc).

Thanks again,
David

> I have personally talked to some people that created workable performance
> over a dialup modem with MS access to SQL server.
[quoted text clipped - 47 lines]
> difficult challenge to have those pictures related or displaed inside of
> an MS access form on the client side of this application.
Albert D. Kallal - 22 May 2008 05:31 GMT
"David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message

> I will now have to start the process of converting my Access mdb front end
> into an ADP

No, you don't have to convert to a adp. You *can* do so, but I was
suggesting you don't re-write your code, and you simply use linked tables.

if you have very little coding, or at least very little reocrdset code, then
by all means consider a ADP, but you don't have to change to a adp, you
simply use linked tables to sql server....

There's no question that with less effort, an ADP will generally performed
better, especially when you're dealing with queries that join multiple
tables together. Other than some sql performance issues, for the most part
however it's not usually worth the effort and time to convert a well made
and well done application from a mdb to a ADP.

Signature

Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

David Anderson - 22 May 2008 09:18 GMT
Albert,
This is such a new subject for me that I didn't realise that using linked
tables with SQL Server would remove the need to convert to an ADP. That
would certainly be worth trying as I would get a feel for any performance
issues much more quickly that way. I would still have to focus on the use of
'where' clauses but that is much less work than rewriting all my DAO code.
If, after that, performance was still poor I would then have a better reason
to invest the effort in an ADP rewrite. Thanks for that excellent
suggestion.

What about my security concerns? Is this a situation where I need to exploit
a VPN, yet another topic where I have little or no knowledge and experience?

I also asked about the the viability of connecting to SQL Server via the
Internet on another forum. I've had very little response but one person has
just told me that most ISPs will only allow you to connect to SQL Server
from within their own environment, i.e. via a website they host for you, and
will not expose SQL Server to the Internet. In other words, SQL Server will
be hidden behind the ISP's firewall. What is your reaction to this comment?

David

> "David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message
>
[quoted text clipped - 13 lines]
> however it's not usually worth the effort and time to convert a well made
> and well done application from a mdb to a ADP.
Wayne-I-M - 14 May 2008 14:06 GMT
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";

Signature

Wayne
Manchester, England.

> I have created an Access 2003 database solution for an annual international
> photographic competition. In it's first year of operation, all the entry
[quoted text clipped - 17 lines]
>
> David
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.