Solved

how to communicate with local MS-access database

Posted on 2008-10-16
16
1,932 Views
Last Modified: 2013-11-17
hi
i'm using Borland c++ bulider. I would like to use SQL to query data from my local MS access database. would you give me a example code? Thank.
0
Comment
Question by:didan11
  • 11
  • 5
16 Comments
 
LVL 3

Expert Comment

by:BTecho
ID: 22734541
Hello,

For MS-Access I would recommend you can use the TADOTable/TADOQuery(for SQL queries) components, they're just as easy to use as the BDE Table components with a few more steps involved.

Article snippet from the C++Builder 6 Developer Guide
http://books.google.com/books?id=Lhxw_6sQBZcC&pg=PA443&dq=accessing+a+dataset+with+tadoquery

Here's a Delphi tutorial
http://delphi.about.com/od/database/l/aa050101a.htm

Converted the first code snippet into C++

For Delphi
with ADOQuery1 do begin
  Close;
  SQL.Clear;
  SQL.Add:='SELECT * FROM Authors '
  SQL.Add:='ORDER BY authorname DESC'
  Open;
end;

For C++Builder
  ADOQuery1->Close();
  ADOQuery1->SQL->Clear;
  ADOQuery1->SQL->Add="SELECT * FROM Authors";
  ADOQuery1->SQL->Add="ORDER BY authorname DESC";
  ADOQuery1->Open();

To convert from Delphi VCL code to C++Builder...  
All the dots .  become ->
:=    become  =
'      become   "
Delphi methods don't end in an () so you have to add those.



0
 

Author Comment

by:didan11
ID: 22735221
Hi BTecho,
How does the program know where to find database, what kind of database is, what's the password etc.?  Thanks
0
 
LVL 3

Expert Comment

by:BTecho
ID: 22735613
Hi Didan,

You can set the connection via the TADOConnection component. The TADOQuery component links to it.
I posted a link to the delphi.about.com site in my first reply or you can look up "Connecting to a data store using TADOConnection" in C++Builder's help file, it explains in more detail.

The TADOConnection component has a ConnectionString property which you need to setup. You can also setup the TADOQuery component seperately. Click the ... button for the property, you can enter the string manually or click the Build button. From there you should be able set database and username/password info. Then after that's setup please set the Connected property to true, if you get no messages, you're set.

p.s. Don't forget to set LoginPrompt to false.
0
 
LVL 3

Expert Comment

by:BTecho
ID: 22735671
I decided to search for TADO alternatives.

I haven't tried this one but it looks promising. It's free , includes source and support C++Builde6
http://www.opus.ch/OpenSource/DirectAccess/Default.htm
0
 

Author Comment

by:didan11
ID: 22735772
BTecho,
it complains workgroup information file is missing and error in initializing provider. i have no idea which provider should i pick and what  is workgroup file.  Could you please tell me? thanks
0
 
LVL 3

Expert Comment

by:BTecho
ID: 22741838
Hi :)

The provider should be the Jet OLEDB or Microsoft.Jet.OLEDB.4.0

Jet engine should already be installed with XP. Perhaps try a re-install of the latest version of JET and MDAC
http://www.zuggsoft.com/data/#Windows%20XP%20Installation
0
 
LVL 3

Expert Comment

by:BTecho
ID: 22741894
"Microsoft JET 4.0 OLE DB Provider" should be in the list.
0
 
LVL 3

Expert Comment

by:BTecho
ID: 22742098
btw, for the workgroup file. It is needed if you're working with passwords. If I remember correctly, you set the password property in the dialog setup and it shouldn't ask for it. If it still does, do you find a mdw file in the folder with the mdb file?

Can please post the full connectionstring that was generated?
0
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.

 

Author Comment

by:didan11
ID: 22743962
Hi BTecho,
My access database was created long time ago. it has been updated to current version, Access 2000 format. However, my Access is 2003. from menu "Tools->Sercurity", I found these information:
on "Workgroup Administrator" window:
Name: didan
Company: my company
Workgroup: C:\Unisim4.0\unisim.mdw

