SQL 2000 to MS Access

[gcbgctb10]

A variation of the normal route
Working in SQL 2000 there is an import wizard that will
take a MS Access database and upgrade this to SQL 2000,
This is the normal upgrade path that Microsoft adopt.
The issue I have is that I have a SQL 2000 database but
want to downgrade (sic) this to MS Access, but can not
see what is the best way to do it.

1. I dont want the data in the current database (50GB
in size exc transaction log!)
2. I have the scripts that created the database

I need a way to run the scripts to create the objects in
an Access database, the DTS from SQL to MSAccess will
create the tables but misses out the contraints and
triggers, it also wants to copy the data.

I started to look at a VBA module that reads the script
files to create the tables and view ( views = Queries) etc.
builds a sqlstring and then use a docmd.runSQL sqlstring
but there must be another way!
I but am having an issue with triggers and constraints.


Points

1. I can take the view that right now I don't care
at this time about the data in the tables, what I want is
a blank database with the schema in place
2. I am looking at the tables, indexes, constraints,
triggers etc.
3. Views will become MSAccess queries
4. Stored procedures will become VBA code and I
think I have an approach to this, well for 3000 of them
anyway as they are in a set format the others I will have
to look at one at a time.

As to the data I can link to the database once it is in
place and copy the meta tables I am interested in, the
Client GUI can do the rest.
Volume of effort

From the database I am using as a sample
select type , count(*) from sysobjects group by type
gives type
TR 95
U 768
S 19
K 24
D 90
V 102
P 3244

I am open to ideas

Many thanks

Tony

 

[Joe Fallon]

1. There may be advanced ($expensive) tools that handle this scenario.
Embarcadero, ERWin, etc.

2. There may be cheap free tools that could do something like this.
I saw one a few years ago that output SQL Server, Oracle and Access.
I don't recall if it reverse engineered them though.

3. Why do you want to do this? 50GB of data is a lot to "throw away".
Access maxes out at 2GB.
You could always use MSDE (which is the exact same engine as SQL Server!)
It also maxes out at 2GB (on a new database.)
You could do a backup of the existing database and restore it to a PC with
MSDE.
Then all the tables, triggers, views and Sprocs will still exist. (The
restore may even work with all the data - not sure if that bug was ever
fixed.)

Even if you purge the data and "start fresh" you at least have the identical
structure.
Then you only need to build and Access Front End to it. EIther using an mdb
and linked tables or an ADP and a direct connection.

 

[gcbgctb10]

Joe
Thanks
Its the creation of the schema that I am interested
in.
The backgound is that it is a commercail application that
is running on SQL, the sample database I am using contains
three years of transaction history, hence the size.

What I was looking for was a means to create the schema in
an MDB file to reduce the cost of ownership, and aim it to
a smaller user which would not need a 50GB database or the
cost ! The GUI exists - it points at SQL/Oracle/Sybase and
it can point to an Access database but needs some of the
calls tweeking in the DB Layer - work is in hand on that
front.
That is not the issue here, but rather the creation and
mantenace of the database is e.g. create or alter a table
or query due to function upgrades.
what would be good is a Window to an MDB that would
accept the scripts in the same way as SQL Query Analyzer,
i.e. multiple CREATE TABLE items in a single file.
Factoring in and out the VBA modules is easy!

regards
Tony

 

[Joe Fallon]

Then you should just use MSDE!

It is free.

It is SQL Server.

You don't have to change a thing!