Advice on database / UI structure and best approach please 1

[LEICJDN1]

Hi,

I have written a few programs in Delphi now but never used database applications before. Could I explain the concept behind what I want to achieve, and ask for advice on the best approach to take / techniques to use, plus any usueful references before I go off and have a stab.

Basically I have a large quantity of references and useful documents on my HD, most of which I have forgotten about! What I would like to develop is a simple program allowing me to organise these. A local database will contain data including:

Subject - broad categories
Keyword - Specialised areas within subject
Comment on reference - 'Memory jogger' text comment
Location of reference - Where it is on my HD, plus file type

The program will require me to login, then present me with a form where I can either:

1) Add data to the local database (add a new reference, perhaps by browsing to it then entering subject, keyword and comment)

2) Search for either subject, keyword or text within the comment field

3) Display result of search and display chosen reference (most are PDFs)

And perhaps also display all keywords within a subject, or an A to Z list of subjects and keywords.

So, I need a simple front end login, then a form to lead on to either data entry, search and display, or A to Z listings. Choosing an option would lead to a new form, but I can drop back to main menu at any time.

I plan to just implement this locally with a DB on my HD.

Questions:

1) Should I use Access to construct the DB and link to this from Delphi?

2) What is the best DB structure to use in Delphi for this plan - ADO, DB Express etc.

3) How can I write a quick and efficient search routine?

Is it possible to produce a system like MS help where as you type a term a list updates according to the letters you type?

Thanks.

PS - Have also written ASP DB applications so have some experience of battling with Access!

 

[buho]

1)
For a personal dbase, Access/Jet is a good choice.

You can work with the dbases from Delphi, but for simple works, relying mostly in forms, reports and queries and not needing special programming I prefer Access itself and not Delphi.

And in some cases where I need some special programming I still use Access with the "complex" code (heavy math or string processing, communication with hardware, etc) in Delphi DLLs or ActiveX components.

IMHO Delphi can't beat Access producing reports and queries. Entry data forms are a little easy to make in Access too (depending which data controls set you are using, the one coming with Delphi is somewhat limited). But Access is infuriating for anything but the simplest programing work.

2)
If you decide to use Access/Jet dbases from Delphi, ADO is the best way to do so.

3)
Searching your dbase data to get the hits? Use SQL; let Jet do the work for you.

Side note: some quick reading about normal forms (NFs) and relational dbase design is advisable before starting. For simple works 2NF is usually enough... but 3NF is better

HTH.

buho (A).

 

[LEICJDN1]

Thanks for the tips. I was unsure whether to use Access alone or involve Delphi. Was keen to produce a standalone program.

Will go away and do some reading.

Cheers.

 

[lespaul]

I actually prefer using the Database Desktop and creating Paradox tables instead of having to deal with Access. Open the Database Desktop, design your tables and save them. I usually put them in the directory where the application is going to be run from. Then you open the BDE, add a standard database driver, give it a name and browse to the directory. Then I drop a TDatabase component in the application and set it to the BDE alias.



Leslie

 

[AP81]

I like MySQL because it's free and quick. Table sizes are also small (unlike Access which gets very big quickly).




------------------------------------
There's no place like 127.0.0.1
------------------------------------

 

[LEICJDN1]

Thanks Lespaul and AP81.

I like the idea of using the Database dektop to create Paradox tables, then link to these from within Delphi. Perhaps I will explore this method as it means I can create a stand alone program to do what I want.

I am sure I will be back with more questions!

 

[buho]

True. If you want a standalone program Access is not an option.

buho (A).

 

[whosrdaddy]

but you still can create a standalone program using ms access db without having the requirement to have ms access on the user's PC.

--------------------------------------
What You See Is What You Get

 

[LEICJDN1]

whorsdaddy,

Yeah, have just read an excellent guide on aboutdelphi.com describing how to create a new .mdb file from within a delphi app, and then modify / search / update said database. Only proviso is the MS Jet engine is installed but I think I am right in saying anything above Win98 should be OK.

So, I should be able to create an app to check for the db file, create it if it does not exist, then lead on to the sections I need.

Any other tips / suggestions?

 

[Griffyn]

From my own experience, I agree 100% with buho. I've tried setting up Paradox tables with the Database Desktop (not since D4 mind you - maybe it's gotten better) and while it seems fairly straight forward, you then have issues with things like aliases, and needing the BDE installed. Moving the application to another machine is not drag and drop.

You mention you want to login - which would suggest you plan to go multi-user down the track. You will definitely want to use Access/Jet (and the ADO components) over Paradox in that case. Jet and multi-users is seamless, whereas I've always had troubles using the BDE.

 

[Griffyn]

Also - in answer to your question about typing in a list and having it auto-update, presuming the control doesn't automatically support this, it's pretty easy to add.

Just trap the OnKeyDown event for your TListBox (not OnKeyPress as it won't detect arrow keys). Set up a global var to hold the string of characters the user is typing.

Then, when the user presses a non-alphnumeric character (such as arrow keys), just clear the global var - as this indicates they no longer wish to 'build' on what they were typing.

If they type an alphanumeric, append it to your global var, and then search the TListBox using your global var, and set the ItemIndex to the first matching item.

 

[LEICJDN1]

Griffyn,

Thanks for the tips. May head to multi-user so may as well build in login from the start. I am heading more towards Access for a number of reasons, not least because I have a little experience of it.

Thanks also for your suggestion of how to implement an autoupdate list. Will try that once the basics are up and running.

Cheers.

 

[buho]

I really can't see the need to have table/dbase creation code in the program.

You can quickly create your empty dbase in Access and deploy a copy as part of the installation procedure.

The creation code will work only the first time the program is fired up, and for anything but very simple tables without relations it is not worth the work, besides it being dead code in normal use.

Furthermore, if the user dbase is lost for any reason, recreating an empty one in place of the working dbase will solve nothing.

Well... I'm know my views on this issue are very arguable.

buho (A)

 

[whosrdaddy]

buho,

I agree 100% with your vision. Every db app I make starts with a simple access db in conjunction with ADO VCL components. If for some reason, the access engine is to limited (too many records, multiuser) for a project, I upsize my db structure to sql server without having to change a single line of code. all you need is to store the connectionstring somewhere (in registry or in an inifile,...) and change it accordingly. That's called flexibility )

just my 2 cents,

Cheers

--------------------------------------
What You See Is What You Get

 

[LEICJDN1]

Yep, good points well made.

However, just fired up Delphi 7 Personal edition and no sign of any ADO components or other DB components? I assume this is because I use the personal edtion. Any way to add these components or are my plans stuffed before they even get beyond conception?

If so then I will build it in HTML and ASP but Delphi would have been nice!