Access 2003 --> SQL 2000

I need to migrate an Access Database to SQL Server 2000.

The DB is Access 2003.

The Database has numerous tables and numerous select queries with
parameters included for efficient 'selecting'.

Now, I would like to migrate the whole lot to SQL 2000. I have tried
the Microsoft upsizing wizard, which creates the tables fine, but puts
the relevent queries as User Defined Functions, which is not really
what I want.

I'd prefer it if they were actually created as Stored Procedures (to
maintain the SQL Variables) so I can use ADO's Command Object to select
individual recordsets based on parameters.

Does anyone know of a tool that does this well ?

There are around 100 select queries and I don't really fancy having to
rewrite them all.

Thanks,

Leslie

RE: Access 2003 --> SQL 2000

Leslie,

I am not aware of any tool that will do this - I'm not saying there isn't
one out there, just that I haven't seen it.

However, one possible route would be to view each query in SQL view and then
cut and paste that into SQL's Query Anaylzer. This will not be autmatic, but
it will be faster than re-coding each one by hand.

HTH,
John


"www.icuknet.co.uk" wrote:

> I need to migrate an Access Database to SQL Server 2000.
>
> The DB is Access 2003.
>
> The Database has numerous tables and numerous select queries with
> parameters included for efficient 'selecting'.
>
> Now, I would like to migrate the whole lot to SQL 2000. I have tried
> the Microsoft upsizing wizard, which creates the tables fine, but puts
> the relevent queries as User Defined Functions, which is not really
> what I want.
>
> I'd prefer it if they were actually created as Stored Procedures (to
> maintain the SQL Variables) so I can use ADO's Command Object to select
> individual recordsets based on parameters.
>
> Does anyone know of a tool that does this well ?
>
> There are around 100 select queries and I don't really fancy having to
> rewrite them all.
>
> Thanks,
>
> Leslie
>
>

Re: Access 2003 --> SQL 2000

www.icuknet.co.uk wrote:
> I need to migrate an Access Database to SQL Server 2000.
>
> The DB is Access 2003.
>
> The Database has numerous tables and numerous select queries with
> parameters included for efficient 'selecting'.
>
> Now, I would like to migrate the whole lot to SQL 2000. I have tried
> the Microsoft upsizing wizard, which creates the tables fine, but puts
> the relevent queries as User Defined Functions, which is not really
> what I want.
>
> I'd prefer it if they were actually created as Stored Procedures (to
> maintain the SQL Variables) so I can use ADO's Command Object to
> select individual recordsets based on parameters.
>

I also know of no tool to do this, but I would like to add that I think this
is a bad idea. You will not be taking advantage of the features provided by
T-SQL stored procedures if you merely duplicate all the views and action
queries that Access (actually, Jet) forces you to use. Stored procedures can
encapsulate multiple queries, as well as performing flow and transaction
control that is not possible in Jet saved queries. As a stopgap, you will
certainly be able to get away with merely duplicating your Jet queries
(keeping in ming that you will need to replace your VBA functions and some
JetSQL syntax), but if you really wish to take advantage of all that SQL
Server brings to the table, you need to look at re-engineering how you
currently do things.

Here are some helpful articles:

http://www.aspfaq.com/show.asp?id=2182
http://www.aspfaq.com/show.asp?id=2214
http://www.aspfaq.com/show.asp?id=2201

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Access 2003 --> SQL 2000

In article ,
reb01501@NOyahoo.SPAMcom (Bob Barrows [MVP]) wrote:

> this is a bad idea. You will not be taking advantage of the features
> provided by T-SQL stored procedures if you merely duplicate all the

Thanks, what I did where the Access queries had parameters, I created
Stored Procedures by copying the code and replacing with the relevant @
variables and using Views for ones that did not require them.

Everything seems to be working rather well.

Thanks,

Leslie

Re: Access 2003 --> SQL 2000

www.icuknet.co.uk wrote:
> In article ,
> reb01501@NOyahoo.SPAMcom (Bob Barrows [MVP]) wrote:
>
>> this is a bad idea. You will not be taking advantage of the features
>> provided by T-SQL stored procedures if you merely duplicate all the
>
> Thanks, what I did where the Access queries had parameters, I created
> Stored Procedures by copying the code and replacing with the relevant
> @ variables and using Views for ones that did not require them.
>
> Everything seems to be working rather well.
>

I'm sure it is. But if you did not combine queries into single stored
procedures where possible, then you are not taking full advantage of the
capabilities provided by SQL Server.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"