Importing SQL Tables

To import a table from another database system that supports ODBC SQL (such as SQL Server or Oracle), you must first have the ODBC driver for that database installed on your computer. Your computer must also be linked to the network that connects to the computer running SQL Server from which you want to import data, and you must have an account on that server. Check with your system administrator for information about correctly connecting to the computer running SQL Server.

If you have SQL Server 2008 installed or have downloaded and installed SQL Server 2008 Express Edition, which you can download from www.microsoft.com/express/sql/default.aspx, you already have SQL Server at your disposal. One of the best ways to be sure SQL Server is running on your computer is to use the SQL Server Configuration Manager. You can start the Configuration Manager from the Windows Start menu in the Configuration Tools folder under Microsoft SQL Server 2008. You can also start the Configuration Manager by running C:\Windows\System32\SQLServerManager.msc. In the Configuration Manager, select SQL Server 2008 Services and be sure the SQL Server (MSSQLSERVER) service is marked as Running. If it is not running, right-click the service name and click Start on the shortcut menu.

To import data from a SQL table, do the following:

1. Open the Access database that will receive the SQL data. If that database is already open, close all open objects so that you see only the Navigation pane.
2. On the External Data tab, in the Import & Link group, click the ODBC Database button. Access opens the Get External Data - ODBC Database dialog box. Make sure the Import The Source Data Into A New Table In The Current Database option is selected and then click OK.
3. Access opens the Select Data Source dialog box, from which you can select the data source that maps to the computer running SQL Server that contains the table you want to import.
   You can select a data source name (.dsn) file that you created previously, or click the Machine Data Source tab, to see data sources that are already defined for your computer.
   
   Troubleshooting
   Access won't use ODBC for all file types. Notice that the Machine Data Source tab lists installed sources for dBASE, Access, and Microsoft Excel. Access will not let you use ODBC for dBASE, Excel, and Access because it uses its own, more efficient, direct connection via its database engine.
   
   If you don't see the data source you need, see "Creating a Data Source to Link to an ODBC Database," for instructions. After you select a data source, click OK.
4. When Access connects to the server, you'll see the Import Objects dialog box, which lists the available tables on that server.
5. From the list of tables or list of files, select the ones you want to import. If you select a table name in error, you can click it again to deselect it or you can click the Deselect All button to start over. Click OK to import the SQL tables you selected.
6. If the import procedure is successful, the new table will have the name of the SQL table. If Access finds a duplicate table name, it will generate a new name by adding a unique integer to the end of the name, as explained earlier about dBASE files.

Note: You've no doubt noticed by now that the different databases use different style conventions (dbo.newstore, Newstore, NEWSTORE) for table names.

In general, Access converts SQL data types to Access data types, as shown in Table-2.

SQL Data Type 	Converts To Access Data Type
CHAR[ACTER] 	Text, or Memo if more than 255 characters in length
VARCHAR 	Text, or Memo if more than 255 characters in length
TEXT 		Memo
TINYINT 	Number, Field Size property set to Byte
SMALLINT 	Number, Field Size property set to Integer
INT 		Number, Field Size property set to Long Integer
REAL 		Number, Field Size property set to Double
FLOAT 		Number, Field Size property set to Double
DOUBLE 		Number, Field Size property set to Double
DATE 		Date/Time
TIME 		Date/Time
TIMESTAMP 	Binary(1)
IMAGE 		OLE Object

¹The Access Database Engine (ACE) supports a Binary data type (raw hexadecimal), but the Access user interface does not. If you link to a table that has a data type that maps to Binary, you will be able to see the data type in the table definition, but you won't be able to successfully edit this data in a datasheet or form. You can manipulate Binary data in Visual Basic.