• Article

OLE DB Provider for Jet

The Microsoft OLE DB Provider for Jet provides an OLE DB interface to Microsoft Access databases, and allows SQL Server 2005 and later distributed queries to query Access databases and Excel spreadsheets.

To download the Microsoft Office 2007 drivers, see 2007 Office System Driver: Data Connectivity Components.

Tip

To connect to Microsoft Access, SQL Server must start the Microsoft Access Database Engine. Unlike most other providers, Microsoft Access is not a light-weight provider, but is the entire Microsoft Access Database Engine. Opening Microsoft Access in the SQL Server process space can cause failures due to lack of disk, processor, or memory resources. Errors include, "Cannot initialize the data source object of OLE DB provider." To avoid out of memory errors, configure the provider to open outside of the SQL Server memory process space.

To run the Microsoft Access outside of the SQL Server memory space

  1. In the SQL Server Management Studio Object Explorer, expand the SQL Server name, expand Server Objects, expand Linked Servers, expand Providers, right-click the Microsoft Access provider Microsoft.ACE.OLEDB.12.0 or Microsoft.Jet.OLEDB.4.0 and then click Properties.

  2. In the Provider Options box, uncheck Allow inprocess.

  3. Click OK.

To create a linked server to access an Access database

  1. Execute sp_addlinkedserver to create the linked server, specifying Microsoft.Jet.OLEDB.4.0 as provider_name, and the full path name of the Access .mdb database file as data_source. The .mdb database file must reside on the server. data_source is evaluated on the server, not the client, and the path must be valid on the server.

    For example, to create a linked server named Nwind that operates against the Access database named Nwind.mdb in the c:\mydata directory, execute:

    sp_addlinkedserver 'Nwind', 'Access', 'Microsoft.Jet.OLEDB.4.0', 
   'c:\mydata\Nwind.mdb'

  2. To access a nonsecured Access database, SQL Server logins trying to access an Access database should have a login mapping defined to the user name Admin without a password.

    The following example enables access for the local user Joe to the linked server named Nwind.

    sp_addlinkedsrvlogin 'Nwind', false, 'Joe', 'Admin', NULL

    To access a secured Access database, configure the registry by using Registry Editor to use the correct Workgroup Information file that is used by Access. Use Registry Editor to add the full path name of the Workgroup Information file that is used by Access to this registry entry:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB

  3. After the registry entry is configured, use sp_addlinkedsrvlogin to create login mappings from local logins to Access logins:

    sp_addlinkedsrvlogin 'Nwind', false, 'Joe',
    'AccessUser', '034fhd99dl'
Security noteSecurity Note

This example does not use Windows Authentication. Passwords will be transmitted unencrypted. Passwords may be visible in data source definitions and scripts saved to disk, in backups, and in log files. Never use an administrator password in this kind of connection. Contact your network administrator for security guidance specific to your environment.

Access databases do not have catalog and schema names. Therefore, tables in an Access-based linked server can be referenced in distributed queries using a four-part name of the form linked_server**...**table_name.

The following example retrieves all rows from the Employees table in the linked server named Nwind.

SELECT * 
FROM Nwind...Employees

To create a linked server against an Excel spreadsheet

The Microsoft OLE DB Provider for Jet 4.0 can be used to access Microsoft Excel spreadsheets.

  • To create a linked server that accesses an Excel spreadsheet, use the format of the following example:

    EXEC sp_addlinkedserver EXCEL,
     'Jet 4.0',
     'Microsoft.Jet.OLEDB.4.0',
     'c:\data\MySheet.xls', 
      NULL,
     'Excel 5.0;'

GO

  • To access data from an Excel spreadsheet, associate a range of cells with a name. A named range can be accessed by using the name of the range as the table name. The following query can be used to access a named range called SalesData using the linked server set up in the previous example.

    SELECT *
FROM EXCEL...SalesData
GO

When you insert a row into a named range of cells, the row is added after the last row that is part of the named range of cells. Therefore, if you want to insert row rA after the column heading, associate the column heading cells with a name and use that name as the table name. The range of cells will automatically grow as rows are inserted.

To set up a linked server against a formatted text

Microsoft OLE DB Provider for Jet can be used to access and query text files.

  • To create a linked server for accessing text files directly without linking the files as tables in an Access .mdb file, execute sp_addlinkedserver, as shown in the following example.

    The provider is Microsoft.Jet.OLEDB.4.0 and the provider string is 'Text'. The data source is the full path name of the directory that contains the text files. A Schema.ini file must exist in the same directory as the text files. A Schema.ini file describes the structure of the text file. For more information about how to create a Schema.ini file, see the Jet Database Engine documentation.

    --Create a linked server.
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0', 
   'Microsoft.Jet.OLEDB.4.0',
   'c:\data\distqry',
   NULL,
   'Text'
GO

--Set up login mappings.
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL
GO

--List the tables in the linked server.
EXEC sp_tables_ex txtsrv
GO

--Query one of the tables: file1#txt
--by using a four-part name. 
SELECT * 
FROM txtsrv...[file1#txt]