Export SQL 2005 Data and Indexes 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. What is the easiest way to complete this task?

Thank you.

 

[bborissov]

From SQL Server to ACCESS?
If that was a reverse translatio I could understand you, BUT to change SQL Server with ACCESS, grrrr!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[genomon]

Try importing into Access instead of exporting from SQL Server. Why not just link the SQL Server tables to an Access mdb & be done with it?

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[BridgeRE]

I know it sounds like a dumb thing to do, but we host "live" databases (SQL) and "training" databases (currently SQL). I'm using a perfectly great PowerEdge Server to host "training" databases, which do not get used much, but have to be available for the users, and just do not want to waste hardware on them, so I want to export them all to Access to free up the server.

 

[genomon]

If you import into Access, you can choose the fields(columns) you want as an index (or you can just open the Access table in design view & set the properties there).

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[SQLSister]

I would not recommend a training database using Access instead of SQl server as the back end because they use differnt flavors of SQL. Why rewrite your code to work in Access? Then any changes in the future will have to be tested in both SQL server and Access environments. If you try to write queries that will work in both environments, you may end up with code that is less than optimal becasue you can;t take advantage of features that SQL Server has that Access does not. Actaully having a small server for this is starting to sound much cheaper than the atuclity of maintaining a nightmare like this.

"NOTHING is more important in a database than integrity." ESquared

 

[genomon]

What she said. Nice call....

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[BridgeRE]

No, we have a a few different versions of our software, the less expensive one uses Access backend, either at the end user location or via hosted on a Terminal Server, which is what we do. I even have blank databases that already have the indexes in place, but get an error when trying to export from SQL to them. We have a special .mdw file to open the Access databases. Whne I join Access to that workgroup, I get an error about workgroup file in use.

 

[fredericofonseca]

In todays world, you should keep it with SQL Server, and drop Access.

If price is the issue, then for the smaller clients install SQL Server Express (or MSDE if still using 2000). Both free if you have a developer license of SQL Server.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[BridgeRE]

Our Access platform, works just fine for some of our smaller customers.

 

[SQLSister]

Personally this sounds like an Access issue to me and should be moved to that forum.


"NOTHING is more important in a database than integrity." ESquared

 

[JoeAtWork]

If price is the issue, then for the smaller clients install SQL Server Express (or MSDE if still using 2000). Both free if you have a developer license of SQL Server.

Wait - isn't SQL Server Express free for everyone, regardless of having a developer license?

A quick Google search seems to confirm this.

Although if you want to bundle it with your application, it seems you need to register with Microsoft:

http://www.microsoft.com/sql/editions/express/redistregister.mspx

However, registration isn't onerous and seems to be open to everyone.