Solved

access Accessfile from Delphi

Posted on 1998-05-19
18
323 Views
Last Modified: 2010-04-06
Accessing Access from Delphi 3

Suppose:

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

Example:
Input
DEFAULT=default

ONE  
    ONEfieldA  ONEreplace
1   FIRST      testa
2   SECOND     testb

TWO TWOreplace TWOvalue
1   testa      FIRSTVALUE
2   testc      SECONDVALUE  

Output

 TEST1[1] = FIRST
 TEST2[1] = FIRSTVALUE
 TEST2[1] = SECOND
 TEST2[2] = DEFAULT

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.

Thanks,
 Mosi
0
Comment
Question by:mosi
  • 8
  • 5
  • 2
  • +2
18 Comments
 
LVL 1

Expert Comment

by:Greedy
ID: 1346262
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

var
  MyStringList : TStringList

begin
  try
    MyStringList := TStringList.Create;
    MyStringList.Add(Table1.FieldByName('<YourField>').AsString);
    MyStringList.Add(....)
  finally
    ShowMessage(MyStringList[0] + ', ' + MyStringList[1]);
    MyStringList.Free;
    ...
  end;
end;

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.


0
 
LVL 1

Author Comment

by:mosi
ID: 1346263
Adjusted points to 300
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1346264
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;
     begin
       y:=0;
       table1.First;
       While not table1.EOF do begin
        inc(y);
        TEST1[y] = table1.FieldByName('ONEFieldA').asString;
        if table2.recordcount > 0 then begin
            TEST2[y] := table2.FieldByName('TWOvalue').asString;
         end else begin
                  TEST2[y] := DEFAULT
         end;
        table1.Next;
       end;
     end;
 

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

Regards, ZiF.

0
 
LVL 1

Author Comment

by:mosi
ID: 1346265
To ZifNab's: thanks for your answer, but
Greedy instructions look much more useful to me.

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

     > 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
        table
      > 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??
       
0
 
LVL 1

Expert Comment

by:Greedy
ID: 1346266
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 see...it 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

DATABASE NAME=DRIVE:/PATH/DATABASE.MDB
USER NAME=
OPEN MODE=READ/WRITE
LANGDRIVER=
SYSTEM DATABASE=
PASSWORD=

your on the right track
so anyway all you really have to do is change the first line of that thing
DATABASE NAME=DRIVE:/PATH/DATABASE.MDB
to go to your database.  then click the OK button...in 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
http://support.microsoft.com/support/kb/articles/Q141/2/94.asp
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.

0
 
LVL 1

Expert Comment

by:Greedy
ID: 1346267
Acctually I know I'm going to need you email address to fix this one...Mine is flicky@frottage.com 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 built...you'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.
0
 
LVL 1

Expert Comment

by:Greedy
ID: 1346268
Well, I got to go home now...I'll be back around 11:00am tommarow
USA Mountain time...
0
 
LVL 1

Expert Comment

by:ssite
ID: 1346269
Forget the procesing part of the q, but if you want to just read values from access, use OLE and not the BDE !
0
 
LVL 1

Author Comment

by:mosi
ID: 1346270
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 mosi@euronet.nl 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
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Expert Comment

by:ssite
ID: 1346271
ACCESS is so NOT native in delphi !!!!  Heard of ODBC. You're using it without knowing !
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1346272
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 :

http://www.inprise.com/devsupport/delphi/ti_list/TI2781.html

Regards, ZiF.
0
 
LVL 1

Author Comment

by:mosi
ID: 1346273
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 (bde451_en.zip). 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
   library.

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
library



0
 
LVL 1

Expert Comment

by:Greedy
ID: 1346274
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.

0
 

Expert Comment

by:skeleton
ID: 1346275

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;

0
 
LVL 1

Expert Comment

by:Greedy
ID: 1346276
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 done...now 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.

0
 
LVL 1

Author Comment

by:mosi
ID: 1346277
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.

0
 
LVL 1

Accepted Solution

by:
Greedy earned 300 total points
ID: 1346278
Refer to comments for Answer.







0
 
LVL 1

Expert Comment

by:Greedy
ID: 1346279
Mosi: email me if you have any other questions come up
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
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…
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

705 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

18 Experts available now in Live!

Get 1:1 Help Now