on "User and Group Permissions" window:
User/Group Name: Admin

on "User and Group Accounts" window:
User Name: Admin
Group Membership Available Groups: Admins Users
Groups Admins

i created file C:\Unisim4.0\unisim.mdw. other things are default

here is the full connectionstring that was generated. I still got the same error
Provider=Microsoft.Jet.OLEDB.4.0;Password=sunny2;Data Source=C:\Unisim4.0\unisim.mdb;Mode=Read;Persist Security Info=True

it there anything wrong?
Thanks in advance


0
 
LVL 3

Expert Comment

by:BTecho
ID: 22745574
Howdy,

Please replace this part of the string

Password=sunny2;

with

Jet OLEDB:Database Password=sunny2;


If that still doesn't work, instead, please add this line to the string. If you setup the login/pass info in the mdw...
Jet OLEDB:System database="C:\Unisim4.0\unisim.mdw";

I'm not on a PC with Delphi or BCB at the moment. So I'm not sure if the User ID needs to be included, but the default is
User ID=Admin;

Good luck!
0
 
LVL 3

Expert Comment

by:BTecho
ID: 22758666
Hi didan,

Just wondering if you found the solution to your problem?

Thanks
0
 

Author Comment

by:didan11
ID: 22760135
Thanks.  BTecho,
I got "connection success" but i don't know why  i got error message "couldn't find file c:\unisim4.0\unisim4.0unisim.mdb"
why the file name "unisim.mdb" prefixed part of the path folder name "unisim4.0"?

here is my code
String str="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Unisim4.0\unisim.mdb;Mode=Read;Persist Security Info=False;Jet OLEDB:Database Password=sunny2";
String sql="SELECT name FROM BearingTable";        

ADODataSet1->CommandText = sql;
ADODataSet1->ConnectionString=str;
ADOCommand1->ConnectionString=str;
ADODataSet1->Recordset=ADOCommand1->Execute();
0
 
LVL 3

Expert Comment

by:BTecho
ID: 22768073
Hi :)

Please try doubling up those backslashes otherwise builder thinks it's an escape character. Anytime you want to include a "\" in a string it always needs to be doubled. That should do the trick.

String str="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Unisim4.0\\unisim.mdb;Mode=Read;Persist Security Info=False;Jet OLEDB:Database Password=sunny2";

0
 

Author Comment

by:didan11
ID: 22781482
Thanks BTecho,
Now I go no error for the code.  I was a VB programmer.  it is so easy to read a field value from recordset in VB.  however, i'm confused how to read a field from ADODataSet1->Recordset. i'm wondering if there is any simple way?
0
 
LVL 3

Expert Comment

by:BTecho
ID: 22798306
Howdy,

Please try

ADODataSet1->RecordSet->Fields("field_name")->Value; //or AsString, AsInteger, etc.

Instead of the TADODataSet you can try the TADOTable. You might find it easier to use for many of the other things you might need to do with the data.

Then it's also simple as

AnsiString FieldStr;
FieldStr=ADOTable1->FieldByName("field_name")->AsString;  //or AsInteger, AsDateTime, etc.
0
 
LVL 3

Accepted Solution

by:
BTecho earned 250 total points
ID: 22798347
Actually , if you're already working and comfortable with using the TADODataSet might as well use it. I ended up using the ADOTable though it has not been limiting in my case. If you're just starting out to program an app , leave it in. FieldByName should also work with the TADODataSet.

I would suggest looking up info on the CommandType property of the ADODataset in C++Builder help. I read that by setting that option yourself it might increase overall performance.
0

Featured Post

Highfive Gives IT Their Time Back

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!

Join & Write a Comment

In our object-oriented world the class is a minimal unit, a brick for constructing our applications. It is an abstraction and we know well how to use it. In well-designed software we are not usually interested in knowing how objects look in memory. …
Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

760 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