Error connecting to MS Access (2010)
lazarus / 16-Databases

----------------------------------------------------------------------
MikeH · 2013-01-30 18:45

Hi Folks.  I'm using Lazarus to teach my A-Level computing students
programming and everything has been going great until we came to
hooking up our applications to a MS access 2010 database.  I spent a
couple of hours last night looking at the forums and saw some really
useful stuff but I still end up with the same error when it comes to
connecting = "Data source name not found and no default driver
specified".

Using lpc 1.0/fpc 2.6.0, 32-bit version of Access 2010 and 64-bit
Windows 7 Home Premium.  I'm hoping this is something to do with
drivers and not a silly syntax error

DB code snippet posted below - screenshot of error and the source
attached - in a txt file as no .pas allowed right?  I know there's a
lot more to getting this code doing what I need but I'm just trying to
get the connection going first.

procedure TForm1.Button1Click(Sender: TObject);
begin

       MyODBC.Driver := 'Microsoft Access Driver (*,mdb,*.accdb)';
       MyODBC.Params.Add('DBQ=D:\KitchenStuff.accdb');
       MyODBC.Transaction := MySQLTrans ;
       MySQLQuery.DataBase := MyODBC;
       MySQLQuery.UsePrimaryKeyAsKey:=false;
       MySQLQuery.PacketRecords:=-1; //retrieve all data at once
       MySQLQuery.SQL.Text := 'select * from item';
       MySQLQuery.Open;
end;

Thanks in advance for any advice.

Mike.

----------------------------------------------------------------------
BigChimp · 2013-01-31 08:10

Thanks for posting & nice that you use Lazarus for teaching

Before looking at your code, have you seen the wiki page:
http://wiki.lazarus.freepascal.org/MS_Access
(seems to be down right now, though).
Code there works for me using Access (2002).

Just to verify: you're using 32 bit Lazarus/FPC, I suppose? Then the
32 bit Access will fit (you need matching ODBC driver bitness) -
verify by checking the 32 bit (!) ODBC control panel, Drivers tab,
look if "Microsoft Access Driver (*,mdb,*.accdb)" is installed (should
be I suppose after a default Access install). If not, install the 32
bit Access runtime (or whatever it's called) from Microsoft.
If you're using 64 bit Lazarus, you'd need to install a 64 bit Access
runtime.
For convenience's sake, I'd stay with 32 bit Lazarus, especially
because there are some issues with using external DLLs in 64 bit
Windows Lazarus/FPC. These will not bite you doing Access dev but may
occur in other situations.

As for uploading snippets etc, the easiest way seems to go
Project=>Publish project into an empty directory, then zip that up and
attach it to a post. This way you give people the entire project.
If you then include an empty db, you've made it even easier for people
to have a look

Edit: added a link to a possible sample db in
http://wiki.lazarus.freepascal.org/MS_Access

----------------------------------------------------------------------
BigChimp · 2013-01-31 10:52

Could you test the program below? Perhaps you're having driver
issues...

Adapted the sample FPC db program at
http://wiki.lazarus.freepascal.org/Database_bug_reporting#FreePascal

and put the demo .accdb database linked to in
http://wiki.lazarus.freepascal.org/MS_Access
in the directory where the exe is.

this works:

Code: [Select]program dberror;

{$mode objfpc}{$H+}

uses
  {$IFDEF UNIX}{$IFDEF UseCThreads}
  cthreads,
  {$ENDIF}{$ENDIF}
  Classes, sysutils,
  sqldb,
  odbcconn {no native Access driver yet ;) };

{
Using Mondial demo database, see
http://wiki.lazarus.freepascal.org/MS_Access#Example_database
}

var
  Conn: TODBCConnection;
  Tran: TSQLTransaction;
  Q: TSQLQuery;
begin
  Conn:=TODBCConnection.create(nil);
  Tran:=TSQLTransaction.create(nil);
  Q:=TSQLQuery.Create(nil);
  try
    // Using dsn less example code on
http://wiki.lazarus.freepascal.org/MS_Access
    Conn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)'; //using
new .accdb file format driver (Access 2007+). Can handle mdb files as
well.
    // File path should work if it's in same directory as .exe:
   
conn.Params.Add('DBQ='+ExtractFilePath(ParamStr(0))+'mondial.accdb');
    // *END IDENTIFIYING INFO*
    Conn.Transaction:=Tran;
    Q.DataBase:=Conn;
    Conn.Open;
    // *EXAMPLE BUG TESTING CODE, REPLACE WITH YOUR OWN*
    Q.SQL.Text:='select now() as RightNow ';
    Q.Open;
    Q.Last; //force recordcount update
    writeln('recordcount: '+inttostr(q.RecordCount));
    Q.Close;
    // *END EXAMPLE BUG TESTING CODE*
    Conn.Close;
  finally
    Q.Free;
    Tran.Free;
    Conn.Free;
  end;
  writeln('Program complete. Press a key to continue.');
  readln;
end.
Compiling with fpc should work.

----------------------------------------------------------------------
MikeH · 2013-02-01 17:18

Hi Big Chimp.  First of all I'd like to thank you for taking the time
to reply - twice!  I was at a parents evening last night and didn't
get a chance to reply.  I've tried the sample you posted and even
downloaded the mondial 2010 d/b.  Lumped them all in the same folder
(pascal and d/b) but still no luck  - same error.

I am just downloading the 32-bit Access runtime install to see if this
makes any diff.  I am just replying now as I might have to nip out and
just wanted to let you know I'm listening!  Will get back to you after
the 32-bit driver install.....here's hoping.

Thanks again.  Mike.

----------------------------------------------------------------------
jamhitz · 2013-02-04 21:22

Hi,

I notice that you are using Windows 7 64-bit. I do not know whether
you have considered this:

If you run Control->Panel->Administrative Tools->ODBC you will notice
that by default the regular connections (Access, Excel, dBF...) are
not available. To activate these, you would need to run the 32-bit
version of ODBC. Windows 7 64bit comes with two versions:

The 32-bit version of the Odbcad32.exe file is located in the
%systemdrive%\Windows\SysWoW64 folder.The 64-bit version of the
Odbcad32.exe file is located in the %systemdrive%\Windows\System32
folder
To work around this problem, use the appropriate version of the ODBC
Administrator tool. If you build and then run an application as a
32-bit application on a 64-bit operating system, you must create the
ODBC data source by using the ODBC Administrator tool in
%windir%\SysWOW64\odbcad32.exe.

Ref: http://support.microsoft.com/kb/942976

JamHitz