By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,171 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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!
Nov 12 '05 #2

P: n/a
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,constraints 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!
Nov 12 '05 #3

P: n/a
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.

sv******@yahoo.com (Sujeet) wrote in message news:<c6**************************@posting.google. com>...
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

Nov 12 '05 #4

P: n/a
-----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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBP+Jtx4echKqOuFEgEQIg3gCfTwSeUY+34w10rAmikR39TC 8LxnwAnjbL
VguTvxC6PIfmOONdPJB/iY/y
=coIr
-----END PGP SIGNATURE-----
Sujeet Varakhedi wrote:
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,constraints 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!


Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.