Access as a datasource

Report · Jan 08, 2007

Hi all,

I've been recently experiencing dramatic fluctuations in the speed of the
sites I have hosted with a particular ISP. All sites are written using DW8,
traditional ASP with an access database DSN.

When I advised them of this I was advised that there was nothing they could
do and that I should upgrade my sites to SQL Server. (at a price of course)

They referred me to this comment from Microsoft:

"...we recommend that Microsoft Access be used solely for development
purposes and not for production. Microsoft Access was designed as a
single-user desktop database, and not for server use. When multiple,
concurrent users make requests of a Microsoft Access database, unpredictable
results may occur"

I could understand this if my sites were experiencing extremely heavy
traffic but this simply isn't the case.

Frankly, I'm sure the ISP is using this as an excuse to avoid looking into
the shortcomings of their own systems. Trouble is I can't find much in the
way of amunition to contradict them.

Can anybody help please?

Thanks in advance

Ken

Report · Jan 08, 2007

Ken

Access is supposed to be able to support 256 users but in practise this
number is significantly lower. I do know from experience that we had
significant improvements in both speed and reliability in moving a site from
Access to SQL and this was on a site that would be lucky if it had 10 users
accessing the database.

Having said this on a shared server your site could be being affecting by
other sites. Initially I would see if the host is willing to place the site
in its own application pool.

--
Paul Whitham
Certified Dreamweaver MX2004 Professional
Adobe Community Expert - Dreamweaver

Valleybiz Internet Design
www.valleybiz.net

"Ken Renwick" <ken@nospamrdsweb.co.uk> wrote in message
news:enu3qf$olc$1@forums.macromedia.com...
> Hi all,
>
> I've been recently experiencing dramatic fluctuations in the speed of the
> sites I have hosted with a particular ISP. All sites are written using
> DW8, traditional ASP with an access database DSN.
>
> When I advised them of this I was advised that there was nothing they
> could do and that I should upgrade my sites to SQL Server. (at a price of
> course)
>
> They referred me to this comment from Microsoft:
>
> "...we recommend that Microsoft Access be used solely for development
> purposes and not for production. Microsoft Access was designed as a
> single-user desktop database, and not for server use. When multiple,
> concurrent users make requests of a Microsoft Access database,
> unpredictable results may occur"
>
> I could understand this if my sites were experiencing extremely heavy
> traffic but this simply isn't the case.
>
> Frankly, I'm sure the ISP is using this as an excuse to avoid looking into
> the shortcomings of their own systems. Trouble is I can't find much in the
> way of amunition to contradict them.
>
> Can anybody help please?
>
> Thanks in advance
>
> Ken
>

Report · Jan 08, 2007

While Access is OK for web development. what Micorosoft says above is true. However, Microsoft are now giving a way something called SQL Sever 2005 Express -- free. I know, you never thought you would hear the word's Microsoft and free in same sentence.

You can easily update your Access Databases using the Updating Wizard and then have them as SQL Server Databases, cost nothing

The only two catches I can see with SQL Server Express is the it doesn't understand dual core processors and can't be bigger than 4 gigabytes of data.

I can't see why this would work for you.

Don't forget please back up your databases before upsizing or doing anything else to them.

Report · Jan 08, 2007

Hi

Not sure I understand the "free" bit

The ISP is quoting me £10 per month per 100mb for MS SQL 2005 and presumably
that will be for each domain or am I missing something?

Ken

"jhutchdublin" <webforumsuser@macromedia.com> wrote in message
news:enuc12$54l$1@forums.macromedia.com...
> While Access is OK for web development. what Micorosoft says above is
> true.
> However, Microsoft are now giving a way something called SQL Sever 2005
> Express
> -- free. I know, you never thought you would hear the word's Microsoft and
> free
> in same sentence.
>
> You can easily update your Access Databases using the Updating Wizard and
> then
> have them as SQL Server Databases, cost nothing
>
> The only two catches I can see with SQL Server Express is the it doesn't
> understand dual core processors and can't be bigger than 4 gigabytes of
> data.
>
> I can't see why this would work for you.
>
> Don't forget please back up your databases before upsizing or doing
> anything
> else to them.
>
>
>

Report · Jan 08, 2007

Thanks Paul

I'll speak to the ISP about the application pool first I think

Ken

