Solved

Connect to an Access DB

Posted on 2002-04-11
10
270 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

726 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