How to Backup / Export from SQL Server to Access
SQL Server databases are not easy for regular users to back up and restore and are not easy to send via email or online file sharing. SQL Server databases cannot be accessed from a laptop that is disconnected from your workplace network.
You can use this procedure inside Instrument Manager to export your SQL Server project to a new Access database to create a backup, or to send to a client or subcontractor.
NOTE: This procedure does not merge with existing contents in an Access database; it exports the data from the current project into a new empty Access database file.
Procedure
From the
menu, select .Confirm (or edit if desired) the file name for the exported / back-up Access database file then click
.The project name followed by ".exported.mdb" is suggested as a default file name, e.g. for a project named "Project1" the suggested database filename is "Project1.exported.mdb".
If you have chosen or enter the name of a database file that already exists, you will be asked to confirm that you want to overwrite the file.
The previous file will be completely overwritten if you click
to overwrite.NOTE: Usually this means that you have used the wrong file name, unless you are intentionally overwriting a previous export from the same project.
The contents of the original SQL Server database will then be copied into the new Access database. Wait for the process to complete.
The procedure finishes with a message confirming the export and the name of the exported database file.
You can now store the exported Access database as a backup or send it to subcontractor.
Wide tables in SQL Server
If the SQL Server database contained tables with more than 255 columns then these tables will have been split to successfully export them to Access, which has a limit of 255 columns in a table.
Split tables have the original name with a suffix "__SplitX", where X is a consecutive number starting at 1 for the first split.
The complimentary procedure, Migrate from Access to SQL Server, will re-combine this type of split table in an exported Access database into a single table in SQL Server.