mORMot Open Source

chopseo

Member

Registered: 2020-07-07

Posts: 5

Newbie connecting to an .accdb Database

Hello,

Im just getting started with the mORMot framework and need to connect to an .accdb database.

Any sample code or link would be great.

I did some connection attempts with the TOleDBJetConnectionProperties class without success.

Usually I connect with the Microsoft.ACE.OLEDB.12.0 provider to this kind of database.

I did a search and found nothing here on the forum.

Im on Windows 7 with Delphi 2006.

Thx and greeting
chopseo

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,603

Website

Re: Newbie connecting to an .accdb Database

Did you try with TOleDBJetConnectionProperties?

chopseo

Member

Registered: 2020-07-07

Posts: 5

Re: Newbie connecting to an .accdb Database

Thx for your fast response!

Yes, I did some tests with TOleDBJetConnectionProperties without luck.

I experimented in the GitHub\mormot\SQLite3\Samples\15 - External DB performance project and tried to alter the test to connect with my .accdb database:

Test( TOleDBJetConnectionProperties,'....\TestDb.accdb','','','','',true);

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,603

Website

Re: Newbie connecting to an .accdb Database

Please try the new TOleDBACEConnectionProperties class.
Check https://synopse.info/fossil/info/f1af277af9

chopseo

Member

Registered: 2020-07-07

Posts: 5

Re: Newbie connecting to an .accdb Database

Thx for providing the new connection class!

I tried it, but unfortunately without success:

Im still changing the mormot\SQLite3\Samples\15 - External DB performance project for my tests.

Im calling the new with class with
Test(TOleDBACEConnectionProperties,'...\TestDb.accdb','','','','',true);

If my TestDb doesnt exists, I got an exception at

function TOleDBConnectionProperties.CreateDatabase: boolean;
var Catalog: _Catalog;
    DB: OleVariant;
begin
.
.
.
        DB := Catalog.Create(ConnectionString); // <- exception here
.
.
. 
end;

If my TestDb exists, I got an exception at

function TOleDBConnectionProperties.GetSchema(const aUID: TGUID;
  const Fields: array of RawUTF8; var aResult: IRowset): boolean;
var i, res, n: integer;
    C: TOleDBConnection;
    SRS: IDBSchemaRowset;
    PG, OG: PGUID;
    PI, OI: PInteger;
    Args: array of Variant;
begin
.
.
.
  C.fSession.QueryInterface(IDBSchemaRowset,SRS); // exception here
.
.
.
end;

My connection string is:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=...\TestDb.accdb;Initial Catalog=ace.1;User Id=;Password=;

Let me know, how I can give you more detailed information about the problem.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,603

Website

Re: Newbie connecting to an .accdb Database

1) please follow the forum rules, and don't post a lot of code in the forums

2) what is the exception full message? (this is the main useful information)

chopseo

Member

Registered: 2020-07-07

Posts: 5

Re: Newbie connecting to an .accdb Database

Im sorry for posting code. I tried to delete all the irrelevant part.

Not posting the exception message was kind of stupid of me though.

This is the exception message, if the database not exists (translated from german):
Exception at $756DC5AF. Error in an OLE DB process consisting of several steps. Check the individual OLE DB status values, if available. Data was not processed.

If the database exists, I get the message
Exception class EOleSysError with message 'OLE error 80040E21'

If I can do any more tests let me know.

Thx and greetings.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,603

Website

Re: Newbie connecting to an .accdb Database

If you follow http://www.adopenstatic.com/faq/80040e21.asp information, it means:

Scenario 2 - Error occurs when opening a connection
The error will occur when you call the .Open() method on an ADO Connection object. The error is caused by attempting to specify a value for an unsupported property in the Connection Object's connection string.

Please try to find out which correct connection string does pass.

chopseo

Member

Registered: 2020-07-07

Posts: 5

Re: Newbie connecting to an .accdb Database

I had success with connecting and creating an accdb Database!

The problem was the "Initial Catalog=ace.1" part of the connection string, which the GitHub\mormot\SQLite3\Samples\15 - External DB performance project added automatically.

I started an own test project with the call

Props := TOleDBACEConnectionProperties.Create( '....\TestDb.accdb', '', '', '' );

and got the working connection string:

'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=....\TestDb.accdb;User Id=;Password=;'

The fDBMS: TSQLDBDefinition property of Props: TOleDBACEConnectionProperties is still dJet. Is this correct?

Also I noticed that there is no call TOleDBACEConnectionProperties.RegisterClassNameForDefinition; in the initialization section of the SynOleDB unit.

Everything worked though.

Thx for your support and greetings.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,603

Website

Re: Newbie connecting to an .accdb Database

We have now registered the TOleDBACEConnectionProperties class for settings.
Check https://synopse.info/fossil/info/bcc427fb32

Thanks for the feedback!