Microsoft Jet OLE DB 4.0 connection strings

Excel 97

Standard alternative

Try this one if the one above is not working. Some reports that Excel 2003 need the exta OLEDB; section in the beginning of the string.

OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

"SELECT * FROM [Sheet1$a5:d]", start picking the data as of row 5 and up to column D.

Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance. Please also note that adding the IMEX=1 option might cause the IMEX feature to set in after just 8 rows. Use IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work.

If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file."

A workaround for the "could not decrypt file" problem

Excel 2000

Standard alternative

Try this one if the one above is not working. Some reports that Excel 2003 need the exta OLEDB; section in the beginning of the string.

OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

"SELECT * FROM [Sheet1$a5:d]", start picking the data as of row 5 and up to column D.

Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance. Please also note that adding the IMEX=1 option might cause the IMEX feature to set in after just 8 rows. Use IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work.

If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file."

A workaround for the "could not decrypt file" problem

Excel 2002

Standard alternative

Try this one if the one above is not working. Some reports that Excel 2003 need the exta OLEDB; section in the beginning of the string.

OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

"SELECT * FROM [Sheet1$a5:d]", start picking the data as of row 5 and up to column D.

Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance. Please also note that adding the IMEX=1 option might cause the IMEX feature to set in after just 8 rows. Use IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work.

If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file."

A workaround for the "could not decrypt file" problem

Excel 2003

Standard alternative

Try this one if the one above is not working. Some reports that Excel 2003 need the exta OLEDB; section in the beginning of the string.

OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

"SELECT * FROM [Sheet1$a5:d]", start picking the data as of row 5 and up to column D.

Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance. Please also note that adding the IMEX=1 option might cause the IMEX feature to set in after just 8 rows. Use IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work.

If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file."

A workaround for the "could not decrypt file" problem

Textfile

Delimited columns

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;Extended Properties="text;HDR=Yes;FMT=Delimited";

The delimiter can be specified in the registry at the following location:
HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Jet \ 4.0 \ Engines \ Text
"Format" = "TabDelimited"
or
"Format" = "Delimited(;)"

How to Use JET in 64 bit environments

Fixed length columns

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;Extended Properties="text;HDR=Yes;FMT=Fixed";

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

To specify each columns length use the Schema.ini file. See description below.

Important note!
The quota " in the string needs to be escaped using your language specific escape syntax.
c#, c++   \"
VB6, VBScript   ""
xml (web.config etc)   "
or maybe use a single quota '.

More info on using the schema.ini file

Access 97

With database password

This is the connection string to use when you have an access database protected with a password using the Set Database Password function in Access.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:Database Password=MyDbPassword;

Some reports of problems with password longer than 14 characters. Also that some characters might cause trouble. If you are having problems, try change password to a short one with normal characters.

Exclusive

Used to get exclusive access to the database if you, for instance, want to let the application be able to reset the database password.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Mode=Share Exclusive;User Id=admin;Password=;

Access 2000

With database password

This is the connection string to use when you have an access database protected with a password using the Set Database Password function in Access.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:Database Password=MyDbPassword;

Some reports of problems with password longer than 14 characters. Also that some characters might cause trouble. If you are having problems, try change password to a short one with normal characters.

Exclusive

Used to get exclusive access to the database if you, for instance, want to let the application be able to reset the database password.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Mode=Share Exclusive;User Id=admin;Password=;

Access 2002

With database password

This is the connection string to use when you have an access database protected with a password using the Set Database Password function in Access.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:Database Password=MyDbPassword;

Some reports of problems with password longer than 14 characters. Also that some characters might cause trouble. If you are having problems, try change password to a short one with normal characters.

Exclusive

Used to get exclusive access to the database if you, for instance, want to let the application be able to reset the database password.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Mode=Share Exclusive;User Id=admin;Password=;

Access 2003

With database password

This is the connection string to use when you have an access database protected with a password using the Set Database Password function in Access.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:Database Password=MyDbPassword;

Some reports of problems with password longer than 14 characters. Also that some characters might cause trouble. If you are having problems, try change password to a short one with normal characters.

Exclusive

Used to get exclusive access to the database if you, for instance, want to let the application be able to reset the database password.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Mode=Share Exclusive;User Id=admin;Password=;