"Paul Whitham AdobeCommunityExpert" <design@valleybiz.net> wrote in message
news:enubo6$4r2$1@forums.macromedia.com...
> Ken
>
> Access is supposed to be able to support 256 users but in practise this
> number is significantly lower. I do know from experience that we had
> significant improvements in both speed and reliability in moving a site
> from Access to SQL and this was on a site that would be lucky if it had 10
> users accessing the database.
>
> Having said this on a shared server your site could be being affecting by
> other sites. Initially I would see if the host is willing to place the
> site in its own application pool.
>
>
> --
> Paul Whitham
> Certified Dreamweaver MX2004 Professional
> Adobe Community Expert - Dreamweaver
>
> Valleybiz Internet Design
> www.valleybiz.net
>
> "Ken Renwick" <ken@nospamrdsweb.co.uk> wrote in message
> news:enu3qf$olc$1@forums.macromedia.com...
>> Hi all,
>>
>> I've been recently experiencing dramatic fluctuations in the speed of the
>> sites I have hosted with a particular ISP. All sites are written using
>> DW8, traditional ASP with an access database DSN.
>>
>> When I advised them of this I was advised that there was nothing they
>> could do and that I should upgrade my sites to SQL Server. (at a price of
>> course)
>>
>> They referred me to this comment from Microsoft:
>>
>> "...we recommend that Microsoft Access be used solely for development
>> purposes and not for production. Microsoft Access was designed as a
>> single-user desktop database, and not for server use. When multiple,
>> concurrent users make requests of a Microsoft Access database,
>> unpredictable results may occur"
>>
>> I could understand this if my sites were experiencing extremely heavy
>> traffic but this simply isn't the case.
>>
>> Frankly, I'm sure the ISP is using this as an excuse to avoid looking
>> into the shortcomings of their own systems. Trouble is I can't find much
>> in the way of amunition to contradict them.
>>
>> Can anybody help please?
>>
>> Thanks in advance
>>
>> Ken
>>
>
>

Report · Jan 08, 2007

There is a major catch with your suggestion in that many shared hosting
companies do not support SQL Server Express.

--
Paul Whitham
Certified Dreamweaver MX2004 Professional
Adobe Community Expert - Dreamweaver

Valleybiz Internet Design
www.valleybiz.net

"jhutchdublin" <webforumsuser@macromedia.com> wrote in message
news:enuc12$54l$1@forums.macromedia.com...
> While Access is OK for web development. what Micorosoft says above is
> true.
> However, Microsoft are now giving a way something called SQL Sever 2005
> Express
> -- free. I know, you never thought you would hear the word's Microsoft and
> free
> in same sentence.
>
> You can easily update your Access Databases using the Updating Wizard and
> then
> have them as SQL Server Databases, cost nothing
>
> The only two catches I can see with SQL Server Express is the it doesn't
> understand dual core processors and can't be bigger than 4 gigabytes of
> data.
>
> I can't see why this would work for you.
>
> Don't forget please back up your databases before upsizing or doing
> anything
> else to them.
>
>
>

Report · Jan 08, 2007

What he was referring to is that SQL Server comes in several versions. There
is a lightweight version called SQL Server Express which is a free download
from MS as it is intended to be used for development purposes on a local
machine.

--
Paul Whitham
Certified Dreamweaver MX2004 Professional
Adobe Community Expert - Dreamweaver

Valleybiz Internet Design
www.valleybiz.net

"Ken Renwick" <ken@nospamrdsweb.co.uk> wrote in message
news:enucml$5uc$1@forums.macromedia.com...
> Hi
>
> Not sure I understand the "free" bit
>
> The ISP is quoting me £10 per month per 100mb for MS SQL 2005 and
> presumably that will be for each domain or am I missing something?
>
> Ken
>
> "jhutchdublin" <webforumsuser@macromedia.com> wrote in message
> news:enuc12$54l$1@forums.macromedia.com...
>> While Access is OK for web development. what Micorosoft says above is
>> true.
>> However, Microsoft are now giving a way something called SQL Sever 2005
>> Express
>> -- free. I know, you never thought you would hear the word's Microsoft
>> and free
>> in same sentence.
>>
>> You can easily update your Access Databases using the Updating Wizard and
>> then
>> have them as SQL Server Databases, cost nothing
>>
>> The only two catches I can see with SQL Server Express is the it doesn't
>> understand dual core processors and can't be bigger than 4 gigabytes of
>> data.
>>
>> I can't see why this would work for you.
>>
>> Don't forget please back up your databases before upsizing or doing
>> anything
>> else to them.
>>
>>
>>
>
>

Report · Jan 09, 2007

"Ken Renwick" <ken@nospamrdsweb.co.uk> wrote in message
news:enu3qf$olc$1@forums.macromedia.com...
> I could understand this if my sites were experiencing extremely heavy
> traffic but this simply isn't the case.
>
> Frankly, I'm sure the ISP is using this as an excuse to avoid looking into
> the shortcomings of their own systems. Trouble is I can't find much in the
> way of amunition to contradict them.

That's because your host/ISP is correct. It doesn't take much traffic to
choke Access, and connecting to Access using a DSN is the slowest possible
way to connect.

Report · Jan 09, 2007

> That's because your host/ISP is correct. It doesn't take much traffic to
> choke Access, and connecting to Access using a DSN is the slowest possible
> way to connect.

So why do ISP's support Access at all and mine makes it awkward to do
anything other than connect via DSN?

Ken

"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:eo0a1v$fvd$1@forums.macromedia.com...
>
> "Ken Renwick" <ken@nospamrdsweb.co.uk> wrote in message
> news:enu3qf$olc$1@forums.macromedia.com...
>> I could understand this if my sites were experiencing extremely heavy
>> traffic but this simply isn't the case.
>>
>> Frankly, I'm sure the ISP is using this as an excuse to avoid looking
>> into the shortcomings of their own systems. Trouble is I can't find much
>> in the way of amunition to contradict them.
>
> That's because your host/ISP is correct. It doesn't take much traffic to
> choke Access, and connecting to Access using a DSN is the slowest possible
> way to connect.
>

