Database for internet applications

I have a colleague who wants to build a web application that is expected to
have a fairly low number of web requests and have this site capture data in
a database. His heart is set upon Access as the back-end database.
Now, I'm happy to accept that Access is a great product, but I know very
little about it - I've always used SQL Server. My gut reaction is to
suggest that he uses SQL Server Express in preference to Access, but
unfortunately I don't know enough about Access to argue why it shouldn't be
used as a database behind a web application. I don't even know much about
the licensing model.
Could someone provide the details here (or persuade me otherwise)?
Thanks everyoneGriff
SQL Server 2005 Express Edition is free but limited to 4GB db size.If I
remeber well Access is limited to 2GB db size.
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl...
>I have a colleague who wants to build a web application that is expected to
>have a fairly low number of web requests and have this site capture data in
>a database. His heart is set upon Access as the back-end database.
> Now, I'm happy to accept that Access is a great product, but I know very
> little about it - I've always used SQL Server. My gut reaction is to
> suggest that he uses SQL Server Express in preference to Access, but
> unfortunately I don't know enough about Access to argue why it shouldn't
> be used as a database behind a web application. I don't even know much
> about the licensing model.
> Could someone provide the details here (or persuade me otherwise)?
> Thanks everyone
>|||On Oct 18, 6:18 pm, "Griff" <griffithsj_...@.hotmail.com> wrote:
> I have a colleague who wants to build a web application that is expected to
> have a fairly low number of web requests and have this site capture data in
> a database. His heart is set upon Access as the back-end database.
> Now, I'm happy to accept that Access is a great product, but I know very
> little about it - I've always used SQL Server. My gut reaction is to
> suggest that he uses SQL Server Express in preference to Access, but
> unfortunately I don't know enough about Access to argue why it shouldn't be
> used as a database behind a web application. I don't even know much about
> the licensing model.
Some notes here:
http://members.optushome.com.au/anandasim/asp.htm
You don't have to worry about licensing. Because when you use
Access .mdb as a back end database, you are not using an Access
license - you are using IIS + ADO/ADO.NET + mdb - Access does not even
need to be installed on the webserver.
One concern is .mdb is not 24x7 - you need to take it offline from
time to time to pack the database and back it up - on the other hand,
if you get the time slot and scheduled batch jobs running, you can
just grab the .mdb and put it in your pocket.
One thing to consider is if you are using a development IDE against
the Access .mdb will there be design time issues with controls and
connections working properly - I used to have those years ago. And Jet
is single threaded - when I had SQL that was half baked during
development, the whole web server would be unresponsive.
HTH
Ananda|||The salomic decission
Use SQL server as the datastore & msAccess as a FrontEnd for maintenance,
reports etc
For the solution you describe the only drawbacks of using Access as a
Back-end is that it has no built in maintenance plan,
must be "offline" for backup, can get corrupted.
Pieter
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl...
>I have a colleague who wants to build a web application that is expected to
>have a fairly low number of web requests and have this site capture data in
>a database. His heart is set upon Access as the back-end database.
> Now, I'm happy to accept that Access is a great product, but I know very
> little about it - I've always used SQL Server. My gut reaction is to
> suggest that he uses SQL Server Express in preference to Access, but
> unfortunately I don't know enough about Access to argue why it shouldn't
> be used as a database behind a web application. I don't even know much
> about the licensing model.
> Could someone provide the details here (or persuade me otherwise)?
> Thanks everyone
>|||There are no licensing issues with supplying data from an Access database
file to a web page.
There is some advantage in using an Access file to store the data, if you
need to enter, query, and report on the data from within Access.
It is not difficult for someone with experience in something like ASP or PHP
to design a web page where data is read from and/or written to an Access
database.
Access data is not really secure, so you need to ensure you handle the
security at another level. For example, last year I was able to download the
entire MDB from a computer hardware vendor because the database went
corrupt, and the error message showed the name and location of the file. In
this case, the db contained only information that was on the web (i.e. parts
and prices, not customers and orders) so no harm was done (though I did
notify them.)
If you just need to export the data from an Access table to a web page (e.g.
a product listing that needs to be re-exported whenever products or prices
change), you can also do that easily. Here's an example:
http://allenbrowne.com/AppOutputHtml.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl...
>I have a colleague who wants to build a web application that is expected to
>have a fairly low number of web requests and have this site capture data in
>a database. His heart is set upon Access as the back-end database.
> Now, I'm happy to accept that Access is a great product, but I know very
> little about it - I've always used SQL Server. My gut reaction is to
> suggest that he uses SQL Server Express in preference to Access, but
> unfortunately I don't know enough about Access to argue why it shouldn't
> be used as a database behind a web application. I don't even know much
> about the licensing model.
> Could someone provide the details here (or persuade me otherwise)?
> Thanks everyone|||One thing about Access as far as web applications are concerned is that it
is still a file. If I happen to know the physical path where the Access
database file is stored (a vulnerability exposed can do this), I can
download the database file from the site ;-)
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl...
>I have a colleague who wants to build a web application that is expected to
>have a fairly low number of web requests and have this site capture data in
>a database. His heart is set upon Access as the back-end database.
> Now, I'm happy to accept that Access is a great product, but I know very
> little about it - I've always used SQL Server. My gut reaction is to
> suggest that he uses SQL Server Express in preference to Access, but
> unfortunately I don't know enough about Access to argue why it shouldn't
> be used as a database behind a web application. I don't even know much
> about the licensing model.
> Could someone provide the details here (or persuade me otherwise)?
> Thanks everyone
>|||Wow - thanks for all the feed-back everyone.
Another thing I've just thought of... Access didn't have the concept of
stored procedures - I don't know if the latest version now does. If not,
then it's of course more vulnerable to SQL Injection attacks.|||You can emulate stored procedures in Access but only in version 2002 and
higher altough a few claim that Access 2000 can do it too. There are some
limitations though such as no temp tables, output parameters cannot be used,
does not use the @. character
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:e%231tviXEIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Wow - thanks for all the feed-back everyone.
> Another thing I've just thought of... Access didn't have the concept of
> stored procedures - I don't know if the latest version now does. If not,
> then it's of course more vulnerable to SQL Injection attacks.
>|||In article <uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl>, griffithsj_520
@.hotmail.com says...
> I'm happy to accept that Access is a great product
Access is only good for single user database tasks, it's always been
unstable for more than one user at at time.
Use one of the MSDE SQL versions or other versions for your solution and
you'll be a lot happier than using Access.
If you need reports you can use MS Access to pull them, but don't use
Access for a database.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||Leythos wrote:
> In article <uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl>, griffithsj_520
> @.hotmail.com says...
>> I'm happy to accept that Access is a great product
> Access is only good for single user database tasks, it's always been
> unstable for more than one user at at time.
> Use one of the MSDE SQL versions or other versions for your solution and
> you'll be a lot happier than using Access.
> If you need reports you can use MS Access to pull them, but don't use
> Access for a database.
You probably missed the fact that this thread is crossposted to an
Access NG, or do you really believe what you wrote about Access?
--
Roy-Vidar|||Allen,
I took a look at your link with some interest, do you know how I can go
about doing the reverse; importing data Into Access 2007 from a webpage;
specifically Googlemaps. Here's what I want to do:
I want to click on an address filed in my Access form and take that address
to googlemaps and have it return a Latitude, longtitude. Before I continue,
let me explain that I already have a fully functioning database with a form
that does the first part succesffuly, namely sending the address (in the
correct google friendly format) to googlemaps, and the browser succesfully
finds the address, and using a combination of a single line Javascript (not
authored by me, I don't understand Java) and some cumbersome Access Sendkeys
I am able to get the Lat/Long and pasted it back into the field. This
approach has it's drawbacks, it involves Alt-tabbing two application windows
and when the net connection is busy, then the timing of the Senkeys gets out
of sequence and the whole process fails, If I increase the timings, then the
process becomes to slow. In summary, it is a bit of a Heath-Robinson setup.
So I was wondering if you could give me some pointers and an understanding
on how to go about automating the process properly. I can understand VBA to a
certain extent, but really don't know where to start. What do you think, can
it be done?
"Allen Browne" wrote:
> There are no licensing issues with supplying data from an Access database
> file to a web page.
> There is some advantage in using an Access file to store the data, if you
> need to enter, query, and report on the data from within Access.
> It is not difficult for someone with experience in something like ASP or PHP
> to design a web page where data is read from and/or written to an Access
> database.
> Access data is not really secure, so you need to ensure you handle the
> security at another level. For example, last year I was able to download the
> entire MDB from a computer hardware vendor because the database went
> corrupt, and the error message showed the name and location of the file. In
> this case, the db contained only information that was on the web (i.e. parts
> and prices, not customers and orders) so no harm was done (though I did
> notify them.)
> If you just need to export the data from an Access table to a web page (e.g.
> a product listing that needs to be re-exported whenever products or prices
> change), you can also do that easily. Here's an example:
> http://allenbrowne.com/AppOutputHtml.html
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> "Griff" <griffithsj_520@.hotmail.com> wrote in message
> news:uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl...
> >I have a colleague who wants to build a web application that is expected to
> >have a fairly low number of web requests and have this site capture data in
> >a database. His heart is set upon Access as the back-end database.
> >
> > Now, I'm happy to accept that Access is a great product, but I know very
> > little about it - I've always used SQL Server. My gut reaction is to
> > suggest that he uses SQL Server Express in preference to Access, but
> > unfortunately I don't know enough about Access to argue why it shouldn't
> > be used as a database behind a web application. I don't even know much
> > about the licensing model.
> >
> > Could someone provide the details here (or persuade me otherwise)?
> >
> > Thanks everyone
>

Database error number 105

I am using ColdFusion to construct a SQL Server insert statement, the statement appears as expected in the debugger and if I cut and paste it into the Query Analyzer it works as expected. Yet from my code it produces the following error:

Database error number 105
[Macromedia][SQLServer JDBC Driver][SQLServer]Unclosed quotation mark before the character string 'kellyc'.

It almost seems as if somehow ColdFusion is introducing the problem. The server is MX, I similarly constructed Insert Statements that are working.

Here is the code...

From my Action Page...Its the first query that is failing.

<cftransaction action="begin">
<cftry>

<cfmodule template = "../queries/CaseMgmtQueries.cfm"
queryname = "AddCase"
CaseName = "#form.CaseName#"
Version = "#form.CaseVersion#"
Desc = "#form.CaseDescription#"
Start = "#form.CaseStartDate#"
End = "#form.CaseEndDate#">

<cfmodule template = "../queries/CaseMgmtQueries.cfm"
queryname = "qGetMaxCase"
CaseName = "#form.CaseName#">

<cfmodule template = "../queries/CaseMgmtQueries.cfm"
queryname = "AddCaseStatus"
CaseID = "#qGetMaxCase.NewCaseID#"
Status = "#form.CaseStatus#">

<cfset HaveError = False>
<cfcatch type="Database">
<cftransaction action="rollback">
<cfset HaveError = True>
<cf_dbErrorDisplay errno = #cfcatch.NativeErrorCode# detail = #cfcatch.Detail#>
</cfcatch>

</cftry>
</cftransaction>

And then from my queries page...

<cfif queryname is "AddCase">
<CFQUERY NAME="AddCase" DATASOURCE=#variables.DSN#>
Insert into CaseInfo
(CaseName, CaseDescription, CaseVersion, CaseStartDate, CaseEndDate)
Values ('#PreserveSingleQuotes(attributes.CaseName)#',
'#Attributes.Desc#',
'#Attributes.Version#',
'#Attributes.Start#',
'#Attributes.End#')
</CFQUERY>
</cfif>

This is producing a query that in the debug dump looks like...

Insert into CaseInfo
(CaseName, CaseDescription, CaseVersion, CaseStartDate, CaseEndDate)
Values ('kellyc', 'test', '1.1', '12/10/2003', '12/11/2003')

Which inserts correctly using Query Analyzer.

The main thing that is different about this insert from other inserts that are functioning in the program is the use of transactin processing.

Any help would be most appreciated!

KellyCKellyC,

I'm afraid I don't know a thing about ColdFusion, but here is what I would try: start up SQL Profiler and run a trace on your application. You can probably use the default settings for the trace unless it is a production server and in heavy use (in which case it's a pain to find the specific transaction you're looking for).

You can then identify the format of the T-SQL Statement (or SP) that is being used and test changes in your code to make corrections.

Regards,

hmscott|||i'm betting it's a coldfusion problem

see http://www.dbforums.com/showthread.php?threadid=974731

kelly, please don't cross-post

rudy|||Sorry Rudy,

I didn't realize it was bad form, since not everyone is subsribed to the same forums, and I don't really know if the problem is ColdFusion or SQL related at this point.

hmscott,

I have sent in a request to the DBA to try running the Profiler... I am now in the waiting zone. If it turns up something I will post that here.
Thanks for the suggestion.

KellyC|||Sorry for the crosspost again, but thought I would post my resolution here as well...

OK, as it often turns out, the problem is not always what it seems from the lovely and informative error messages. The unclosed quote was actually in the second query in the transaction. This came to light for me when I started playing with trying to submit a string with a single quote in it and noticed ColdFusion MX does not display the text of an unsucessful query in the debug. (Personally I consider this a deficiency of MX in case any Macromedia folks want my 2 cents).

So... long story short I realized it wasn't the insert query that was throwing the error and fixed the offending query and it now works like a charm.

Thanks again,
KellyC|||Nice of you to post the resolution. This is not meant to be an "I told you so," but it definitely points out an instance where SQL Profiler could have helped to narrow down the cause. Congrats for solving the problem.

Regards,

hmscott