access Accessfile from Delphi

Accessing Access from Delphi 3


 I have two Access db files called
 - ONE  with string/text fields ONEfieldA and ONEreplace
 - TWO  with string/text fields TWOreplace and TWOvalue.
 I have one string in Delphi called DEFAULT

For some stupid reason I want Delphi to read these values in two string arrays  TEST1 and TEST2.
such that for all records y in ONE:
-   TEST1[y] = value of  ONEFieldA
-   if there is NO record in TWO where the value TWOreplace is the value of ONEreplace
       TEST2[y].FIELD2 = DEFAULT
    if there is a record in TWO where the value in TWOreplace is the
    value of ONEreplace then
       TEST2[y].FIELD2 = TWOvalue (the value of the first record which match the


    ONEfieldA  ONEreplace
1   FIRST      testa
2   SECOND     testb

TWO TWOreplace TWOvalue
1   testa      FIRSTVALUE
2   testc      SECONDVALUE  



Hope this is clear enough.

BTW: If I need to install/configure some times to
be able to access Access MDB's from Delphi: please give
the needed instruction.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Well, Lets first work on asseccing access.
1) Do you have Access installed.
2) Do you want to use Native Drivers or ODBC
3) The DAO file might have got unregistered so type this in
   1)Verify that Regsvr32.exe is in your Windows\system folder
   2)Click the Start button, and then click Run
   3) In the Open Box, type the following
      Regsvr32 c:\progra~1\common~1\micros~1\dao\dao3032.dll
OK now start Delphi 3.  On the blank form drop a TDataSource, a TTable, and a TDatabase component all fond in the Data Access folder.  Double click on the TDatabase component.  A dialog window will appear.  For name type in MyTestDB and then leave the alias blank then in the Driver name choose from the list MSACCESS (all caps this is the native driver) then click on the button with default on it.  this will put some text in the window to the left.  In that Window you will notice that one of the things in the Params list is DATABASE NAME put in the path and file to your .MDB.  Link your TDataSource's DataSet property to the TTable and set your TTable's DatabaseName property to MyTestDB and then select the table you want from the TableName property's list.

To test this drop a DBGrid on the form and set it's DataSource to the datasource and then set the TTable's Active Property to true.

Ok that covers DB access now for your string array question

Use a StringList

  MyStringList : TStringList

    MyStringList := TStringList.Create;
    ShowMessage(MyStringList[0] + ', ' + MyStringList[1]);

I'm not sure of what you want to do with that code but this should get you started so you can ask another question when ready.

mosiAuthor Commented:
Adjusted points to 300
Hi mosi, let's first clear it out.

1. Which BDE version do you have?
    -> In earlier versions : Access tables can only be connected with an ODBC driver.
    -> In newer versions : Access can be accessed by a native driver.

--> I still prefer the first one, because the native driver has problems with the index keys (i.e. 4.5 still had this problem)

For installing an ODBC driver, look at the delphi support pages for how to establish it (if you can't find it, give me a sign)

For the native driver is just the same as the paradox driver. Just add an alias and get working.

2. In delphi :

   Add two tables, enter your alias. add two datasources.
   MasterSource of table2 = datasource1 (pointing to table1)
   Give as join : OneReplace=TwoReplace

3. Start your iteration :

     procedure browsetable1;
     var y: integer;
       While not table1.EOF do begin
        TEST1[y] = table1.FieldByName('ONEFieldA').asString;
        if table2.recordcount > 0 then begin
            TEST2[y] := table2.FieldByName('TWOvalue').asString;
         end else begin
                  TEST2[y] := DEFAULT

Ok, let me know what you didn't understood and let me also see the code where the fault arrises.

Regards, ZiF.

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

mosiAuthor Commented:
To ZifNab's: thanks for your answer, but
Greedy instructions look much more useful to me.

 To Greedy:
     > 1) Do you have Access installed.

     > 2) Do you want to use Native Drivers or ODBC

       Don't care, whatever works with reasonable speed, whatever gets me started.

     >    3) In the Open Box, type the following
     >       Regsvr32 c:\progra~1\common~1\micros~1\dao\dao3032.dll
       The file dao3032.dll is missing, but dao350.dll was there. I hoped this
       was another, more recent, version, so I did run:
           regsvr32 c:\progra~1\common~1\micros~1\dao\dao350.dll            
       It registred OK.    

      > OK now start Delphi 3.... DatabaseName property to MyTestDB and then select the
      > you want from the TableName property's list.

       When I selected the tablename, I got a Database Login windows,
       with a User name/password prompt and I just pressed <ENTER>.
       An error dialog pops up with the text:
         Cannot load an IDAPI service library. Alias: MyTestDB    

       So what to do next??
woops sorry about that I forgot to check that email me back thing.  Lets see...dao350.dll is the one out of access 97 so it should be fine.  

lets work on that database compoent somemore because it's the key to Native access.  When you double click the TDatabase componet you saw the dialog box, right?  Since you probably havn't created an Alias for this database with your DB administrator or your ODBC setup (nonnative found out of control panel) you will not put anything in for the Alias Name field.  All other fields must then have some values in them.  The Name field is the name of the database that your other data aware components will is not the name of the component.  The Driver field will be set to MSACCESS.  Then you will click on the Defaults button on the right side of the biggy memo like box.  If you see ODBC anywhere in any of the strings that it enters in you have selected the wrong Driver, go back and change it.  If you see something like this


