Cannot open Access database.

[ranny]

Hi,

I haven't managed to solve this from a long time ago and wondered if anyone can shed some light on this issue.  I am trying to create a program that will read a *.mdb database and provide various options to view and edit the content.

The code I have is this (got it on the web somewhere):-

Code: Pascal 
   //make ODBC connection
    ODBCConnection1.Driver := 'Microsoft Access Driver (*.mdb, *.accdb)';
    ODBCConnection1.Params.Add('DBQ=.\id931.mdb');      // or specify full path to mdb file
    ODBCConnection1.Params.Add('Locale Identifier=1031');
    ODBCConnection1.Params.Add('ExtendedAnsiSQL=1');
    ODBCConnection1.Params.Add('CHARSET=ansi');
    ODBCConnection1.Connected := True;
    ODBCConnection1.KeepConnection := True;
 
    //transaction
    SQLTransaction1.DataBase := ODBCConnection1;
    SQLTransaction1.Action := caCommit;
    SQLTransaction1.Active := True; //not applied to ms access(false)
 
    SQLQuery1.DataBase := ODBCConnection1;
    SQLQuery1.UsePrimaryKeyAsKey := False;

I get the following response in the error:-

......

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified etc.


It works on one Windows 11 machine and not another.  I have tried to update ODBC drivers but it always reports the same problem.

Also, I am sure it worked on my usual laptop at some point and then just reverted to the same issue.

Any advice would be very helpful.

Thanks

[Zvoni]

either
ODBCConnection1.Driver := '{Microsoft Access Driver (*.mdb, *.accdb)}';

Or leave out "Driver" empty, and add it directly as a Param
ODBCConnection1.Params.Add('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}');

--> https://www.connectionstrings.com/microsoft-access-odbc-driver/
And don't go with relative paths. Go for full path for DBQ

[ranny]

Hi,

Thanks for your suggestions.  Both do not work and I get the same error.

Actually I copied this issue from a post from some time back.  The error actually reads:-

Could not connect with connection string "DRIVER={....} then [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified;

Yet it works fine on another computer....

[Zvoni]

Then specify full path to mdb: https://wiki.freepascal.org/MS_Access#DSN-less_connection

[ranny]

Tried that as well and it doesn't work.

This is a project I started some time ago and I could never get it to work.  It was abandoned for a while but I tried it again a few months ago and it worked fine, and I am sure that it was on my current laptop but it doesn't work now on the same laptop, although it does on my "works" laptop.

[wp]

Access is a nasty beast regarding bitness (32bit vs 64 bit) because only one bitness is allowed on a system. So, if you have 32-bit Access and build a 64-bit application Access will refuse to open the database (IIRC with a similar error message to what you report).

So, make sure that the Access DLLs on you system have the correct bitness. You can download the Microsoft Access Database Engine from https://www.microsoft.com/en-us/download/details.aspx?id=54920 for both bitnesses, but you can install only one (at least for Access 2016)

[af0815]

open the ODBC Administrator of windows. Create a working System-DNS and try with this first. If the connectiontest ist working, try to connect to this System-DSN. If this works, you can try to make your own at runtime. A good hint is, to seach with regedit for the System-DSN and look what parameters it is realy using. 

with the system-DSN you are seeing what kind of bitness you have to use.

[ranny]

Great thanks, I have a couple of things to work with and hope it works.

I will let you know how I get on.

Thanks as always.

[VB_Lazarus]

Hi,

you have to keep few things in mind.
If the application is 32bit then you have to use the 32bit driver ('Microsoft Access Driver (*.mdb)'). This driver is almost installed.
If the application is 64bit then you have to use the 64bit driver ('Microsoft Access Driver (*.mdb, *.accdb)'). This driver must be installed (for examples runtimes a post above, or MSAccess 64bit).
If I made a program then I use this following:

  {$IFDEF CPU64}
      ODBCDRIVER := 'Microsoft Access Driver (*.mdb, *.accdb)';
      ODBCRegistryKey := 'SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers';
  {$ELSE}
      ODBCDRIVER := 'Microsoft Access Driver (*.mdb)';
      ODBCRegistryKey := 'SOFTWARE\WOW6432Node\ODBC\ODBCINST.INI\ODBC Drivers';
  {$ENDIF}

I check the Registry if the driver is available.

[Khrys]

I've been using this function for a while now. It's not the most elegant approach, but it does the job:

Code: Pascal  [Select][+][-]

1. function OpenConnectionODBC(const Filename: String; const Password: String; out Error: String): TODBCConnection;
2. const
3.   MS_ACCESS_SQL_DRIVERS: array of String = ('Microsoft Access Driver (*.mdb)',
4.   { I'm starting to understand the...    }  'Microsoft Access Driver (*.mdb, *.accdb)',
5.   { ...resentment people hold towards... }  'Microsoft Access Paradox Driver (*.db)',
6.   { ...Microsoft. Access has reliably... }  'Microsoft Access Text Driver (*.txt, *.csv)',
7.   { ...been the worst piece of...        }  'Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)',
8.   { ...software I've had to work with    }  'Microsoft dBase Driver (*.dbf)');
9. var                                       // When the 32-bit driver is already present on the system...
10.   DriverString: String;                   // ...the 64-bit version won't let itself be installed graphically; it must...
11. begin                                     // ...be done via CMD in quiet mode. After it's installed it will, at some...
12.   Error := '';                            // ...random point, decide to simply stop working unless reinstalled
13.   for DriverString in MS_ACCESS_SQL_DRIVERS do try  // And even when it's "working" for once, it'll cause spurious...
14.     Result := TODBCConnection.Create(Nil);          // ...and obviously corrupt thread stack traces to appear in GDB
15.     Result.Driver := DriverString;                  // An actual excerpt (this may go on for 300+ levels):
16.     with Result.Params do begin                     // #332 0xcccc76eb in ?? ()
17.       Add(Format('ExtendedAnsiSQL=%s', ['1']));     // #333 0xcccccccc in ?? ()
18.       Add(Format('CharSet=%s', ['UTF-8']));         // #334 0xcccccccc in ?? ()
19.       Add(Format('UID=%s', ['admin']));             // #335 0x105425ff in ?? ()
20.       Add(Format('DBQ=%s', [Filename]));            // #336 0xcccc76eb in ?? ()
21.       if Password <> '' then begin                  // #337 0xcccccccc in ?? ()
22.         Add(Format('PWD=%s', [Password]));
23.       end;
24.     end;
25.     Result.KeepConnection := True;
26.     Result.Open();
27.     Error := ''; // No exception was thrown, skip remaining tries
28.     break;
29.   except on Ex: Exception do begin
30.     Error += Ex.Message.Replace(Password, '[****]') + LineEnding;
31.     Result.Close(True);
32.     FreeAndNil(Result);
33.   end end;
34.   if Result <> Nil then Error := '';
35. end;