Recent

Author Topic: Problem connecting to a MS Access Database with ODBC  (Read 67930 times)

timmermanj

  • New member
  • *
  • Posts: 39
Problem connecting to a MS Access Database with ODBC
« on: December 08, 2009, 10:45:16 am »
I've searched in this forum how to connect to a MS Access DB with ODBC.

I've tried this with following code:

procedure TForm1.FormCreate(Sender: TObject);
begin
  ODBCConnection1.Connected:= False;
  ODBCConnection1.Driver:= 'Microsoft Access Driver (*.mdb)';
  ODBCConnection1.Params.Add('DBQ=C:\BackUp.mdb');
  ODBCConnection1.Connected:= True;
end;


When i try this i get following error. (See picture in attachment)

Could someone please tell me what i did wrong?

Thanks a lot!

LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: Problem connecting to a MS Access Database with ODBC
« Reply #1 on: December 10, 2009, 07:20:25 am »
Try set DatabaseName property to name of your ODBC DataSource (As defined in ControlPanel/ODBC) and omit Driver property (I had never used it) ...

timmermanj

  • New member
  • *
  • Posts: 39
Re: Problem connecting to a MS Access Database with ODBC
« Reply #2 on: December 10, 2009, 03:24:59 pm »
Could someone please show me a simple example, just how to connect to a MS Access DB?
I really don't have a clue.

I would really appreciate the help!

Thanks a lot!!!


theo

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 1890
Re: Problem connecting to a MS Access Database with ODBC
« Reply #3 on: December 10, 2009, 04:10:56 pm »
I've tried this once and it worked.
I've simply set up a System DSN (using ODBC Administrator) and then entered the DSN in the DatabaseName property of the ODBCConnection and set Login Prompt to false.

I don't know it this helps.

stanks

  • New member
  • *
  • Posts: 9