your on the right track
so anyway all you really have to do is change the first line of that thing
to go to your database.  then click the OK the object inspector change the property of connected to true.  You should have a login box come up.  When you created your database you never gave it any login paramiters did you...well if you did you probably didn't delete out the default user that is Admin with a blank password...but leaving both blank usualy works too.  If you don't get anykind of error when you do this all is going to be good...if you get some freekish error with a number like -12683 then we have to look at that doa thing again, check out
if you have get that error.
I you need it I can tell you how to do an ODBC install but I don't use ODBC anymore because if a client has the newest version of ODBC installed I can't created an alias using InstallShield so I use the Native drivers now.  Pluss I need more information like do you have IE4 installed and what ODBC vesions you have.  That IDAPI service library problem has me a bit worried give me your email address in case I need to address this outside the public arena.  I have to go away next week so hopefully your around tonight.  If not I'm sure Ziffy knows how to do this too.

Acctually I know I'm going to need you email address to fix this one...Mine is if you would like to send me your sample app that is failing.  Better yet when you do finally get back to your computer I could send you the .DFM and .PAS files to a sample app that I'll have to change the path to be your database.  Acctually don't even bother building a sample app all you really need to see if you have the right files is that one TDataBase component if you can set the connected to true you'll be fine.
Well, I got to go home now...I'll be back around 11:00am tommarow
USA Mountain time...
Forget the procesing part of the q, but if you want to just read values from access, use OLE and not the BDE !
mosiAuthor Commented:
Sorry for by late response.
I can not access Access databases at all (unless I use Access ;-))).
I like to use the BDE, since Access is native is Delphi, so faster.
If you can just explain me how to use BDE with Access, you
can forget about the procesing part, I think it is fair to put that in another question.
My email is if you email you files there, I can tell
which exact errormessages I am getting..

Info: Access 97, Delphi 3.0 Standard, Win NT 4.0, p166/64Mb

Thanks, MOSI
ACCESS is so NOT native in delphi !!!!  Heard of ODBC. You're using it without knowing !
To all,

Access can be reached by ODBC... but with newer BDE versions Access is also accessable with a native driver. Only, the last time I tried to use a native driver for Access with version 4.5 of BDE they primary keys were not remembered.....

Using native driver (you should use a newer version of BDE), same as you use native drivers with paradox/dbase...

For working with ODBC drivers, look at Inprise examples how to install it :

Regards, ZiF.
mosiAuthor Commented:
To Greedy: thanks for your demo program you kindly send me by email.

Sadly it didn't run because the registration of the DAO3032.DLL failed.
This is what I did:

1- I downloaded the new BDE driver from the Inprise site and
installed them ( Went OK.

2- I ran regsvr32  \progra~1\common~1\micros~1\DAO\dao3032.dll
This gave the following error:
   LoadLibrary("\progra~1\common~1\micros~1\DAO\dao3032.dll") failed
   GetLastError returns 0.0000007e
   (I was logged on to NT as a administrator).

3- I tried to run your program anyway but of course since dao3032 didn't
   get registered, I got a:
   Project Project1.exe EDBEngineError with message 'Cannot load an IDAPI service

4- I put a brand new HD in my swapbay and installed WinNT Workstation 4.0 SP 3.
    I installed Delphi 3.0 Standard
    I did step 1,2 en 3 again: same results.

I could find useful info on this registering error.

Project Project1.exe EDBEngineError with message 'Cannot load an IDAPI service

Damn, that blows...hang on and I'll send you another version using ODBC...I'll build an install shield setup to make the ODBC for you so we can see if that'll work...sould take like 10 minutes or so so expect an email soon.


I have created a Access database with two tables ( like your
example): ONETable and TWOTable.Through the Access
Query Design I have created the following query which gives
me OUTPUT that you are looking for.

I created Delphi App with form, TQuery, Tdatasource, and dbgrid.I then paste the query, designed in Access, in to the
query and set the Active property to True and give me results that you want.(You need to create a ALIAS in the BDE pointing to the Accessdatabase and set the Database property of the TQuery.You need the latest update to the BDE - I think ver 451)

You just then loop though the query and add it to your
two-dimensional array.

Here is the Qeury:

SELECT ONETable.OneFieldA, IIf(IsNull([TwoValue]),"DEFAULT",[TwoValue]) AS Expr1
FROM ONETable LEFT JOIN TWOTable ON ONETable.OneReplace = TWOTable.TwoReplace;

Hey mosi did you get that ODBC version.  Out of curiosity (because it could have an impact) when you did that fresh install of NT -WKS 4.0 did you install Access before tring to run the program?
Go to Start | Settings | Control Panel | 32 bit ODBC then click on the About Tab and look at the Version...probably something like 3.0...the newest is 3.5 but I've found some problems with 3.5 accessing MSSQL with strange filters (I use sql links now) Install shield also has a problem with creating the ODBC DSN under ver 3.5.  However you can create your ODBC alias here by selecting (instructings for 3.5 but you'll get the idea) the User DSN tab and then click on the add button, select the driver (Microsoft Access Driver *.mdb) then click on finish give it a data source name...TestDB is good.  then click the select button under database and go find your MBD file click on OK then Click on OK again and your go into delphi and start a new app and drop a TDataSource, a TTable, and a TDBGrid on the form and set the DataSet property of DataSource1 to TTable1 set the dataSource property of the DBGrid1 to DataSource1 and set the DataBaseName property of Table1 to your ODBC Alias (TestDB) then set the TableName Property - should give you a drop down menu to select from if it didn't something has gone wrong...then set Active to true and you'll see your data in the DBGrid.

mosiAuthor Commented:
Sorry Skeleton but you mist a part of the discourse.
Greedy, I would like to grant you the points. Thanks for your efforts.
I will concentrate on trying to get it run with the BDE.  Resubmit your last comment as a answer.

Refer to comments for Answer.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mosi: email me if you have any other questions come up
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.