Date: 02/22/05 (SQL Server)    Keywords: database, asp, sql

I have this really obnoxious scenario where I need to take the table structure from one of my SQL Server 2000 databases, and copy all of the tables down to a new MS Access database. There's a LARGE NUMBER of tables, so recreating each table in Access manually should be a last resort.

I've looked into using the DTS wizard, but when Exporting Data, I cannot simply execute the "CREATE TABLE" statements without copying the data it seems? That, or if I modify the DTS package by hand afterwards, DTS sets up each table as a separate command stream, so I have to manually modify each to rip out the data transfer portion. This is extremely irksome.

Then I speculated on generating the "CREATE TABLE" T-SQL via SQL Server and running that against the Access db. Not being terribly familiar with Access, I'm unaware of any actual SQL front-end. But from my 'net application dev days, I have an old ASP page which I'd use on remote servers to modify client Access dbs that I otherwise didn't have access to. So thought of using that as my front-end, but then realized that the T-SQL generated code would NOT be Access friendly due to differing datatypes!

So I'm growing rather irritated with this situation and thought I'd ask folks out here for advice on this? Any ideas would be greatly appreciated!

Source: http://www.livejournal.com/community/sqlserver/18878.html