ADO 64-bit Access

In 64-bit Windows, there are two ODBC Data Source Administrators: a 32-bit and a 64-bit version.
The ODBC Data Source Administrator that is accessible in the Control Panel, under Administrative Tools, is the same sweet bitness of Windows. In other words, on 32-bit Windows, it is the 32-bit administrator; on 64-bit Windows, it is the 64-bit administrator.

On 64-bit Windows, you can execute 32-bit and 64-bit applications transparently on a platform.

ODBC Administrator 64-bit

But if you want to access a ODBC data link to 64-bit you will see only SQL-Server in ODBC administrator 64bit on Win11 to get your ODBC connection string (replace with actual connection details).

So we have to install the 64-bit drivers first:

To download the ODBC driver for ACCESS or Excel that allows you to open .accdb or *.mdb databases for Windows 11, you can follow these steps:

1. Visit the Microsoft Download Center https://support.microsoft.com/en-us/office/download-and-install-microsoft-365-access-runtime-185c5a32-8ba9-491e-ac76-91cbe3ea09c9

2. Look for the Microsoft Access Database Engine 2016 Redistributable.
or direct with https://www.microsoft.com/en-us/download/details.aspx?id=54920&irgwc=1
Check the exe name: accessdatabaseengine_X64.exe

3. Download the version that matches your system (32-bit or 64-bit). 4. After downloading, run the
above installer to install the ODBC drivers and you get more names than just SQL Server:

After download from the Redistributable 2016

Then you can build the data source name in maXbox5 or Data Source Administrator, in maXbox go to menu ../Options/ADO SQL Workbench with the use connection string as the result below :

• Provider=MSDASQL;Persist Security Info=False;Extended Properties=”DSN=mX5detailbase64;
• DBQ=C:\maxbox\maxbox4\maxbox4\examples\detail.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;”

Data Source Name Builder

Then you will test the connection and open Table1 in the workbench source viewer:

ADO DB 64 bit in view for modify

Microsoft ODBC Driver for Access and SQL Server is a crucial component for developing native-code applications that connect to SQL Server and Azure SQL Database. It provides runtime support for applications using native code APIs to establish connections with SQL Server or Access. We can test this in a script with late binding as COM or Ole Automation. Ole Automation, originally known as OLE Automation and later renamed to simply Automation, is an interprocess communication mechanism IPC created by Microsoft.
It is based on a subset of the Component Object Model (COM). :

Function OpenConnection(ConnectionString: AnsiString): Integer; var ADODBConnection: OleVariant; rs: Olevariant; sqlQuery: string; begin ADODBConnection:= CreateOleObject('ADODB.Connection'); ADODBConnection.CursorLocation:= 3; // User client ADODBConnection.ConnectionString:= ConnectionString; Result := 0; try ADODBConnection.Open; sqlQuery:= 'SELECT * FROM Table1'; rs:= ADODBConnection.Execute(sqlQuery) while Not rs.EOF do begin writeln(rs.Fields('LastName').Value); rs.MoveNext; end; except Result := -1; finally //clean up rs.close; ADODBConnection.close; end; end;

Function OpenConnection(ConnectionString: AnsiString): Integer;
var ADODBConnection: OleVariant; rs: Olevariant;
      sqlQuery: string;
begin
  ADODBConnection:= CreateOleObject('ADODB.Connection');
  ADODBConnection.CursorLocation:= 3; // User client
  ADODBConnection.ConnectionString:= ConnectionString;
  Result := 0;
  try
    ADODBConnection.Open;
    sqlQuery:= 'SELECT * FROM Table1';
    rs:= ADODBConnection.Execute(sqlQuery)
    while Not rs.EOF do begin
       writeln(rs.Fields('LastName').Value);
       rs.MoveNext;
    end;   
  except
    Result := -1;
  finally  //clean up
    rs.close;
    ADODBConnection.close;  
  end;
end;

Let’s explore an example of using Ole Automation in maXbox or VBA (Visual Basic for Applications) to read data from an ODBC (Open Database Connectivity) table. In thisscripting scenario above, we’ll assume you have an ODBC data source set up, and we’ll use an ADO (ActiveX Data Objects) connection to retrieve data with CreateOleObject(‘ADODB.Connection’).

The code creates an ADO connection using the specified ODBC driver.

The same goes for MySQL connector and an update to 8.03 ANSI or Unicode drivers:

install mySqL Drivers 8.3

adoquery:= TAdoDataSet.Create(self);
with adoquery do begin
  cachesize:= 1500;
  commandType := cmdText;
  commandText:= mysql; //'select * from user'; //mysql;
  connectionString:= 
   'Provider=MSDASQL.1;Persist Security Info=False;Data Source=' + 'AD64'
    //odbcDsn;
    //'Provider=MSDASQL;DSN=' + odbcDsn + ';Uid=root';//;Pwd=***';
  writeln ('connectionString : ' + connectionString);    
  try
    Open;
  except   
    Writeln('Database can not be opened')
  end;

It opens the connection, executes a query (selecting all data from a table), and loops through a result set. You can customize the query and column names according to your specific use case in our example Table1 and LastName. We can also reduce the Connection String after stored the data link properties:

CSTRING2= ‘Provider=MSDASQL.1;Persist Security Info=False;Data Source=mX5detailbase64’;

ConnectionString:= CSTRING2;

try iReturn:= OpenConnection(ConnectionString); if (bMessage) then begin if (iReturn = 0) then begin (Application.MessageBox(utf8toansi('Connection OK!'),utf8decode('Information'),MB_OK)) writeln('Connection OK!'); end else if (iReturn = -1) then Application.MessageBox(utf8toansi('Connection Error!'),'Error', MB_ICONERROR+MB_OK); end;

try
  iReturn:= OpenConnection(ConnectionString);
  if (bMessage) then begin
    if (iReturn = 0) then begin
      (Application.MessageBox(utf8toansi('Connection OK!'),utf8decode('Information'),MB_OK))
       writeln('Connection OK!');
    end else if (iReturn = -1) then
      Application.MessageBox(utf8toansi('Connection Error!'),'Error', MB_ICONERROR+MB_OK);
  end;

Lima SBB Re 4/4 I 10046 & Roco DB E10 472 as a Train Database

Unicode Tests with Charsets

Create DB at runtime with OLE DB

I found a solution for this problem. The issue I described in my question occured basically due to the incompatibility of the Microsoft.Jet.OLEDB.4.0 driver in 64 bit OS.

After installing the OLE DB 12/16 Drivers from Microsoft Access Database Engine 2016 Redistributable you get new Data Link Properties

After Install msi package

Then you can call another OLE DB Object CreateOleObject(‘ADOX.Catalog’); with the compatible Provider as Provider=Microsoft.ACE.OLEDB.12.0;

function CreateAccessDatabase(FileName: string): string;
var  cat: OLEVariant;
begin
  Result := '';
  try
    cat:= CreateOleObject('ADOX.Catalog');
    //cat.Create('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + FileName + ';'); old 32 bit
    cat.Create('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' + FileName + ';');  //64-bit
    cat:= NULL;
  except
    writeln(ExceptionToString(ExceptionType, ExceptionParam));
    writeln('ADOX.Catalog create failed ');
    //on e: Exception do Result := e.message;
  end;
end;

Originally published at http://maxbox4.wordpress.com on February 9, 2024.