Access databases not for Web Apps?

[4Wheels]

Is it true that Access databases were not intended for use in web applications? I build a database driven site 6 years ago using Access and never had any problems. It's version 2002/2003 now but I can't get a connection to it no matter what.

Honestly I wasn't planning on investing this much time fixing old databases, ASP code and HTML. I just wanted to get my old site back up since people have been wanting me to, and then focus on another project I already started using SQL Server and ASP.NET 4.0.

Ideas? Should I abandon my old site?

Or one last time, does anyone see anything wrong with this code? If I can't resolve this after one day I give up...time to move on.

Thanks a lot...to anyone who can see what I can't.


set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath

set rsIntro = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT count(*) as plantcnt FROM intro " & _
"WHERE ready=true and Instr(common_name, '1')=0 and group_nbr<17 "

' response.write "<br>filepath:" & filepath & "*"
' response.write "<br>common_name:" & common_name & "*"
' response.write "<br>group_nbr:" & group_nbr & "*"
' response.write "<br>strSQL:" & strSQL & "*"
' response.flush
' response.end

rsIntro.Open strSQL, objConn <-------- error

if not rsIntro.EOF then
dim intTotPlants
intTotPlants=rsIntro("plantcnt")
end if

rsIntro.Close
strSQL = "SELECT count(*) as tipscnt FROM intro WHERE ready=true and Instr(common_name, '1')=1 or group_nbr>16"
rsIntro.Open strSQL, objConn
if not rsIntro.EOF then
dim int
intTotTips=rsIntro("tipscnt")
end if

set rsIntro = nothing
set objConn = nothing

========================

If I uncomment the 6 lines above, this is the output: (common_name and group_nbr are both null even though I see data there.

Sorry but I can't get this code to format very well. [code] and [\code] don't seem to work.

========================

filepath:E:\web\jaysplantsc\htdocs\db\plants.mdb*
common_name:*
group_nbr:*
strSQL:SELECT count(*) as plantcnt FROM intro WHERE ready=true and Instr(common_name, '1')=0 and group_nbr<17 *
Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

/index.asp, line 43

[wisemx]

Honestly it is time to move away from Classic ASP.
While Access DB's were all the charm for ASP sites they really should only be used for smaller Web purposes now.
While many people like myself created sites with Access recordsets it is not easy to help someone nowadays, when I look at the code I want to run some tests, but that's where the problem is, I don't have any way to test it now.
What are you using to code the site btw? Dreamweaver was probably the best back then, it will even create the RS for you and generate over 300 lines of code per page.
I'd like to look at that code and tell you what's wrong but my mind just ain't working that way. ;-)
All the best,
Mark

[4Wheels]

Originally Posted by wisemx

Honestly it is time to move away from Classic ASP.

Mark,

Hey, thanks for your input. I'd been thinking the same thing myself. Too bad there's no way to convert the database since it took 2+ years to build...oh well.

I don't even remember the editor I used back then but I remember coding quite a lot manually. Now I'm using ASP.NET 3.5/4...

Take care...
Jay

[Tasslehoff]

Hi Jay,

There is actually a wizard in Access 2007/2010? that will help you convert your Access database to a SQL Server database. I've used it once, and you may have to fiddle around with it to get it working.

[4Wheels]

Thanks for the idea but I thought of that (converting) right after I posted the comment. I'm going to pass though. It's not that important, and I'd still have to mess with the old code.

[rfrlajoied1]

well. i'm currently having a website running in MS-Access accessing data with ADO and i don't have problem with that. your connectionstring perhaps.

[mjp]

Originally Posted by 4Wheels

Is it true that Access databases were not intended for use in web applications?

To answer this question, yes it's true. Remember, Microsoft released the first versions of Access in 1992, before anyone in Redmond seemed to be aware that there was an internet.

Access is part of the Office suite, and it is meant to be used by a single desktop user. Does it work for a web application? Sure, it can. But if you hit it with any considerable amount of traffic/requests, it's likely going to fail.

Since Microsoft is moving Office into "the cloud" they may take steps to make Access more multi-user friendly, but really, with SQL, SQL Express and SQL CE (or even MySQL), there is little reason anymore to use Access for a web application.

[Peter N Roth]

Originally Posted by mjp

... but really, with SQL, SQL Express and SQL CE (or even MySQL), there is little reason anymore to use Access for a web application.

Do i understand correctly that SQL Express and SQL CE are recommended over Access? I thought they were designed to be single user DBs also?

[wisemx]

Gosh we'd have to hash this one out for sure.
For people like me, no income (My last paycheck was 11 years ago).
I'd still vote Access for something like a blog that supports it over SQL CE.
While I was at Siemens AG a lot of very wealthy persons used it for portability and I think it still holds that water. Just saying.

[mjp]

We support Access, so anyone who wants to use Access should feel free to do so. Just don't expect it to scale if your site grows to have more than modest resource needs.

I wasn't really recommending or not recommending any database, just answering the original question.

[Peter N Roth]

I’m still trying to discern a reasonable option for databases on the web, because there are conflicting recommendations (or NO recommendations).

Microsoft makes a bunch of ‘datasources’ available in Visual Studio 2010, one of which interfaces with Access. So there’s some measure of credibility associated with using Access simply because that tool is available. Access is really point and click development, which is another advantage. So the idea of ‘fail’ – what does that mean in this context? That Access can’t handle some magnitude of concurrent requests? Is failure merely a delay in processing? What already?

Or perhaps the recommendation is 'this is new, therefore it is better'?

Still not convinced, and not even persuaded, but considering...

[mjp]

Originally Posted by Peter N Roth

So the idea of ‘fail’ – what does that mean in this context? That Access can’t handle some magnitude of concurrent requests?

That's it. I can't tell you specifically what that limit is, since it depends on what your application is asking from the database. But if you put too much stress on an Access database, you will eventually "crash" it, meaning it won't respond to any queries.

In all fairness, that's true of pretty much any database, but the ability to handle traffic is exponentially greater each time you "move up" a level from Access (meaning from Access to CE or Express and from CE or Express up to SQL 2005 or 2008). They all work, and they all have their place. But a file-based database like Access or CE will never scale up like a full-blown relational database will.

I understand that Access is simple and easy to work with and many people have almost two decades of experience with it, which makes it an attractive choice. But that ease and simplicity comes at a cost of performance.

[wisemx]

Fully agree
The advantage of using some of these others with a SQL Server account is security, for separation.
i.e. Community Forum that has more than 3,000 users, the forum software uses SQL Server or maybe even MySQL, then you create something public like a Shoutbox that you do not want associated with your secure SQL DB, so for that little Shoutbox Access will work out fine, especially since there are two very clean ways on DASP servers to secure your Access DB. Its all good.

[Peter N Roth]

Thanks guys!