Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS ACCESS database

Posted on 2001-06-29
21
Medium Priority
?
144 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 400 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
 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

718 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