Any tools avaiable to convert Sql server 2000 to ms access?

L

Liming

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

Douglas J. Steele

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 ')
 
L

Liming

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
 
J

John Vinson

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]
 
G

Guest

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)
 
L

Liming

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top