Convert an Access database to SQL

**Steve Jorgensen** · Nov 12 '05, 06:35 PM

Re: Convert an Access database to SQL

Please see the recent thread underway on this subject here at
comp.databases. ms-access.

On Wed, 07 Jan 2004 04:56:55 GMT, "cory" <noone@here.com > wrote:
[color=blue]
>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
>
>[/color]

**Scott McDaniel** · Nov 12 '05, 06:35 PM

Re: Convert an Access database to SQL

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

--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP
"cory" <noone@here.com > wrote in message
news:rkMKb.8681 2$6b2.26807@edt nps84...[color=blue]
> 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[/color]
with[color=blue]
> 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
>
>
>[/color]

**Norman Yuan** · Nov 12 '05, 06:36 PM

Re: Convert an Access database to SQL

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.

"cory" <noone@here.com > wrote in message
news:rkMKb.8681 2$6b2.26807@edt nps84...[color=blue]
> 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[/color]
with[color=blue]
> 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
>
>
>[/color]

**Kevin3NF** · Nov 12 '05, 06:36 PM

Re: Convert an Access database to SQL

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

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

"cory" <noone@here.com > wrote in message
news:rkMKb.8681 2$6b2.26807@edt nps84...[color=blue]
> 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[/color]
with[color=blue]
> 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
>
>
>[/color]

**Tom** · Nov 12 '05, 06:36 PM

Re: Convert an Access database to SQL

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** · Nov 12 '05, 06:36 PM

Re: Convert an Access database to SQL

normanyuan@Remo veThis.shaw.ca (Norman Yuan) wrote in
<XBWKb.15883$X% 5.13218@pd7tw2n o>:
[color=blue]
>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.[/color]

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

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

**Steve Jorgensen** · Nov 12 '05, 06:36 PM

Re: Convert an Access database to SQL

On Wed, 07 Jan 2004 20:10:13 GMT, dXXXfenton@bway .net.invalid (David W.
Fenton) wrote:
[color=blue]
>normanyuan@Rem oveThis.shaw.ca (Norman Yuan) wrote in
><XBWKb.15883$X %5.13218@pd7tw2 no>:
>[color=green]
>>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.[/color]
>
>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."[/color]

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** · Nov 12 '05, 06:36 PM

Re: Convert an Access database to SQL

Amen

--
Jerry Boone
Analytical Technologies, Inc.

http://www.antech.biz

Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access

"Steve Jorgensen" <nospam@nospam. nospam> wrote in message
news:rqrovvk6ee i8d8lh6d583urjo qqmkmhmo0@4ax.c om...[color=blue]
> On Wed, 07 Jan 2004 20:10:13 GMT, dXXXfenton@bway .net.invalid (David W.
> Fenton) wrote:
>[color=green]
> >normanyuan@Rem oveThis.shaw.ca (Norman Yuan) wrote in
> ><XBWKb.15883$X %5.13218@pd7tw2 no>:
> >[color=darkred]
> >>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.[/color]
> >
> >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."[/color]
>
> What I've heard from people who have done it is that Web apps bog down[/color]
with[color=blue]
> Access back-ends under pretty light loads because Jet is not[/color]
multi-threaded.[color=blue]
> The concensus seems to be that you'll eventually need a server back-end[/color]
for[color=blue]
> most Web apps, so it's easier if you go ahead and start out that way.[/color]

**Larry Linson** · Nov 12 '05, 06:37 PM

Re: Convert an Access database to SQL

"Steve Jorgensen" wrote
[color=blue]
> What I've heard from people who have
> done it is that Web apps bog down with
> Access back-ends under pretty light loads[/color]

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.
[color=blue]
> because Jet is not multi-threaded.[/color]

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)?
[color=blue]
> 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.[/color]

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** · Nov 12 '05, 06:37 PM

Re: Convert an Access database to SQL

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

"Norman Yuan" <normanyuan@Rem oveThis.shaw.ca > wrote in message
news:XBWKb.1588 3$X%5.13218@pd7 tw2no...[color=blue]
> 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[/color]
quite[color=blue]
> 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[/color]
in[color=blue]
> 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[/color]
entire[color=blue]
> 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[/color]
Access[color=blue]
> DB to Sql Server, after all, a server based db is more suitable for web[/color]
app.[color=blue]
>[/color]