Report · Jan 09, 2007

"Ken Renwick" <ken@nospamrdsweb.co.uk> wrote in message
news:eo0gbm$nnc$1@forums.macromedia.com...
>> That's because your host/ISP is correct. It doesn't take much traffic to
>> choke Access, and connecting to Access using a DSN is the slowest
>> possible way to connect.
>
> So why do ISP's support Access at all and mine makes it awkward to do
> anything other than connect via DSN?
>
> Ken

Consumer demand for the first; I doubt that's the case for the second.
Connecting via OLE DB doesn't require any special server setup so long as
the appropriate providers are installed (which is likely). Besides, the
host isn't really "supporting" Access so much as they're allowing you to
create DSNs on their server (which can be used to connect to any ODBC data
source, not just Access) and have the standard set of database providers
installed. Their response to you seems to indicate that they tolerate
Access more than anything else, which they probably have to do to keep some
customers happy.

Report · Jan 09, 2007

They actively promote Access

"unlimited MS-Access ODBC sources"

to quote their publicity and a special folder outwith the site root to
upload the file to. Because of that, for a dsn-less connection you cannot
use "server.mappath" so you have to know the absolute path to the database.
They used to provide this in the member area but it has suddenly
disappeared.

On top of that they charge extra for sql server and MySQL.

After all this I'm afraid I find it extremely disingenuous of them to turn
round and say that Access shouldn't be used

<end of rant>

Ken

"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:eo0ir8$qol$1@forums.macromedia.com...
>
> "Ken Renwick" <ken@nospamrdsweb.co.uk> wrote in message
> news:eo0gbm$nnc$1@forums.macromedia.com...
>>> That's because your host/ISP is correct. It doesn't take much traffic
>>> to choke Access, and connecting to Access using a DSN is the slowest
>>> possible way to connect.
>>
>> So why do ISP's support Access at all and mine makes it awkward to do
>> anything other than connect via DSN?
>>
>> Ken
>
> Consumer demand for the first; I doubt that's the case for the second.
> Connecting via OLE DB doesn't require any special server setup so long as
> the appropriate providers are installed (which is likely). Besides, the
> host isn't really "supporting" Access so much as they're allowing you to
> create DSNs on their server (which can be used to connect to any ODBC data
> source, not just Access) and have the standard set of database providers
> installed. Their response to you seems to indicate that they tolerate
> Access more than anything else, which they probably have to do to keep
> some customers happy.
>

Report · Jan 09, 2007

"Ken Renwick" <ken@nospamrdsweb.co.uk> wrote in message
news:eo0krs$t6j$1@forums.macromedia.com...
> After all this I'm afraid I find it extremely disingenuous of them to turn
> round and say that Access shouldn't be used
>
> <end of rant>
>
> Ken

Marketing usually doesn't care what the poor guys who have to run the place
think. 😉

Report · Jan 10, 2007

OK - I read most of the thread so far ...

1. - I have used Access for some massive websites and they have been fine,
in fact I can think of a few absolutely huge websites running off access!
2. - I do reccomend MS SQL it makes life soooo much easier!
3. - can you use a DSNless connection - I have been told that helps ... ?
4. - change host!
5. MySQL and DW and ASP isn't supported (or is it?)

Good luck!!!

Gerry

"Ken Renwick" <ken@nospamrdsweb.co.uk> wrote in message
news:enu3qf$olc$1@forums.macromedia.com...
> Hi all,
>
> I've been recently experiencing dramatic fluctuations in the speed of the
> sites I have hosted with a particular ISP. All sites are written using
> DW8, traditional ASP with an access database DSN.
>
> When I advised them of this I was advised that there was nothing they
> could do and that I should upgrade my sites to SQL Server. (at a price of
> course)
>
> They referred me to this comment from Microsoft:
>
> "...we recommend that Microsoft Access be used solely for development
> purposes and not for production. Microsoft Access was designed as a
> single-user desktop database, and not for server use. When multiple,
> concurrent users make requests of a Microsoft Access database,
> unpredictable results may occur"
>
> I could understand this if my sites were experiencing extremely heavy
> traffic but this simply isn't the case.
>
> Frankly, I'm sure the ISP is using this as an excuse to avoid looking into
> the shortcomings of their own systems. Trouble is I can't find much in the
> way of amunition to contradict them.
>
> Can anybody help please?
>
> Thanks in advance
>
> Ken
>

Report · Jan 11, 2007

I had exactly the same problem - Access being a client-side processed database.

We had several sales guys out on the road and we tried a DSN connection to an Access Database (which I am comfortable working with).

In the end, we turned to MySQL server-side and php coding which was a free option but it meant we had to learn php and all the processes behind it.

I have to admit that, since the databases formal launch in December, we have never had it so good - lightning fast and, in many ways, more practical and powerful than Access.

Good luck