Cannot open Access database.
lazarus / 16-Databases

----------------------------------------------------------------------
ranny · 2024-09-11 11:04

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 · 2024-09-11 11:21

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 · 2024-09-11 11:35

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 · 2024-09-11 11:39

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

----------------------------------------------------------------------
ranny · 2024-09-11 12:12

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 · 2024-09-11 12:24

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 · 2024-09-11 12:26

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 · 2024-09-11 13:01

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 · 2024-09-11 18:33

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:

Quote  {$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}
Quote
I check the Registry if the driver is available.

----------------------------------------------------------------------
H₂SO₄ · 2024-09-12 04:59

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