Solved

MS ACCESS database

Posted on 2001-06-29
21
134 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Print Graphic and Text to Epson TM-T88v 12 373
creating threads in delphi 1 131
how can i search if string exist in array ? 3 63
Convert MS Word document to a PDF file 9 90
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

840 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