Solved

Connect to an Access DB

Posted on 2002-04-11
10
266 Views
Last Modified: 2010-04-04

hi,
How can I connect to an Access DB ?  I tried to create an alias an connect the TDatabase to it, but it do not work.
And I do not want the application prompt for a login because I do not have any password set for the DB.
Thanks
0
Comment
Question by:syloux
10 Comments
 

Expert Comment

by:ewingengr
ID: 6934140
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.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6934186
thats not a public answer !
0
 

Author Comment

by:syloux
ID: 6934588
Delphi 6
0
 
LVL 10

Accepted Solution

by:
Jacco earned 50 total points
ID: 6934839
It all depends on your Access db version.

With version 95 and 97 you can use the BDE. Type of alias is MSACCESS. It might also work with a ODBC alias.

For Access 2000 you can use ADO. Be careful though what version of MDAC you have installed and be sure to update you Delphi ADOExpress sources (although I dont know if that is necessary for D6).

A native way to connect to Access database ios through DAO. There are sets of components for that. The KADAO:

http://www.delphi.pari.bg/

I had to make the decision on how to connect to MS Access 2000 databases today as well and decided for the KADao components.

Regards Jacco

P.S: If it only the password dialog you are concerned about you can double click the TDataBase component and use the ComponentEditor that pops up. Click defaults first, then delete all lines from the memo except for the two that state username and password. Set the values to the correct ones (if none then skip this step). The set the property LongPrompt of the TDataBase component to false. No login screen will be displayed then.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6934854
thats a public answer form jacco,
preferable posted as comment ;-)
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 14

Expert Comment

by:DragonSlayer
ID: 6935753
--> 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.
0
 
LVL 2

Expert Comment

by:header
ID: 6935876
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.
0
 
LVL 3

Expert Comment

by:smurff
ID: 6936858
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.
0
 

Expert Comment

by:jarbou
ID: 6939020
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
0
 
LVL 1

Expert Comment

by:DelRunner
ID: 6940644
ADO can also be used for Access 97 !!

DR
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now