Solved

MS ACCESS database

Posted on 2001-06-29
21
131 Views
Last Modified: 2010-04-06
1 - How can i add new records and information on MS ACCESS database. I connect with TDatabase and TTable. I also know SQL language.

2 - How can i change info on records, search records and delete records?

Thanks
0
Comment
Question by:systemop
  • 10
  • 7
  • 4
21 Comments
 
LVL 4

Expert Comment

by:jsweby
ID: 6238198
So esentially, how can you interact with the database?

You can use the Edit, Insert, Delete, Cancel and Post methods of the TTable component to modify, add, delete, cancel and save data to a table.

Or you can use a TQuery component and use SQL in its SQL property.

J.
0
 
LVL 4

Expert Comment

by:YodaMage
ID: 6238249
As stated above you'll either use SQL with the TQuery component, or use TTables methods. You'll add data to the table either using fieldbyname, or TFields. If your database if fairly static and is going to remain so (structure, indexing, field names, etc) then you'll probably want to use the Fields Editor and add data with TFields. If you are designing and changing on the fly, I'd stick with FieldbyName for now.

Simple Example:

Table1.Append;
Table1.FieldbyName('Field1').AsString := TEdit1.text;
Table1.Post;

0
 
LVL 4

Expert Comment

by:jsweby
ID: 6238260
Better still, use the data aware components in the Data Controls tab to display the fields in individual edit boxes, check boxes, etc. Just call Edit, let the user make their changes and then call Post. All the changes are saved.

J.
0
 

Author Comment

by:systemop
ID: 6238347
So i can do everything to my ACCESS database like dBase database example :

Table1.Append;
Table1NAME := Edit1.Text;
Table1.Update;
0
 
LVL 4

Expert Comment

by:jsweby
ID: 6238359
Well, more like:

Table1.Append;
Table1.FieldByName('NAME').AsString := Edit1.Text;
Table1.Post;

Look in the Delphi help for the TTable methods.

J.


0
 

Author Comment

by:systemop
ID: 6238384
Ok. How about deleting and changing records?
0
 
LVL 4

Expert Comment

by:jsweby
ID: 6238395
Delete:

Table1.Delete; (Deletes current record)

Changing:

Table1.Edit;
Table1.FieldByName('NAME').AsString := Edit1.Text;
Table1.Post;

J.
0
 

Author Comment

by:systemop
ID: 6238411
One Last Thing to learn for me. How about searching a record by field and keyword? Example NAME field and 'systemop' for key??
0
 
LVL 4

Accepted Solution

by:
jsweby earned 100 total points
ID: 6238462
OK, you need to set an index in your table on the field you want to search by, in your case, Name.

Delphi:

Table1.IndexName := 'Name'; {Or whatever you called the index in MS ACCESS}
If Table1.FindKey(['systemop']) then ShowMessage('Found record!')
Else
ShowMessage('Could not find record.');

J.
0
 

Author Comment

by:systemop
ID: 6238471
Thank for everything see you later
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 4

Expert Comment

by:YodaMage
ID: 6238480
Depends on rather it is an indexed field or not.

Indexed Field:

Table1.FindKey([systemop});

or

Table1.SetRange([systmop], [systemop]);
This will give you a matching set of values, while findkey will return only the first found value.

If not indexed:

Table1.Filter := 'Name = '''systemop'''';
Table1.Filtered := True;

You use:

Table1.CancelRange;
-or-
Table1.Filtered := False;

afterward to get your entire dataset back.

*Also, Delete requires no post and no change in dataset state before the method is called.

**After a delete it is good practice to manually be sure of where your pointer went. It usually is expected to move one record up, but make sure.

*** Beware of data aware components. They are very good for many tasks, but there are downfalls. Example: If you allow data entry and modification in a data aware grid. If user changes row, an implied 'POST' is called without you or using doing anything. This is a big problem if you are running any functions to verify data values.
0
 
LVL 4

Expert Comment

by:YodaMage
ID: 6238491
jsweby : Often it is better to use a filter than add multiple indexes all over the place that will contibute to table corruption.
0
 
LVL 4

Expert Comment

by:jsweby
ID: 6238495
I disagree, the filter is slow and outdated, indexes are a fantastic way to speed up searching through a database and if you are using SQL, they will dramatically increase execution speed as well.

J.
0
 
LVL 4

Expert Comment

by:YodaMage
ID: 6238504
A table with 8 indexes WILL corrupt and often if hit hard and often. That is fact.
0
 
LVL 4

Expert Comment

by:jsweby
ID: 6238513
I have no idea where you're getting these facts from, this has never happened to me and I often have multiple indexes on a table. What database are you using? We have a table here that has 15 indexes on it. Without it, it would take hours to search for information.

J.
0
 
LVL 4

Expert Comment

by:YodaMage
ID: 6238522
A table with 8 indexes WILL corrupt and often if hit hard and often. That is fact.
0
 
LVL 4

Expert Comment

by:jsweby
ID: 6238541
Whatever you think of indexes, the Filter is a terrible option to use if you can use another method. If it is all you have and you are using small tables, fine. If you have a medium-to-large amount of data, forget it.

This is where you'd use SQL anyway.

J.
0
 
LVL 4

Expert Comment

by:YodaMage
ID: 6238648
Exactly my point though. If you are using small data sets, Indexes are fine, and Filters don't really hurt you.

Large data sets, you'd go to a true SQL based data structure where all this becomes moot.

If you need to more than 3 indexes on any table, you must determine rather you will either deal with the short comings of a filter, or move to SQL. Using TTables and Access with tables 5 indexes deep is not viable, that is my point.
0
 
LVL 4

Expert Comment

by:jsweby
ID: 6238660
I take your points on board, and the idea of moving to SQL once you hit a certain level of data and/or database structure is a given. However, we have been using Access and multiple indexes for a couple of years now with no problems, using standard Delphi Edit/Post, etc.

Ironically, we're now using SQL and migrating to SQL Server, which kind of wraps it up!

J.
0
 
LVL 4

Expert Comment

by:YodaMage
ID: 6238681
I've rewritten/patched the heck out of several applications that had large amounts of data (500,000 records per table) in Paradox tables with up to 14 indexes on them.

I had to rebuild tables at least once a week, and still found occasional "index out of date" errors leaking through. I combined many ill conceived indexes, eliminated others, and finally went to a technique of using setrange to limit data set, then applying a filter to that result. Yes it is slower, but no I haven't seen the data corruption that appeared before.
0
 
LVL 4

Expert Comment

by:jsweby
ID: 6238757
Ah, Paradox - now you're talking the height of corruption!

J.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

743 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

13 Experts available now in Live!

Get 1:1 Help Now