Re: Dbase3 Conversion

For BQ:


"Bob Quintal" <rquintal@xxxxxxxxxxxx> wrote in message
news:Xns97CCE87932A3ABQuintal@xxxxxxxxxxxxxxxxx
"Rick Wannall" <cwannall@xxxxxxxxx> wrote in
news:18Ncg.34843$Lm5.7478@xxxxxxxxxxxxxxxxxxxxxxxxxx:

Access is pretty slick, but it doesn't exactly "automagically
create any need index."

If you mean that it can sort easily through the interface,
you're absolutely correct.

and it does so by building a hidden index. If you set a relation
in a query, Access will create hidden indexes on the two fields
if none exist.

I'm trying to understand the purpose of this reply. You're referring to how
Access does things behind the scenes. The hidden indexes you refer to are
ephemeral. When you finish your query, the indexes are discarded. The user
cannot see or manipulate them through the index interface. Neither can the
developer through code. You're referring to something that a user will not
see or ever make any use of, except to be the unknowing beneficiary of it.
Why?


To create an index, however, you must tell it to do so. This
happens in the table design view. If you mark a field as
Primary Key, it makes an index to implement that. If you mark
a field as indexed (whether with or without duplication
allowed), it creates an index for that.

Yes, you can create additional indices to improve performance.


In code you can access a TableDef object and append a new
Index object to create more indexes. Those indexes show up in
the popup Indexes windows, where you can manually create more
indexes. You must use either code or this window to create
indexes based on more than one field.



None of this exactly solves the poster's problem of finding
correspondences between fields in different tables.

Actually the SET RELATION TO xxxxx INTO yyyyyy,
is the only way to set up a relation in dBASE III. The statement
is a pretty good clue as to the relationship, as it identifies
the field (or fields) used on one side of the (left) join.It's
then fairly simple to open the joined table and look for values
like those in the identified fields. ,


What you say sounds right, though I haven't seen dbase in so long that I
would have had to be reminded of this somehow. Absent such SET commands, or
other joining techniques used in the application, how would you determine
relationships other than by inspection?

One would
hope that the primary key in one table would be carried into a
field of the same name as a foreign key in any related table,
but even that must sometimes be violated. For instance, if
you have a Parts table, using PartID as the primary key, then
in your assembly table, where you pair one Master part with 1
to n Component parts, you cannot use the field PartID twice,
clearly. Typically, you prepend or append some distinguishing
text, such as Assembly_PartID and Component_PartID, or
PartID_Assembly, PartID_Component.


Calling them Mabel and Bob would be dumb.

And yet I have seen developers call a foreignkey field something that meant
something to them but gave no clue to the corresponding field in the
referenced table. They might as well have called the foreign key fields
Mabel and Bob. There are a lot of people who manage to pass themselves off
as developers, and I've cleaned up behind several of them. People do dumb
things, and decoding them takes analysis.


So, some analysis is needed in coming forward from dbase-era
data to Access. Sometimes not much. It has always been
possible to design tables well and to design related tables
properly. If the original designer did that, the analysis
required in the conversion is minimal.

Granted. I wrote an app in dbase III, around 1986. Various
employees upgraded to dBase IV, then Access 2, then '97. in
2001, the app was split and tables moved into SQL server. I was
consulted to investigate a modernization of the app, to tie it
into the MRP system. The structure of the system hadn't changed,
even the _dBaseLock fields were there. I deleted those and
added a field named website and a second called email to the
vendors table. No other changes were necessary.

--
Bob Quintal

PA is y I've altered my email address.



.

Relevant Pages

  • Re: Relationship question
    ... the design as a separate identity. ... CONTACTS_ID (Primary Key - Autonumber) ... "Jeff Boyce" wrote: ... Microsoft Office/Access MVP ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Dynamic column specification in table update
    ... Pro SQL Server 2000 Database Design - ... > primary key should be made up from your data. ... >> The only excuses tend to fall along the lines of laziness or ignorance. ...
    (microsoft.public.sqlserver.programming)
  • Re: junction tables for 3 many to many relationships
    ... aspects of design that I think are relevant. ... because the 4 fields could serve as a composite primary key. ... >> absolutely identifies a unique record. ... the ID's are aluminum bands placed on the legs of birds. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Key Violations in Append Queries
    ... Normally, if a primary key or other unique index is created, it is ... the design of the database requires uniqueness for the key involved. ... Whoever designed the database supposedly felt this was necessary. ... It may be that you should just omit this from the append query, ...
    (microsoft.public.access.queries)
  • Re: Dbase3 Conversion
    ... and it does so by building a hidden index. ... happens in the table design view. ... I wrote an app in dbase III, ...
    (comp.databases.ms-access)
Loading