Problem connecting to a MS Access Database with ODBC

[anis2505]

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
                         

Hi,

I'm using MS Access 2010 with accdb extention

I'm using controls it works for me all I do is on the property fileDSN of the ODBCConnection control i provide the FULL PATH (X:\...\file.dsn)

nothing more I don't provide the driver or anything else just the full file path of the dsn file

found this too

http://wiki.lazarus.freepascal.org/MS_Access

------------------------------
Lazarus #1.0.2 on Windows 7 64 bits

[asdf]

Thank you,
but what I need is moving all codes inside 'file.dsn' and write in code of project.
Not like this...

Code: [Select]

  //connection
 ODBCConnection1.FileDSN := ExtractFilePath(Application.ExeName) + 'file.dsn';
 

[asdf]

Only AnyDAC -> http://www.da-soft.com/anydac/ can do. 


Finally after reading in Lazarus wiki, I have to create '.dsn'.

Are there any other non-commercial components equivalent to AnyDAC ?

[BigChimp]

I'm a bit unclear about what you did. Did you try entering the ODBC details to create a DSN-less connection?
http://wiki.lazarus.freepascal.org/ODBCConn#Without_a_DSN

Did you get error messages then?

[asdf]

ํYes, what I only need is dsn-less.

I tried

Code: [Select]

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

But error.

[BigChimp]

Ok. 2 ideas (haven't used accdb myself though)
1. Make sure the .accdb driver supports the DBQ=bla notation for specifying the database
2. If you haven't already: make sure you have a 64 bit accdb ODBC driver if compiling a 64 bit program and a 32 bit ODBC driver if compiling a 32 bit program.

[BigChimp]

Also, make sure the driver name you use in cn.Driver is correct. I could only find this example:
http://www.connectionstrings.com/access-2007#microsoft-access-accdb-odbc-driver

[anis2505]

Hi,

I used the code below and it works fine

Code: [Select]

cn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';
  cn.Params.Add('DBQ='+ExtractFilePath(Application.ExeName) + 'yourdb.accdb');
  cn.Connected:=True;
  cn.KeepConnection:=True;
  SQLQuery1.SQL.Text:='SELECT * FROM table1';
  SQLQuery1.Open;     


[asdf]

Today I tried DSN-less again, but it worked. I am so  now.

Thank you for all kind suggestions from all of you.

Maybe I placed codes in wrong lines or wrong sequence, or misspelled the data field name or table name.

 

[Goodman H__]

Hi,

I used the code below and it works fine

Code: [Select]

cn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';
  cn.Params.Add('DBQ='+ExtractFilePath(Application.ExeName) + 'yourdb.accdb');
  cn.Connected:=True;
  cn.KeepConnection:=True;
  SQLQuery1.SQL.Text:='SELECT * FROM table1';
  SQLQuery1.Open;     


Sorry for my stepping in but I've encountered the same issue.
Are you all talking about the dsn-less connection via ODBC with Access database (*.mdb)?With the above connection code,it still failed to connect to an access database if dsn is not defined in ODBC source panel a 'database =' property is not provided in IDE.

All I want is just to connect an access database (*.mdb) with sqlDB component(TODBCconnection,QSqlTransaction,TSqlQuery...),the most important point is there is NO need to set up a dsn in the ODBC Source panel!Something like in C# below code is sufficient to connect to an access *.mdb database:

Code: [Select]

OleDbConnection nwindConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;")

Solution would be appreciated.

Regards,
Sam

[anis2505]

Hi,

I used the code below and it works fine

Code: [Select]

cn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';
  cn.Params.Add('DBQ='+ExtractFilePath(Application.ExeName) + 'yourdb.accdb');
  cn.Connected:=True;
  cn.KeepConnection:=True;
  SQLQuery1.SQL.Text:='SELECT * FROM table1';
  SQLQuery1.Open;     


Sorry for my stepping in but I've encountered the same issue.
Are you all talking about the dsn-less connection via ODBC with Access database (*.mdb)?With the above connection code,it still failed to connect to an access database if dsn is not defined in ODBC source panel a 'database =' property is not provided in IDE.

All I want is just to connect an access database (*.mdb) with sqlDB component(TODBCconnection,QSqlTransaction,TSqlQuery...),the most important point is there is NO need to set up a dsn in the ODBC Source panel!Something like in C# below code is sufficient to connect to an access *.mdb database:

Code: [Select]

OleDbConnection nwindConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;")

Solution would be appreciated.

Regards,
Sam

Hi,

The C# code means this
For the database driver

Code: [Select]

Provider=Microsoft.Jet.OLEDB.4.0;
Your database

Code: [Select]

Data Source=C:\mydatabase.mdb;


It's the same here

you set the driver like this:

Code: [Select]

cn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';

The database like this

Code: [Select]

cn.Params.Add('DBQ=C:\mydatabase.mdb');

I hope that will help

[anis2505]

Today I tried DSN-less again, but it worked. I am so  now.

Thank you for all kind suggestions from all of you.

Maybe I placed codes in wrong lines or wrong sequence, or misspelled the data field name or table name.

 

Hi,
Could you provide the code you used.

Thanks in advance.

[BigChimp]

Note: I'm sure people would appreciate an update of
http://wiki.lazarus.freepascal.org/ODBCConn#Connecting_to_MS_Access
... and/or a rewrite of
http://wiki.lazarus.freepascal.org/MS_Access
... which seems a bit well... one sided at least (focus on File-based DSN connection)

[anis2505]

Note: I'm sure people would appreciate an update of
http://wiki.lazarus.freepascal.org/ODBCConn#Connecting_to_MS_Access
... and/or a rewrite of
http://wiki.lazarus.freepascal.org/MS_Access
... which seems a bit well... one sided at least (focus on File-based DSN connection)

GREAT THANKS

[asdf]

in MS Access database, there is a field and its data type is 'Date/time'.

Code: [Select]

  sq.SQL.Text := 'select FSDT from TimeTable order by FSDT'; // FSDT is a field of Date/Time data type
  sq.Open;
  stringgrid2.RowCount:=sq.RecordCount+1;

    while not sq.EOF do
      begin
        stringgrid2.Cells[1,sq.RecNo]:=sq.FieldByName('FSDT').Text;
        sq.Next;
      end;
  sq.Free;
  cn.Free;

It crashed, and could you please teach me how to design the database upon Date/Time type ?
And what is the correct sql statement for Date/Time type field ?