Data capture via online form

[Albert D. Kallal]

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

 

[David Anderson]

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

 

[Albert D. Kallal]

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?

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.

 

[David Anderson]

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]

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]

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/micr....server/browse_thread/thread/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

 

[Albert D. Kallal]

However, I did have some concerns about response time in this scenario and
I asked questions about this in the sqlserver forum.
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.

 

[David Anderson]

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

 

[Albert D. Kallal]

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.

 

[David Anderson]

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