Connect to an Access DB

I do not know what version of delphi you are using but give me your email address I have a help file I found that explains everything for delphi 1,2, and 3.  I will zip it and email.

thats not a public answer !

Delphi 6

thats a public answer form jacco,
preferable posted as comment ;-)

--> The set the property LongPrompt of the TDataBase component to false. No login screen
will be displayed then.

I think Jacco was trying to say "Then, set the LoginPrompt property of the TDatabase component to false"

Cheers,
DragonSlayer.

Jacco, does the KADAO components appear by default, where do I get to them?

I have used the ADO components that come with Delphi 6 and have found them to work very good.  The only problem I have had is it will not sort properly sometime. (The query will not accept the Order By clause)  This however can be taken care of with proper indexes.  So it's not so much a limitation, but a different way of doing things.

syloux, to answer your specific question of how to connect to an Access database:
- Go to the ADO tab of your components bar
- Drop an ADO Connection (First Icon) onto your form
- Double-click the ADO Connection or
  Go to the Connection String property and click the '...'
- Choose 'Use Connection String' and click Build
- Select 'Microsoft Jet 4.0 OLE DB Provider' and click Next
- Browse or enter the path to your database
- If you have to enter a user name, fill in the appropriate information
- Note: A Database password is different, if you have a database password, go to the 'All' tab and double-click 'Jet OLEDB: Database Password' and fill in your password
- To make sure everything is working, go to the 'Connection' tab and click 'Test Connection'
- Click 'OK' to accept everything
- Go to the properties of the ADO Connection
- Change the 'Login Prompt' property to false
- Change the 'Connected' property to true

Your now connected to the database.  You can now connect the other ADO components (Table, Query, Dataset, etc.) to the ADO Connection.

syloux,

Here is the pas file I used when I was teaching myself how to use ADO to connect to a mdb. Theres a couple of functions in there that Ive got from others, cant remember who, but it all works.

Enjoy
Smurff.

Unit uMain;

Interface

Uses
 Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
 Dialogs, ADODB, StdCtrls;

Type
 TfrmMain = Class(TForm)
  Button1: TButton;
    ComboBox1: TComboBox;
    ComboBox2: TComboBox;
  Procedure Button1Click(Sender: TObject);
  Procedure LoadAndConnectToMSAccessDB(zDBName, zDBPassword: String);
  Function GetDBPath(zDBName: String): String;
  Function CheckIfAccessDB(zDBPathName: String): Boolean;
  Function ConnectUsingADOConnect(zDBPathAndName, zDBPAssword: String): Boolean;
 Private
  { Private declarations }
 Public
  { Public declarations }
 End;
 
Var
 frmMain: TfrmMain;
 Global_DBConnection_String: String;
Const
 ERRORMESSAGE_1 = 'No Database Selected';
 ERRORMESSAGE_2 = 'Invalid Access Database';
 
Implementation

{$R *.dfm}

Procedure TfrmMain.Button1Click(Sender: TObject);
Begin
 LoadAndConnectToMSAccessDB('BMS.MDB', '');
End;

Function TfrmMain.CheckIfAccessDB(zDBPathName: String): Boolean;
Var
 UnTypedFile: File Of Byte;
 Buffer: Array[0..19] Of Byte;
 NumRecsRead: Integer;
 i: Integer;
 MyString: String;
Begin { should put a try expect here ! TODO:}
 AssignFile(UnTypedFile, zDBPathName);
 reset(UnTypedFile);
 BlockRead(UnTypedFile, Buffer, 19, NumRecsRead);
 CloseFile(UnTypedFile);
 For i := 1 To 19 Do
  MyString := MyString + Trim(Chr(Ord(Buffer[i])));
 Result := False;
 If Mystring = 'StandardJetDB' Then
  Result := True;
 If Result = False Then
  MessageDlg(ERRORMESSAGE_2, mtError, [mbOK], 0);
End;

Function TfrmMain.ConnectUsingADOConnect(zDBPathAndName,
 zDBPAssword: String): Boolean;