Re: Problem connecting to a MS Access Database with ODBC
« Reply #4 on: January 10, 2010, 11:20:33 am »
this example works great under winblowz but not under linux :(

procedure TMKB.Button1Click(Sender: TObject);
var
  S: String;
  conn: TODBCConnection;
  query: TSQLQuery;
  transaction: TSQLTransaction;

begin
  conn := TODBCCOnnection.Create(nil);
  query := TSQLQuery.Create(nil);
  transaction := TSQLTransaction.Create(nil);
  try
    try
//      conn.HostName := '127.0.0.1';
//      conn.DatabaseName := 'diary'; {replace this with the name of your database}
      conn.Transaction := transaction;
//      conn.UserName:= '';
//      conn.Password:= '';
// for winblowz
      conn.Params.Add ('DBQ=C:\Lazarus-Pascal\odbc-example\dg.mdb');
      conn.Driver := 'Microsoft Access Driver (*.mdb)';
      query.DataBase := conn;
      query.UsePrimaryKeyAsKey:=false;
      query.SQL.Text := 'SELECT * FROM dg';
      query.Open;
      S := '';
      while not query.EOF do
      begin
        S := S + query.FieldByName('sifra').AsString + #13#10;
        query.Next;
      end;
    finally
      query.Free;
      conn.Free;
    end;
  except
    on E: Exception do
      ShowMessage(E.message);
  end;
  Memo1.Text:= S;
end;

I only change some things in this example. Orig example is on wiki (i don't have url now...search for it)

btw can somebody tell me why this example fails on linux (i changed the path to db file)?


theo

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 1890

dobedani

  • Newbie
  • Posts: 4
Re: Problem connecting to a MS Access Database with ODBC
« Reply #6 on: March 22, 2010, 10:10:42 am »
Hi folks,
I'm new to Lazarus but not new to Delphi. I tried the above example on Windows XP SP2, specifying the driver and the DBQ. As far as I understand, I don't need to do anything in the ODBC Data Source Administrator and in fact I even want to avoid that. Unfortunately, I am getting this error when I try to open the connection:

Could not connect with connection string: DSN=access; DRIVER={Microsoft Access Driver (*.mdb)};DBQ=D:\MyPathWithoutSpaces\MyDB.mdb; ;". ODBC error details: LastReturnCode: SQL_ERROR; Record 1: SqlState: IM002; NativeError:0; Message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified;

Am I overlooking something?

BTW, the executable I got for my rather simple app has size 20000 kb whereas before with Delphi it had size 750 kb. Why this great difference? TIA

Kind regards,
Dobedani

theo

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 1890
Re: Problem connecting to a MS Access Database with ODBC
« Reply #7 on: March 22, 2010, 10:42:03 am »
BTW, the executable I got for my rather simple app has size 20000 kb whereas before with Delphi it had size 750 kb. Why this great difference?

http://wiki.lazarus.freepascal.org/Lazarus_Faq#Why_are_the_generated_binaries_so_big.3F

dobedani

  • Newbie
  • Posts: 4
Re: Problem connecting to a MS Access Database with ODBC
« Reply #8 on: March 22, 2010, 12:50:44 pm »
Hi folks,

Theo: thanks for the reply. Can somebody also answer my other question? TIA

Regards, Dobedani

Wodzu

  • Full Member
  • ***
  • Posts: 171
Re: Problem connecting to a MS Access Database with ODBC
« Reply #9 on: March 22, 2010, 01:00:38 pm »
Have you read article given to you by theo?

It says:

Connecting to MS Access

On Windows, you can use

Driver = 'Microsoft Access Driver (*.mdb)'
Params.Strings = (
  'DBQ=C:\path\to\my\database.mdb'
)

Note: The MS Access ODBC driver seems not to support the SQLPrimaryKeys API function, hence UpdateIndexDefs will fail. Currently, you must set SQLQuery.UsePrimaryAsKey:=false; to avoid an exception until code has been added that tests for the availability of the function in the driver.


And I see that in your connection string you are setting up a DSN. Don't do this unless you configured it in ODBC manager.

dobedani

  • Newbie
  • Posts: 4
Re: Problem connecting to a MS Access Database with ODBC
« Reply #10 on: March 22, 2010, 02:12:13 pm »
Hi Wodzu,

Thanks a lot for your reply! I've got my application working. Now I trust Lazarus a lot more.

Kind regards,
Dobedani

thierrybo

  • Full Member
  • ***
  • Posts: 123
Re: Problem connecting to a MS Access Database with ODBC
« Reply #11 on: March 22, 2010, 08:58:15 pm »

BTW, the executable I got for my rather simple app has size 20000 kb whereas before with Delphi it had size 750 kb. Why this great difference? TIA


Besides the Wiki link , I post a chart of what base sizes you can achieve :

http://www.lazarus.freepascal.org/index.php/topic,7916.msg41310.html#msg41310

AdrianW

  • Newbie
  • Posts: 2
Re: Problem connecting to a MS Access Database with ODBC
« Reply #12 on: March 25, 2010, 11:02:02 am »
Thanks to stanks (for the instant poetry opportunity, and..)
for your example of ODBC Access DB connectivity.
At first I got "transaction not set", but then I added the line
transaction.DataBase := conn;
after setting the conn.Driver, and it worked *nicely*
Lazarus lives!

snesska

  • New member
  • *
  • Posts: 10
Re: Problem connecting to a MS Access Database with ODBC
« Reply #13 on: April 01, 2010, 05:31:11 pm »
Hi, everybody!
I have placed components on the form (as in the picture in the attachment).
I have configured the components so that DBGrid would link up with the base in Microsoft Access (the base has only one table). In control panel, I have configured SystemDSN, FileDSN and Drivers as it says in http://wiki.lazarus.freepascal.org/ODBCConn.
Lazarus doesn't report errors, but I can't see the data from the table (Table is in MS Access) in DBGrid, Navigator also doesn't work.
My operating system is Windows XP SP3, I have tried both Access 2003 and 2007.
Does anybody have any idea what seems to be the problem?
Also, can you give me some suggestions about some other way to view and update data in MS Access base?
Are there any tutorials that explain that in detail.
Kind regards,
Snesska

Zoran

  • Hero Member
  • *****
  • Posts: 1389
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: Problem connecting to a MS Access Database with ODBC
« Reply #14 on: April 01, 2010, 06:27:53 pm »
You should use either SystemDNS or FileDNS, not both.

From the picture you provided not much can be seen -- we can't see how you set properties of your components.

Can your ODBCConnection component connect in design time (when you set connected to True in ObjectInspector)?
Then, what happens if you set Query1.Active to True in ObjectInspector?

I just tried with SystemDNS and at first the Query coud not open, but when I set the Query's UseKeyAsKey to False it worked (Win XP sp3, Access 2003).