Convert an Access database to SQL

[cory]

Hi,
I have an Access database and am having an ASP.NEt application written for
it. It is almost complete. I have a hosting company that I signed up with
a month ago but before I did anything I asked them if Access and ASP.NET
would work on their servers, they said yes so I bought in. Now they are
saying my application wont work on their servers using MSaccess and I can
only use SQL or asp 3.0. They are saying Microsoft is trying to keep Ms
Access as a desktop application. The database is done, and the app is
almost done. I have paid alot of money for this application. What are my
options here?

How hard is it to convert an Access Database to SQL? What ramifications
does this have on the ASP.NET part?

How hard is it to convert to asp 3.0?

I was also told XML would work. DOnt know anything about it.

What about other hosts?

Any other options?

I just cant believe this.
Any help and insight would be greatly appreciated.

Cory

 

[Scott McDaniel]

Converting an Access data structure to a SQL Server structure can be
difficult, depending on how the tables were designed and built. There are
tools available to do this, but I generally use the Data Transformation
Service if going to MS SQL Server.

From what I've heard, ASP 3.0 provides much poorer performance than ASP.net.
NOt sure about coverting between the two, you'll probably need to ask that
in an ASP group ... I've merely dabbled in ASP.NET

XML is a method to transfer data between platforms using formatted
text-based streams. Your data provider (i.e. your database) would send you
an XML document, and your application would take/convert/use that XML
document to "read" the data sent by the provider.

There are quite a few hosts that will support Access databases ... Google
should return a good list. However, your ISP service is not entirely
incorrect in their views. Most find that, unless your web site experiences
very low traffic, your Access db will quickly not be up to the task. My ISP
recently stopped supporting Access dbs simply because of the problems they
were having with congestion, performance issues (server tied up with
Jet/Access requests) and complaints from their users of lost/corrupted
databases. If my site were critical to my business, and I couldn't afford to
lose the time spent in restoring from my backup, then I'd certainly consider
going the SQL route ...

 

[Norman Yuan]

Since your application is developed in ASP.NET + MS Access...here are some
thought:

1. Convert DB from Access to Sql Server may be fairly easy, or may be quite
some work, depending on how the db is designed. Tables can be easily
transferred to SQL server, while queries are not. So, if the Access DB and
your app use a lot of queries in Access, you are having trouble to convert
them to either stored procedures in SQL server, or dynamic SQL statement in
your app.

2. Since the app is .NET app, I believe that the data access is based on
ADO.NET, not old ADO/DAO thing, thus, OleDB namespace in .NET is used to
access data in Jet database (Access database). Although OleDB namespace
works with SQL Server, you really should use SqlClient namespace to access
data in SQL Server, SqlClient namespace is specifically designed and
optimized in ADO.NET for SQL Server.

3. Go back to ASP.3.0 does not change the fact that Access is basically
designed as desktop database. Convert ASP.NET to ASP 3.0 may lead to entire
application re-written because of the different application
structure/approach in .NET and pre-.NET technology.

IMO, find a ISP that provides ASP.NET + Access DB service; or convert Access
DB to Sql Server, after all, a server based db is more suitable for web app.

 

[Kevin3NF]

I feel your pain, so here's a thought...

Let me take a look at your existing Access database, and I'll give you a
free, no obligation time estimate on the conversion to SQL Server. I've
done a few of these recently...the most complex took 57 hours and included a
learning curve I won;t have to climb again...

You can reach me by emailing Kevin [at] 3NF-inc {dot} com. You will get a
SPAMArrest verification email in reply.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

 

[Tom]

I write in ASP 3.0 and use MsAccess DB's but for very small traffic
sites. I agree if you expect any traffic you should go ahead and move
up to MySQL or SQLserver whichever the host allows you to use.

Since your data isn't too far along your developer should be able to
do the switch over for you.

 

[David W. Fenton]

(e-mail address removed) (Norman Yuan) wrote in

IMO, find a ISP that provides ASP.NET + Access DB service; or
convert Access DB to Sql Server, after all, a server based db is
more suitable for web app.

Every single web app should use a server-based db?

Or do you really mean "a server-based db is more suitable for
medium- to large-volume websites, especially with large populations
of simultaneous connections and heavy write load. On the other
hand, smaller numbers of simultaneous connections and read-only web
pages can be run very well against Jet back ends."

 

[Steve Jorgensen]