Var
 ADOC: TADOConnection;
 ADOQ: TADOQuery;
Begin
 Global_DBConnection_String :=
  'Provider=Microsoft.Jet.OLEDB.4.0;' +
  'Data Source=' + zDBPathAndName + ';' +
  'Persist Security Info=False;' +
  'Jet OLEDB:Database Password=' + zDBPassword;
 Try
  ADOC := TADOConnection.Create(Self);
  ADOC.ConnectionString := Global_DBConnection_String;
  Try
   ADOC.Open;
   { Just Playing  :) }
   ADOC.GetTableNames(ComboBox1.Items);
   ADOC.GetFieldNames('Dealers',ComboBox2.Items);
  Except
   On E: Exception Do
   Begin
    MessageDlg('Error : ' + E.Message, mtError, [mbOK], 0);
    Exit;
   End;
  End;
  { ######################################################################### }
  { now you have a recordset and can work with the data}

  ADOQ := TADOQuery.Create(Self);
  ADOQ.Connection := ADOC;
  // u can also use ADOQ.ConnctionString and not use ADOC
  ADOQ.SQL.Add('SELECT * FROM Dealers WHERE Title = ' + QuotedStr('Mr'));
  Try
   ADOQ.Open; { or ExecSql if your using DDL Data Definition Lang }
  Except
   On E: Exception Do
   Begin
    MessageDlg('Error : ' + E.Message, mtError, [mbOK], 0);
    Exit;
   End;
  End;
  frmMain.Caption := IntToStr(ADOQ.RecordCount); {total row count from query}

  { ####################################################################### }
  While Not ADOQ.Eof Do
  Begin
   //if ADOQ.Fields.FieldByName('SomeFieldNameInTable1').AsString ='SomeValue' then
   If ADOQ.Fields.FieldByName('Title').AsString = 'Mr' Then { ADOQ.RecNo }
   Begin
    // Edit the data
    ADOQ.Edit;
    ADOQ.Fields.FieldByName('Title').AsString := 'Miss';
    ADOQ.Post;
   End;
   ADOQ.Next;
  End;
  MessageDlg('Done', mtConfirmation, [mbOK], 0);

  { Finished and ready to clean up }
  { ######################################################################### }
 Finally
  If Assigned(ADOQ) Then
   ADOQ.Free;
  If Assigned(ADOC) Then
   ADOC.Free;
 End;
End;

Function TfrmMain.GetDBPath(zDBName: String): String;
Var
 lOpenDialog: TOpenDialog;
Begin
 lOpenDialog := TOpenDialog.Create(Nil);
 If FileExists(ExtractFileDir(Application.ExeName) + '\' + zDBName) Then
  Result := ExtractFileDir(Application.ExeName) + '\' + zDBName
 Else
 Begin
  lOpenDialog.Filter := 'MS Access DB|' + zDBName;
  If lOpenDialog.Execute Then
   Result := lOpenDialog.FileName;
 End;
 If Assigned(lOpenDialog) Then
  lOpenDialog.Free;
End;

Procedure TfrmMain.LoadAndConnectToMSAccessDB(zDBName,
 zDBPassword: String);
Var
 zDBpathName: String;
Begin
 zDBpathName := GetDBPath(zDBName);
 If (Trim(zDBPathName) <> '') Then
 Begin
  If CheckIfAccessDB(zDBPathName) Then
   ConnectUsingADOConnect(zDBPathName, zDBPassword);
 End
 Else
  MessageDlg(ERRORMESSAGE_1, mtError, [mbOK], 0);
End;

End.

with TDatabase, you have to do the following

1. create an ODBC driver or bde for the access db. once you create it, it will appear in the AliasName's property.

2. select the odbc driver.

3. Give  a name for db in the DatabaseName property. say: yyy

4. Let the LoginPrompt property be false.

5. when you use a Query or a Table, select yyy from AliasName's property.

That will solve your problem.

Regards

ADO can also be used for Access 97 !!

DR