Hi,

I'm developing an asp.net application and recently, the company decided
to support customers with only ms access (lots of share hosting users
don't have the luxury of sql server, and my company doesn't want to
miss out these users since we are getting a lot of requests from these
share hosting users).

Anyway, most of my app's quries are fairly straight forward, insert,
update, delete.. but there are a few sql quries are a bit more
complicatd (dynamic sql concatnations and stuffs), I'm wondering if
anybody here knows a decent third party tool that can convert downward
from sql server 2000 to ms access?

Thanks a lot.

Nothing that I'm aware of (it's more common to move in the other direction.)

I wouldn't think you'd need to change much more than the connection
information though (plus ensure that dates are delimited with #, not ')

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Liming" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I'm developing an asp.net application and recently, the company decided
> to support customers with only ms access (lots of share hosting users
> don't have the luxury of sql server, and my company doesn't want to
> miss out these users since we are getting a lot of requests from these
> share hosting users).
>
> Anyway, most of my app's quries are fairly straight forward, insert,
> update, delete.. but there are a few sql quries are a bit more
> complicatd (dynamic sql concatnations and stuffs), I'm wondering if
> anybody here knows a decent third party tool that can convert downward
> from sql server 2000 to ms access?
>
> Thanks a lot.
>

Thanks Douglas,

Yeah, the reason I'm asking is because there are more than 800 quries
or something like that, and for me to convert them( which i undestand
is more like copy,paste and fix syntax) is such a pain to just think
about them, so i figured it would be much better if there are any tools
to do the bulk of the work for me.

Anyway, thanks for the reply man, really appreciate it and happy new
year.

Liming



Douglas J. Steele wrote:
> Nothing that I'm aware of (it's more common to move in the other direction.)
>
> I wouldn't think you'd need to change much more than the connection
> information though (plus ensure that dates are delimited with #, not ')
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Liming" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi,
> >
> > I'm developing an asp.net application and recently, the company decided
> > to support customers with only ms access (lots of share hosting users
> > don't have the luxury of sql server, and my company doesn't want to
> > miss out these users since we are getting a lot of requests from these
> > share hosting users).
> >
> > Anyway, most of my app's quries are fairly straight forward, insert,
> > update, delete.. but there are a few sql quries are a bit more
> > complicatd (dynamic sql concatnations and stuffs), I'm wondering if
> > anybody here knows a decent third party tool that can convert downward
> > from sql server 2000 to ms access?
> >
> > Thanks a lot.
> >

On 29 Dec 2006 17:58:56 -0800, "Liming" <(E-Mail Removed)> wrote:

>Yeah, the reason I'm asking is because there are more than 800 quries
>or something like that, and for me to convert them( which i undestand
>is more like copy,paste and fix syntax) is such a pain to just think
>about them, so i figured it would be much better if there are any tools
>to do the bulk of the work for me.
>

Take a look at some of the third-party tools for bulk editing:

Free:
http://www3.bc.sympatico.ca/starthere/findandreplace
Find and Replace: http://www.rickworld.com
Speed Ferret: http://www.moshannon.com
Total Access Analyzer: http://www.fmsinc.com

For this I'd suggest Speed Ferret.

John W. Vinson[MVP]

Access/Jet supports two sql syntaxes, 'SQL Server' and
'Jet'. If you are using ADO.NET with dynamic SQL, you
will be using the 'SQL Server' syntax, so you won't need
to make any changes at all - none of that stuff like # date
delimiters will affect you at all.

If you are using stored procedures, they can't be converted
at all. Access/Jet lets you use the key word 'procedure', but
in fact all you can store are simple create/update/delete statements.

create/update/delete SQL can be stored in an MDB
database using the 'SQL Server' syntax, but it's not convenient
to do so in Access 2000. If you want to do that, you should
either use Access 2003, or script the statements so that you can
use ADO.NET to create them.

(david)


"Liming" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I'm developing an asp.net application and recently, the company decided
> to support customers with only ms access (lots of share hosting users
> don't have the luxury of sql server, and my company doesn't want to
> miss out these users since we are getting a lot of requests from these
> share hosting users).
>
> Anyway, most of my app's quries are fairly straight forward, insert,
> update, delete.. but there are a few sql quries are a bit more
> complicatd (dynamic sql concatnations and stuffs), I'm wondering if
> anybody here knows a decent third party tool that can convert downward
> from sql server 2000 to ms access?
>
> Thanks a lot.
>

Thanks again guys.

Yeah, I'm using Enterprise Library 2.0 (jan 2006) for the data access
layer and store procedures.

Most of the store procedures are okay, very simple, but there are quite
a few that are dyanmically created inside the store procedures based on
the parameters passed in.

For now, I'm going ot manually edit each one and hope I can get it to
work. For the ones I can't get away, I'll just construct them in C#
before I pass into access.

Thanks again everybody, wish you a great new year.


david@epsomdotcomdotau wrote:
> Access/Jet supports two sql syntaxes, 'SQL Server' and
> 'Jet'. If you are using ADO.NET with dynamic SQL, you
> will be using the 'SQL Server' syntax, so you won't need
> to make any changes at all - none of that stuff like # date
> delimiters will affect you at all.
>
> If you are using stored procedures, they can't be converted
> at all. Access/Jet lets you use the key word 'procedure', but
> in fact all you can store are simple create/update/delete statements.
>
> create/update/delete SQL can be stored in an MDB
> database using the 'SQL Server' syntax, but it's not convenient
> to do so in Access 2000. If you want to do that, you should
> either use Access 2003, or script the statements so that you can
> use ADO.NET to create them.
>
> (david)
>
>
> "Liming" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi,
> >
> > I'm developing an asp.net application and recently, the company decided
> > to support customers with only ms access (lots of share hosting users
> > don't have the luxury of sql server, and my company doesn't want to
> > miss out these users since we are getting a lot of requests from these
> > share hosting users).
> >
> > Anyway, most of my app's quries are fairly straight forward, insert,
> > update, delete.. but there are a few sql quries are a bit more
> > complicatd (dynamic sql concatnations and stuffs), I'm wondering if
> > anybody here knows a decent third party tool that can convert downward
> > from sql server 2000 to ms access?
> >
> > Thanks a lot.
> >