how to communicate with local MS-access database

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.
didan11Asked:
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.

BTechoCommented:
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
didan11Author Commented:
Hi BTecho,
How does the program know where to find database, what kind of database is, what's the password etc.?  Thanks
0
BTechoCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

BTechoCommented:
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
didan11Author Commented:
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
BTechoCommented:
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
BTechoCommented:
"Microsoft JET 4.0 OLE DB Provider" should be in the list.
0
BTechoCommented:
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
didan11Author Commented:
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
BTechoCommented:
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
BTechoCommented:
Hi didan,

Just wondering if you found the solution to your problem?

Thanks
0
didan11Author Commented:
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
BTechoCommented:
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
didan11Author Commented:
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
BTechoCommented:
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
BTechoCommented:
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

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

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.