Using the Enterprise version of XE2, which includes native dbExpress ODBC support, I have successfully created a "Connection" where the Database Name is the name of the System DSN, and the Password is the MS Access database password.
I can click Test Connection on the Modify Connection page, and the Database Explorer reports Test connection succeeded.
Dragging and dropping the connection from the Data Explorer creates a TSQLConnection with the Driver property set to ODBC and the Params collection being:
drivername=ODBC
database=myaccessdbDSN
password=accessdbpwd
While this is great for testing, I'd rather not require an ODBC System DSN to be created for it to work.
From what I remember, a DSN can just be replaced by a ConnectionString of the correct format, and can usually be figured out by looking at the text of the DSN. Unfortunately, the Data Explorer in XE2 only works with System DSN's, and not File DSN's.
What should the ConnectionString of the TSQLConnection be when using ODBC to connect to a MS Access database file?
EDIT 1
Given a form with a databound grid, a TDataSource, a TSQLQuery with appropriate SQL command, and a private TSQLConnection (not form component), the following works:
FSQLConnection := TSQLConnection.Create(nil);
FSQLConnection.DriverName := 'ODBC';
FSQLConnection.LoginPrompt := false;
FSQLConnection.Params.Values['Database'] := 'myaccessdbDSN';
FSQLConnection.Params.Values['Password'] := 'accessdbpwd';
FSQLConnection.Connected := true;
SQLQuery1.SQLConnection := FSQLConnection;
SQLQuery1.Active := true;
I see the results of the query in the grid.
What I need to do is replace the 'myaccessdbDSN' with a connection string. If I do the following, which seems to be the way it should work, I get a "Connection Could Not Open" error from the ODBC Driver Manager.
FSQLConnection := TSQLConnection.Create(nil);
FSQLConnection.DriverName := 'ODBC';
FSQLConnection.LoginPrompt := false;
FSQLConnection.Params.Values['ConnectionString'] := 'Provider=Microsoft.ACE.OLEDB.12.0;'+
'Data Source=myaccessdb.mdb;'+
'Jet OLEDB:Database Password=accessdbpwd;';
FSQLConnection.Connected := true;
SQLQuery1.SQLConnection := FSQLConnection;
SQLQuery1.Active := true;
Please note that normally, when setting the DriverName property at runtime, you have to set some of the other properties that are normally defined in the dbxdrivers.ini file. If the DriverName is set to 'MSSQL', these are:
GetDriverFunc=getSQLDriverMSSQL
LibraryName=dbxmss.dll
VendorLib=sqlncli10.dll
But when the DriverName is set to 'ODBC', these are blank. Is this a fault with the dbxDriver for ODBC or not?
I've looked through the help at http://docwiki.embarcadero.com/RADStudio/Berlin/en/Setting_Up_TSQLConnection but it does not tell me anything I haven't already tried, and it does fill me with confidence when the sentence
To display the Connection Editor, double-click the TSQLConnection component.
does not work in XE2, XE5, Seattle or Berlin.
EDIT 2
The whole reason I am looking at this is because the Open ODBC dbxExpress driver no longer works in Berlin. The following will work in XE2:
FSQLConnection.DriverName := 'DbxOpenOdbc';
FSQLConnection.GetDriverFunc := 'getSQLDriverODBCW';
FSQLConnection.LibraryName := 'dbxoodbc.dll';
FSQLConnection.VendorLib := 'odbcjt32.dll';
FSQLConnection.Params.Values['DriverPackageLoader'] := 'TDBXDynalinkDriverLoaderOpenOdbc';
FSQLConnection.Params.Values['IsolationLevel'] := 'ReadCommitted';
FSQLConnection.Params.Values['RowSetSize'] := '20';
FSQLConnection.Params.Values['Database'] := '?';
FSQLConnection.Params.Values['ConnectionString'] := 'DRIVER={Microsoft Access Driver (*.mdb)};'+
'DBQ=myaccessdb.mdb;'+
'UID=;'+
'PWD=accessdbpwd;'+
'DriverId=25;'+
'FIL=MS Access;';
But when the same code is run in Berlin, I get a "Duplicates not allowed" error, from the AddCommandFactory, which is related to this code in the Dbx34Drv unit, as below:
constructor TDbxOpenOdbcDriver.Create(DriverDef: TDBXDriverDef);
begin
{$IF CompilerVersion > 18.50}
inherited Create(DriverDef, TDBXDynalinkDriverLoader);
InitDriverProperties(TDBXOpenOdbcProperties.Create(DriverDef.FDBXContext));
// '' makes this the default command factory.
AddCommandFactory('', CreateCommandOpenOdbc);
//AddCommandFactory(TDBXCommandTypes.DbxMetaData
{$ELSE}
raise EDbxOODBCDriverError.Create('Not Implemented: TDbxOpenOdbcDriver.Create(DriverDef: TDBXDriverDef)');
{$IFEND}
end;
As the Open ODBC dbxExpress driver has not been maintained since 2013, and that XE2 and Berlin Enterprise come with their own ODBC dbxExpress driver (Data.DBXOdbc), I am wanting to change the properties and parameters to get the code that is working in XE2 to work with the native ODBC driver, and then check that it works in Berlin.
The thread at https://sourceforge.net/p/open-dbexpress/discussion/119359/thread/703de7d9/ indicates it should just be a matter of replacing the Open ODBC values with regular ODBC ones.
The table at http://docwiki.embarcadero.com/RADStudio/Berlin/en/DbExpress_Supported_Database_Management_Systems does not list any libraries for "Odbc", so I assume it would be blank.
EDIT 3 (AND ANSWER)
After some faffing about with the code, I have got the following to work in both XE2 Enterprise and Berlin Enterprise in order to open a MS Access database with a TSQLConnection object using the native ODBC dbxExpress driver.
FSQLConnectionAccess.DriverName := 'ODBC';
FSQLConnectionAccess.GetDriverFunc := 'getSQLDriverODBCW';
FSQLConnectionAccess.LibraryName := '';
FSQLConnectionAccess.VendorLib := 'odbcjt32.dll';
FSQLConnectionAccess.Params.Values['DriverPackageLoader'] := 'TDBXOdbcDriverLoader';
FSQLConnectionAccess.Params.Values['IsolationLevel'] := 'ReadCommitted';
FSQLConnectionAccess.Params.Values['RowSetSize'] := '20';
FSQLConnectionAccess.Params.Values['Database'] := '?';
FSQLConnectionAccess.Params.Values['ConnectionString'] := 'DRIVER={Microsoft Access Driver (*.mdb)};'+
'DBQ=myaccessdb.mdb;'+
'UID=;'+
'PWD=accessdbpwd;'+
'DriverId=25;'+
'FIL=MS Access;';
Note how the connection string used is nothing like the ones shown in http://www.connectionstrings.com, which is the main reason I don't use that site for Delphi development.
A similar change is required if you were using the DevArt SQL Server driver and want to change to the native MS SQL Server one:
// FSQLConnectionSQL.DriverName := 'DevArtSQLServer';
// FSQLConnectionSQL.GetDriverFunc := 'getSQLDriverSQLServer';
// FSQLConnectionSQL.LibraryName := 'dbexpsda40.dll';
// FSQLConnectionSQL.VendorLib := 'sqloledb.dll';
becomes
FSQLConnectionSQL.DriverName := 'MSSQL';
FSQLConnectionSQL.GetDriverFunc := 'getSQLDriverMSSQL';
FSQLConnectionSQL.LibraryName := 'dbxmss.dll';
FSQLConnectionSQL.VendorLib := 'sqlncli10.dll';
The reason we had been using Open ODBC and DevArt SQL Server drivers is that we originally had XE2 Professional, which did not include ODBC or SQL drivers as standard. Now that we are using Enterprise, that is no longer an issue. The only thing left to do is determine if the old and new drivers behave differently.
The answer to the original question on how to setup a DSN-less connection to a MS Access database in XE2 Enterprise using the ODBC driver is:
FSQLConnectionAccess.DriverName := 'ODBC';
FSQLConnectionAccess.GetDriverFunc := 'getSQLDriverODBCW';
FSQLConnectionAccess.LibraryName := '';
FSQLConnectionAccess.VendorLib := 'odbcjt32.dll';
FSQLConnectionAccess.Params.Values['DriverPackageLoader'] := 'TDBXOdbcDriverLoader';
FSQLConnectionAccess.Params.Values['IsolationLevel'] := 'ReadCommitted';
FSQLConnectionAccess.Params.Values['RowSetSize'] := '20';
FSQLConnectionAccess.Params.Values['Database'] := '?';
FSQLConnectionAccess.Params.Values['ConnectionString'] := 'DRIVER={Microsoft Access Driver (*.mdb)};'+
'DBQ=myaccessdb.mdb;'+
'UID=;'+
'PWD=accessdbpwd;'+
'DriverId=25;'+
'FIL=MS Access;';