Solved

MS ACCESS database

Posted on 2001-06-29
21
132 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 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

12 Experts available now in Live!

Get 1:1 Help Now