**Steve Jorgensen** · Nov 12 '05, 06:37 PM

Re: Convert an Access database to SQL

On Thu, 08 Jan 2004 02:30:02 GMT, "Larry Linson" <bouncer@localh ost.not>
wrote:
[color=blue]
>"Steve Jorgensen" wrote
>[color=green]
> > What I've heard from people who have
> > done it is that Web apps bog down with
> > Access back-ends under pretty light loads[/color]
>
>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.
>[color=green]
> > because Jet is not multi-threaded.[/color]
>
>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)?[/color]

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** · Nov 12 '05, 06:38 PM

Re: Convert an Access database to SQL

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

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

"cory" <noone@here.com > wrote in message
news:EY3Lb.4794 6$Dm.7435@edtnp s89...[color=blue]
> It is a large application and smalldatabase at the same time. I think[/color]
there[color=blue]
> 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[/color]
100[color=blue]
> 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
>
> "Norman Yuan" <normanyuan@Rem oveThis.shaw.ca > wrote in message
> news:XBWKb.1588 3$X%5.13218@pd7 tw2no...[color=green]
> > Since your application is developed in ASP.NET + MS Access...here are[/color][/color]
some[color=blue][color=green]
> > thought:
> >
> > 1. Convert DB from Access to Sql Server may be fairly easy, or may be[/color]
> quite[color=green]
> > 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[/color][/color]
and[color=blue][color=green]
> > your app use a lot of queries in Access, you are having trouble to[/color][/color]
convert[color=blue][color=green]
> > them to either stored procedures in SQL server, or dynamic SQL statement[/color]
> in[color=green]
> > 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[/color][/color]
access[color=blue][color=green]
> > 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[/color]
> entire[color=green]
> > 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[/color]
> Access[color=green]
> > DB to Sql Server, after all, a server based db is more suitable for web[/color]
> app.[color=green]
> >[/color]
>
>
>[/color]

**Larry Linson** · Nov 12 '05, 06:38 PM

Re: Convert an Access database to SQL

Thanks for the clarification.

"Steve Jorgensen" <nospam@nospam. nospam> wrote in message
news:ldtpvv08ah j22g3njmgtr7flm l5k9kecrs@4ax.c om...[color=blue]
> On Thu, 08 Jan 2004 02:30:02 GMT, "Larry Linson" <bouncer@localh ost.not>
> wrote:
>[color=green]
> >"Steve Jorgensen" wrote
> >[color=darkred]
> > > What I've heard from people who have
> > > done it is that Web apps bog down with
> > > Access back-ends under pretty light loads[/color]
> >
> >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[/color][/color]
for[color=blue][color=green]
> >"the next Amazon", but I would venture that few websites on the Net
> >consistently have enough users to put a strain on it.
> >[color=darkred]
> > > because Jet is not multi-threaded.[/color]
> >
> >Again, if that's not a problem with 75 concurrent users on a LAN, why[/color][/color]
would[color=blue][color=green]
> >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)?[/color]
>
> Aree you comparing Access/JET to ASP/JET? If so, they are not at all the[/color]
same[color=blue]
> thing. With Access, each user has their own instance of JET running[/color]
queries.[color=blue]
> That's like each user having a separate thread, and even a separate[/color]
machine to[color=blue]
> run the thread! The only issues are lock contention and network[/color]
resources.[color=blue]
>
> With a Web server, a single instance of JET must handle all requests from[/color]
all[color=blue]
> 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.
>[/color]

**David W. Fenton** · Nov 12 '05, 06:38 PM

Re: Convert an Access database to SQL

nospam@nospam.n ospam (Steve Jorgensen) wrote in
<rqrovvk6eei8d8 lh6d583urjoqqmk mhmo0@4ax.com>:
[color=blue]
>On Wed, 07 Jan 2004 20:10:13 GMT, dXXXfenton@bway .net.invalid
>(David W. Fenton) wrote:
>[color=green]
>>normanyuan@Re moveThis.shaw.c a (Norman Yuan) wrote in
>><XBWKb.15883$ X%5.13218@pd7tw 2no>:
>>[color=darkred]
>>>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.[/color]
>>
>>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."[/color]
>
>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.[/color]

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.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Convert an Access database to SQL

Convert an Access database to SQL

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment