Access SQL Server Remote
Locate the SQL Server database that contains the data that you want import. Contact the administrator of the database for connection information.
Identify the tables or views that you want to import. You can import multiple objects in a single import operation.
Review the source data and keep the following considerations in mind:
Access does not support more than 255 fields in a table, so Access imports only the first 255 columns.
The maximum size of an Access database is 2 gigabytes, minus the space needed for system objects. If the SQL Server database contains many large tables, you might not be able to import them all into a single .accdb file. In this case, you might want to consider linking the data to your Access database instead.
Access does not automatically create relationships between related tables at the end of an import operation. You must manually create the relationships between the various new and existing tables by using the options in the Relationships window. To display the Relationships window:
Click the File tab, and then on the Info tab, click Relationships.
Identify the Access database into which you want to import the SQL Server data.
Ensure that you have the necessary permissions to add data to the Access database. If you don't want to store the data in any of your existing databases, create a blank database by clicking the Filetab, and then on the New tab, click Blank Database.
Review the tables, if any exist, in the Access database.
The import operation creates a table with the same name as the SQL Server object. If that name is already in use, Access appends "1" to the new table name — for example, Contacts1. (If Contacts1 is also already in use, Access will create Contacts2, and so on.)
Note: Access never overwrites a table in the database as part of an import operation, and you cannot append SQL Server data to an existing table.
Import the data
Open the destination database.
On the External Data tab, in the Import & Link group, click ODBC Database.
Click Import the source data into a new table in the current database, and then click OK.
In the Select Data Source dialog box, if the .dsn file that you want to use already exists, click the file in the list.
I need to create a new .dsn file
Note: The steps in this procedure might vary slightly for you, depending on the software that is installed on your computer.
Click New to create a new data source name (DSN).
The Create New Data Source Wizard starts.
In the wizard, select SQL Server in the list of drivers, and then click Next.
Type a name for the .dsn file, or click Browse to save the file to a different location.
Note: You must have write permissions to the folder to save the .dsn file.
Click Next, review the summary information, and then click Finish to complete the wizard.
Create a New Data Source to SQL Server dialog box appears.
Type a description of the data source in the Description box. This step is optional.
Under Which SQL Server do you want to connect to, in the Server box, type or select the name of the SQL Server to which you want to connect, and then click Next to continue.
You might require information from the SQL Server database administrator, such as whether to use Microsoft Windows NT authentication or SQL Server authentication. Click Next to continue.
If you want to connect to a specific database, ensure that the Change the default database to check box is selected. Then select the database that you want to work with, and then click Next.
Review the test results, and then click OK to close the dialog box.
If the test was successful, click OK again, or click Cancel to change your settings.
Click OK to close the Select Data Source dialog box.
Access displays the Import Objects dialog box.
Under Tables, click each table or view that you want to import, and then click OK.
If the Select Unique Record Identifier dialog box appears, Access was unable to determine which field or fields uniquely identify each row of a particular object. In this case, select the field or combination of fields that is unique for each row, and then click OK. If you are not sure, check with the SQL Server database administrator.
Access imports the data. If you plan to repeat the import operation later, you can save the import steps as an import specification and easily rerun the same import steps later. You require Microsoft Office Outlook installed on your computer to create a task.
Click Close under Save Import Steps in the Get External Data - ODBC Database dialog box. Access finishes the import operation and displays the new table or tables in the Navigation Pane.
If you want to save the import as a task for reuse, continue to the next section.
Save and use the import settings
Note: You must have Microsoft Office Outlook installed to create a task.
Under Save Import Steps in the Get External Data - ODBC Databasedialog box, select the Save import steps check box. A set of additional controls appears.
In the Save as box, type a name for the import specification.
Type a description in the Description box. This step is optional.
To perform the operation at fixed intervals (such as weekly or monthly), select the Create Outlook Task check box. This creates a task in Microsoft Outlook 2010 that lets you run the specification.
Click Save Import.
If Outlook is not installed, Access displays an error message when you click Save Import.
Note: If Outlook 2010 is not configured properly, the Microsoft Outlook 2010 Startup wizard starts. Follow the instructions in the wizard to configure Outlook.
Optionally, you can create an Outlook task. It can be useful to create a task in Outlook if you want to run the import operation at regular or recurring intervals. However, if you do not create a task, Access still saves the specification.
Create an Outlook task
If you selected the Create Outlook Task check box, Access starts Office Outlook 2010 and displays a new task. Follow these steps to configure the task:
Note: If Outlook is not installed, Access displays an error message. If Outlook is not configured properly, the Outlook Configuration Wizard starts. Follow the instructions in the wizard to configure Outlook.
Review and modify the task settings, such as the Start date, Due date and Reminder.
To make the import task a recurring event, click Recurrence and fill in the appropriate information.
Click Save and Close.
Run a saved task
In the Outlook Navigation Pane, click Tasks, and then double-click the task that you want to run.
On the Task tab, in the Microsoft Access group, click Run Import.
Switch back to the Access window, and then press F5 to refresh the Navigation Pane.
Right-click the imported table in the Navigation Pane, and then click Design View on the shortcut menu. Review the field data types and other field properties.
Link to SQL Server data
Since data is stored in tables, when you link to a table or view in a SQL Server database, Access creates a new table (often known as a linked table) that reflects the structure and contents of the source object. You can change data either in SQL Server, or in Datasheet view or Form view from Access. The changes that you make to data in one location are reflected in the other. However, if you want to make structural changes, such as removing or changing a column, you must do so from the SQL Server database, or from an Access project that is connected to that database. You cannot add, delete, or change the fields in a linked table while you are working in Access.
Prepare to link
Locate the SQL Server database that has the data to which you want to link. Contact the database administrator for connection information.