MS ACCESS database

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.

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;

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.

So i can do everything to my ACCESS database like dBase database example :

Table1.Append;
Table1NAME := Edit1.Text;
Table1.Update;

Well, more like:

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

Look in the Delphi help for the TTable methods.

J.

Ok. How about deleting and changing records?

Delete:

Table1.Delete; (Deletes current record)

Changing:

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

J.

One Last Thing to learn for me. How about searching a record by field and keyword? Example NAME field and 'systemop' for key??

Thank for everything see you later

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.

jsweby : Often it is better to use a filter than add multiple indexes all over the place that will contibute to table corruption.

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.

A table with 8 indexes WILL corrupt and often if hit hard and often. That is fact.

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.

A table with 8 indexes WILL corrupt and often if hit hard and often. That is fact.

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.

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.

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.

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.

Ah, Paradox - now you're talking the height of corruption!

J.