SQL 2005 Export to Access?

[BridgeRE]

I have SQL 2005 databases I would like to export to Access to include the indexes. I can export the data successfully using the wizard, but there is no choice to export the indexes. How does one complete this task?

Thank you.

 

[sblocher]

You can execute Access DDL commands from T-SQL code to create the indexes in the Access mdb. You'll need to create a linked server to the mdb. Then you can execute DDL commands against that linked server.

For example, you can run this command from SSMS, where the linked server name is 'MyAccessMDB':

EXECUTE ('CREATE INDEX MyStateIndex
ON Addresses
(
State ASC
)') ON MyAccessMDB;

There are several resources for Jet DDL command reference; here's one from MS KB:

http://support.microsoft.com/kb/180841

You can obviously do this from a VB program or something too, if you want. And there may be other ways to do this; this is the one i use.

 

[BridgeRE]

Thank you for your answer, but I just don't understand, I am not educated in programming, nor advanced SQL things. That just looks like jibberish to me. There is no "quick and easy" way to export indexes from a SQL 2005 database to Access? The database export is simple enough, I wonder why is this not an export feature?

 

[sblocher]

No quick and dirty way that i know of short of scripting out the database and running the script against Access -- as i've suggested. The wizard takes care of data only. It won't even handly indexes and other such structure

You may want to try the SQL Server Programming forum, as your question is really more of a programming question than an admin one.