Problem connecting to a MS Access Database with ODBC

[thierrybo]

I just tried with C:\Program Files\Fichiers communs\Borland Shared D2006\Data\bddemos.mdb with a file dsn and {Microsoft Access Driver} version 4.00.6305..00 and SQL := 'SELECT * FROM Customer'.

The DBgrid get populated at design time without any other quirk that adding fields manually to the sqlquery (edit: and setting components needed options as  Datasource/Dataset/active of course). Without I get an error.

[snesska]

Thank you for your quick answer. Unfortunately, I can't try that until now (I was on the trip), and now I must solve the problem...

Here is my situation:
I started from the begining:

I created SystemDSN named "zabazu2" with driver
"Microsoft Access Driver (*.mdb)"
 

Components properties are:

ODBCConnection1
DatabaseName:= zabazu2

SQLQuery1
Database:=ODBCConnection1
Datasource:=Datasource1
SQL:=select * from Table1


SQLTransaction1
Database:=ODBCConnection1

Datasource1
DataSet:=SQLQuery1

DBGrid1
Datasource:=Datasource1


When I set connected to True nothing happens.
When I set Query1.Active to True in ObjectInspector there is an error: "Transaction not set.

When I start the application i get a message like one in the attachment

I try a little variation of these settings  but nothing helps...

[thierrybo]

Hi,

I think this is your tsqlquery datasource property, it should be blank (see picture of what works (used a file odbc source)

[snesska]

YESSSSSSSSS!!! I works! At last! After two weeks of agony...
Thank you Zoran and  thierrybo!

You should use either SystemDNS or FileDNS, not both.
&
tsqlquery datasource property, it should be blank

is a winning combination!


 

[snesska]

Hi, it's me, again     
In run-time I edited some records shown in DBGrid.
I "post-it" with a button in navigation bar, but there are no changes in the database!
What must be done to "tell lazarus" to make changes in the *mdb file?

[thierrybo]

Read http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery#Cached_Updates

all changes are "buffered" and ou need to  manually send them to the database.

You can do this in  the "afterscroll" event of TSqlquery, data will be sent when when you change row. Or in "afterpost" event if you absolutely need to send changes as soon as you enter data in a cell.

[snesska]

Thank you!
That was very helpful!
If someone have the same problem - these two links helps me a lot to finish this:

http://wiki.lazarus.freepascal.org/Lazarus_Database_Tutorial
http://bugs.freepascal.org/view.php?id=13241

At last, syntax for that flags...

SQLQuery1.Fields.Fields[0].ProviderFlags:=[pfInkey]

[thierrybo]

interesting you also need to add the mars connection ODBC property to 'yes', I use it for MSQqlserver, but I believed it was only for this database.

[snesska]

bad news...
I try to do the same thing on 10 computers at school (i am a teacher ), but on every computer when I try to set SQLQuery1.Active to "true" i get a message "Access violation"!
Any idea?

[Wodzu]

It would be good if you could describe your problem with more detail.
Perhaps attach a test project to your post?

[snesska]

I set everything what is said on tihis forum, and I describe that on
http://www.e-pismen.com/mod/lesson/view.php?id=168&pageid=79
(Log in as a guest)
It works on my computer at home, but not work at school!

[Zoran]

I followed the images on your site and it works here... I just can't tell why it doesn't work in school...
Might be something with the permissions on the school's computers?!

[snesska]

Thank you for your time and  your answer. It gives me a hope that my exlpanation is not so bad...
But, I am admin at school, this not works neither on my nor on students computer.
One student wrote to me, that it is not working on his computer at home  too...

Maybe it is something with ODBC driver manager?

Maybe I have done something wrong while I was trying to setup ODBS driver – which is setup at your system but not on school computers – at the moment I have nothing on my mind....

[snesska]

Well, that problem iz solved -  on school's computers was older version of Lazarus, (thank's to Zoran), but now...

For updating database, I put a Button vith procedure:

procedure TForm1.Button1Click(Sender: TObject);
begin
  SQLQuery1.Fields.Fields[0].ProviderFlags:=[pfInkey];
  SQLQuery1.ApplyUpdates;
end;

Update iz working good, but insert and delete causes message:
Project raIsed exception class 'External: SIGSEGV'.

What is this?

[asdf]

Dear All,

How can I add 'file.dsn' into codes?

In http://wiki.lazarus.freepascal.org/MS_Access and  sample project from http://www.mediafire.com/file/ne1jx3zpnwzefq3/msaccesstest2.zip

There is a separate file namely 'file.dsn' but I would like to write it in codes and do not need to drag many controls(TODBCConnection, TSQLTransaction and TSQLQuery) onto form.

And another question, now I use MS Access 2010. The extention is '.accdb', not '.mdb' . How should I write codes for those ?

My codes (it does not work)...

Code: [Select]

implementation

{$R *.lfm}

{ TForm1 }

var
  cn : TODBCConnection;
  sq : TSQLQuery;
  tr : TSQLTransaction;

procedure TForm1.FormCreate(Sender: TObject);

begin

  // Set up
  cn := TODBCConnection.Create(nil);
  tr := TSQLTransaction.Create(nil);
  tr.DataBase := cn;
  tr.Action   := caCommit;

  cn.Transaction := tr;

  sq := TSQLQuery.Create(nil);
  sq.DataBase:= cn;
  sq.Transaction := tr;

  sq.UsePrimaryKeyAsKey:=false;

  cn.Driver := 'Microsoft Access Driver (*.accdb)';
  cn.Params.Add('DBQ=D:\KTH.accdb');

  cn.CloseDataSets;

  sq.SQL.Clear;
  sq.sql.text:='select FSpersonnelID from FromFingerScan';
  sq.open;

  while not sq.EOF do begin
  RadioGroup1.Items.Add(trim(uppercase(sq.FieldByName('FSpersonnelID').Text)));
  sq.Next;
  end;

  sq.close