Downloading a changing SQL Server Database structure to Access

• Peter Clements-368786

  SSC Enthusiast

  Points: 139

  More actions

  August 7, 2009 at 1:29 am

  #96101

  Hi,

  I am working on a project that requires export of a sql server 2008 database to access mdb. The difficulties lie in that the sql server database structure may be modified from time to time, but the requirement is to be able to generate the new format sql server db without software change.

  I believe this precludes use of standard ssis, that would require the table bindingsto be done up front.

  I have a few ideas on how parts of the problem could be done, but gaps in providing the complete solution.

  1. I could use SMO to script the sql database structure, then I need somehow to turn the sql server script into a script to create the access db

  2. I could try to programatically use SSIS to 'discover' the sql database structure.

  Any pointers as to the best way to proceed gratefully received; it seems 99.99% of the documents online describe access -> sql server upsizing, and not flows in the opposite direction.

  Many Thanks

  Peter