Importing Tables from SQL Server to Access with Keys and Indices preserved

[Sujeet]

Hey guys,
I want to import a database from SQL Server 2000 to MS Access with all
indices and keys along with the tables. DTS does not export indices
and keys, only the structure and the data.

When used "Import External Data" through Access(linking through ODBC)
it creadted liked tables, however with indices and keys. Can remove
these links and preserve only th stucture back. How do I do it.

Thanks

[Rich P]

Re: Importing Tables from SQL Server to Access with Keys and Indices preserved

Upsizing from Access to Sql Server is not uncommon. But downsizing from
Sql Server to Access is a different story. As far as RDBMS's go, Sql
Server supports a much wider array of RDBMS things than Access (since
Sql Server is a real RDBMS and has not extended support (in a manner of
speaking) for VBA inside the Sql Server mdf). Sql Server is designed
specifically for RDBMS stuff. Access has a much smaller support for
RDBMS but supports other stuff like VBA/OLE automation (from within the
mdb) and so forth. They are different tools. Additionally, Access is
the size of a molecule compared to Sql Server. If your Sql Server
tables don't exceed the limitations of Access (less than 255 columns,
less than 1 gig of data - less than 200 megs of data really) you can
duplicate such a table in Access, but you can't duplicate clustered
indices and so forth.

I would create tables in Sql Server to match the exact structure of the
tables in Access. Populate these tables with your Actual Sql Server
data. Then you can use DTS to export this data to your Access tables.
If you have a lot of data, you will have to create a lot of mdb's.

Anyway, this is how I would approach it (if I had to). Disclaimer: it
is possible that there is a utility that I am not aware of that can do
what you need. It is also possible that there are methods in Access/Sql
Server that I am not aware of for doing this. So the above is just a
suggestion.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Sujeet Varakhedi]

Re: Importing Tables from SQL Server to Access with Keys and Indices preserved

Rich P,
Thats true in every sense of it. Our database in really huge housing
around 350 tables and surely more that 3GB of data. We just needed the
structure of these tables in MS Access for training our clients.
I did get the structure, keys and indices through to access from SQL
Server but required a lot of manual effort. Here are the steps. I will
be making a tool to do this if I get the scripts

1)Create linked tables in access connecting them to the SQL Server
Database through ODBC. In this case the strcture and the other
objects(like indices,keys,co nstraints etc. are also imported)
2)Still these are links and not tables. Individually go to each table
design view and click "Save As" to a table with a diffrent name.
3)Delete the linked tables and compact the database.

I will need scripts for all the steps to create a tool. I am sure I will
find it.
I will post it as soon as as I am done.

Thanks
Sujeet



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ted Theodoropoulos]

Re: Importing Tables from SQL Server to Access with Keys and Indices preserved

DTS would be the only facility i would think would accommodate this
and it obviously doesn't. the problem is sql server has different
data types and speaks a different dialect of sql. in access you don't
have clustered indexes and fill factors, etc so there has to be some
utility to translate these special indexes in to generic access
indexes.

maybe there's some shareware utility out there that does. i think
that's your only hope. if u do find a way to do this, please post
your solution.

svsujeet@yahoo. com (Sujeet) wrote in message news:<c60cc882. 0312180949.1e82 abff@posting.go ogle.com>...[color=blue]
> Hey guys,
> I want to import a database from SQL Server 2000 to MS Access with all
> indices and keys along with the tables. DTS does not export indices
> and keys, only the structure and the data.
>
> When used "Import External Data" through Access(linking through ODBC)
> it creadted liked tables, however with indices and keys. Can remove
> these links and preserve only th stucture back. How do I do it.
>
> Thanks[/color]

[MGFoster]

Re: Importing Tables from SQL Server to Access with Keys and Indicespreserve d

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can use the SQL Server scripting utility (right-click the Database
name in Enterprise Manager & select "All Tasks" > "Generate SQL
Script") it will generate SQL DDL scripts that you can use in Access
to create tables & indexes & constraints. When you run the utility
you will have to limit what can be created - nothing Access can't
"understand ." The results of the utility can be saved to a text file,
edited (to get rid of anthing Access doesn't understand) and run as
queries in Access to create the structure of the SQL Server db. Then
use DTS to transfer the data to the Access tables.

HTH,

MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP+Jtx4echKq OuFEgEQIg3gCfTw SeUY+34w10rAmik R39TC8LxnwAnjbL
VguTvxC6PIfmOON dPJB/iY/y
=coIr
-----END PGP SIGNATURE-----


Sujeet Varakhedi wrote:
[color=blue]
> Rich P,
> Thats true in every sense of it. Our database in really huge housing
> around 350 tables and surely more that 3GB of data. We just needed the
> structure of these tables in MS Access for training our clients.
> I did get the structure, keys and indices through to access from SQL
> Server but required a lot of manual effort. Here are the steps. I will
> be making a tool to do this if I get the scripts
>
> 1)Create linked tables in access connecting them to the SQL Server
> Database through ODBC. In this case the strcture and the other
> objects(like indices,keys,co nstraints etc. are also imported)
> 2)Still these are links and not tables. Individually go to each table
> design view and click "Save As" to a table with a diffrent name.
> 3)Delete the linked tables and compact the database.
>
> I will need scripts for all the steps to create a tool. I am sure I will
> find it.
> I will post it as soon as as I am done.
>
> Thanks
> Sujeet
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]