(e-mail address removed) (Norman Yuan) wrote in


Every single web app should use a server-based db?

Or do you really mean "a server-based db is more suitable for
medium- to large-volume websites, especially with large populations
of simultaneous connections and heavy write load. On the other
hand, smaller numbers of simultaneous connections and read-only web
pages can be run very well against Jet back ends."

What I've heard from people who have done it is that Web apps bog down with
Access back-ends under pretty light loads because Jet is not multi-threaded.
The concensus seems to be that you'll eventually need a server back-end for
most Web apps, so it's easier if you go ahead and start out that way.

 

[Jerry Boone]

Amen

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access

 

[Larry Linson]

"Steve Jorgensen" wrote

What I've heard from people who have
done it is that Web apps bog down with
Access back-ends under pretty light loads

What's a "pretty light load"? Would it be fewer concurrent users than can
use an Access multiuser application with satisfactory performance? I
seriously doubt it, and that number can be up to 100+. That's not enough for
"the next Amazon", but I would venture that few websites on the Net
consistently have enough users to put a strain on it.

because Jet is not multi-threaded.

Again, if that's not a problem with 75 concurrent users on a LAN, why would
it be a problem with 75 concurrent users on a website (short of the web
developers not knowing much about database and using it in an abominable
fashion)?

The concensus seems to be that you'll
eventually need a server back-end for
most Web apps, so it's easier if you go
ahead and start out that way.

That's a generalization, that, like many generalizations, can throw you
off-track -- I'd disagree that _most_ Web apps will outgrow a Jet database.
Some will, but many will not.

Use appropriate technologies... that's the ticket.

Larry Linson
Microsoft Access MVP

 

[cory]

It is a large application and smalldatabase at the same time. I think there
are 25-30 tables. No queries or forms etc.
Just tables. It is meant for limited users. There may be 10-20 users
logged in at once at any given time. It is not ecommerce and is is not
intended to be marketed for the world to use. If I am lucky, I may get 100
users in the dbase in a few years. The web app is more read only meaning
the user cannot write to the database only an admin can and I am the only
admin right now. I felt Access was the right choice because I know Access
somewhat and I dont know SQL. at all. I like Access for the reporting
feature as this is very important to me for providing clients with their
info at the end of a project. It seems alot of people think all databases
habe world wide users, this is not the case. I have found a host that can
do this and the host I am with who said they could not is the ONLY one who
does not support this arrangement......I find this odd. Myhosting.com is
the company that cant do it so I am moving to another provider. I have it
in writing that this new host I am moving to can provide what I need 100%.

I may switch to SQL in the future and my programmer said this would take
about 50 hours to do....doe sthis sound right for the number of tables and
no queries?


Thanks for all help here

 

[Steve Jorgensen]

"Steve Jorgensen" wrote


What's a "pretty light load"? Would it be fewer concurrent users than can
use an Access multiuser application with satisfactory performance? I
seriously doubt it, and that number can be up to 100+. That's not enough for
"the next Amazon", but I would venture that few websites on the Net
consistently have enough users to put a strain on it.


Again, if that's not a problem with 75 concurrent users on a LAN, why would
it be a problem with 75 concurrent users on a website (short of the web
developers not knowing much about database and using it in an abominable
fashion)?

Aree you comparing Access/JET to ASP/JET? If so, they are not at all the same
thing. With Access, each user has their own instance of JET running queries.
That's like each user having a separate thread, and even a separate machine to
run the thread! The only issues are lock contention and network resources.

With a Web server, a single instance of JET must handle all requests from all
users, and it can only service one of them at a time. If my query takes 5
seconds, everyone waits 5 seconds. If the next request in the queue also
takes 5 seconds, someone else waits 10.

 

[Kevin3NF]

How long it would take depends entirely on the structure of the tables and
how complex the queries are. Simple select queries convert easily. More
complex queries need to be converte to stored procedures, which takes
longer.

My offer to look over your db still stands, and will always be there.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

 

[Larry Linson]

Thanks for the clarification.

Aree you comparing Access/JET to ASP/JET? If so, they are not at all the same
thing. With Access, each user has their own instance of JET running queries.
That's like each user having a separate thread, and even a separate machine to
run the thread! The only issues are lock contention and network resources.

With a Web server, a single instance of JET must handle all requests from all
users, and it can only service one of them at a time. If my query takes 5
seconds, everyone waits 5 seconds. If the next request in the queue also
takes 5 seconds, someone else waits 10.

 

[David W. Fenton]

(e-mail address removed) (Steve Jorgensen) wrote in

What I've heard from people who have done it is that Web apps bog
down with Access back-ends under pretty light loads because Jet is
not multi-threaded. The concensus seems to be that you'll
eventually need a server back-end for most Web apps, so it's
easier if you go ahead and start out that way.

Is that people who are being careful to conserve connections in
their ASP code?

MichKa used to explain how his website was very robust with 100s of
thousands of hits per day, and a Jet back end.

As to multi-threading, I think the question is *thread-safe*, and
MichKa has said, I believe, that Jet accessed via DAO is not
thread-safe, whereas through ADO it *is* thread-safe.

Because of that, my surmise is that ASP is one of the cases where
ADO is better for use with Jet than its native DAO.

 

[Steve Jorgensen]

....

Is that people who are being careful to conserve connections in
their ASP code?

MichKa used to explain how his website was very robust with 100s of
thousands of hits per day, and a Jet back end.

That's an opinion, I'd be inclinded to believe.

As to multi-threading, I think the question is *thread-safe*, and
MichKa has said, I believe, that Jet accessed via DAO is not
thread-safe, whereas through ADO it *is* thread-safe.

That's interesting to know. That would make a big difference.

Because of that, my surmise is that ASP is one of the cases where
ADO is better for use with Jet than its native DAO.

My opinion has been that, in Access there are usually more reasons to use DAO
than ADO. Outside of Access, there are usually more reasons to use ADO than
DAO.

 

[Jerry Boone]

Have you or anyone else been able to open mdb's while they are being used
and not get a "Page cannot be displayed.... Jet database is in use" error?
I spent a lot of time posting and trying to work around this with no
success. Resorted to copy, modify and replace, or web based updates that
use sql statements. This is quite different with Sql Server and other
engines.

If no one has a "viable" answer to this then I would say the hell with mdb's
on the web when they need to be modified regularly by a developer and
updated directly by users.

Don't even bother replying unless you have a connection string or proven
odbc property... or something similar, pretend for a minute that your a
developer and need to have the database open while your building your ASP
pages.

This may be better in ASP.Net, but I haven't even given it a chance based on
past performance.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access

 

[David W. Fenton]

(e-mail address removed) (Steve Jorgensen) wrote in

...

That's an opinion, I'd be inclinded to believe.

Well, it's MichKa, so it means a lot to me. I'm certain the design
of his website was super rock solid, as MichKa knows what he's
doing. I would expect he conserved his connections very carefully.

That's interesting to know. That would make a big difference.

I hope I'm not misremembering. Nope:

http://groups.google.com/groups?selm=#3rmveRE$GA.306@cpmsnbbsa
05&oe=UTF-8

says:

Jet 4.0 is threadsafe, as is the Jet OLE DB provider for
4.0.

Neither 3.5, nor any prior version, nor DAO of any version,
nor Access, is thread-safe.

There you have it.

My opinion has been that, in Access there are usually more reasons
to use DAO than ADO. Outside of Access, there are usually more
reasons to use ADO than DAO.

ADO is an interface to OLEDB, right? In ASP can't you use OLEDB
directly? And all bets are off in ASP.NET?

 

[Jim Allensworth]

Aree you comparing Access/JET to ASP/JET? If so, they are not at all the same
thing. With Access, each user has their own instance of JET running queries.
That's like each user having a separate thread, and even a separate machine to
run the thread! The only issues are lock contention and network resources.

With a Web server, a single instance of JET must handle all requests from all
users, and it can only service one of them at a time. If my query takes 5
seconds, everyone waits 5 seconds. If the next request in the queue also
takes 5 seconds, someone else waits 10.

5 seconds is a *very* long query. But here is an interesting
article/discussion on that.

http://www.15seconds.com/Issue/010514.htm

- Jim

 

[Jim Allensworth]

Because of that, my surmise is that ASP is one of the cases where
ADO is better for use with Jet than its native DAO.

AFIK, you can't use DAO with asp - only ADO. Classic ASP anyway.

- Jim

 

[Suvra Barua]

I don't think there is any reason to convert or to rewrite code or
change database. ASP.NET + Access works absolutely fine this is from
my practical experience. Only thing I see here is the ISP is not up to
dated. Find a good ISP that will help you get what you need.


Suvra
M6.Net
www.m6.net