Accessing data in a large table

[Nick Mirro]

I have an access 2002 db managing content contributors to a taxonomic
(biological) web site. I have an imported table containing 180,000 records,
which is used locally as a reference to the taxonomic nodes managed by the
site's db app. These records each correspond to a taxonomic node in an
evolutionary tree.

Is the table too large for Access to use?

When a contributor to the site submits a block of images (for example), I
would like to store the taxonomic node metadata (organism or group, common
or scientific name) in that persons record. That way I can look up a
contributor and see what they have submitted, and know what node on the site
it pertains to.

I tried creating a smaller table of just the common names (4000 records) and
selecting them from a combo box. I then tried to use the requery method to
preload the data (see below), which didn't work. (data wouldn't preload -
see below)

Is there a "not too advanced" way to pull a record from the large table and
add it to a contributors records. Ideally, I would like to scroll an
alphabetic list from the large table instead of performing blind searches.

Nick



(Requery combo from the parentmost form)

Public Sub Form_Current()
Me![sbfrmContactInfo].Form![ContactsList].Requery

[Forms]![frmDataExchange]![sbfrmContactInfo].[Form]![SbfrmContactReason].[Fo
rm]![cmbAllReasons].Requery

[Forms]![frmDataExchange]![sbfrmContactInfo].[Form]![SbfrmContactReason].[Fo
rm]![cmbTaxa].[Form]![frmImageData].[Form]![cmbTaxa].Requery
End Sub



(From the form with the combo)

Private Sub Form_Open(Cancel As Integer)
Me.cmbTaxa.Requery
End Sub

 

[Bryan]

Nick,

What it sounds like you need is a method of referencing
records from a "contribution" table with "contributors".

The best way to do this is to create a table for the
contributors (if you haven't done so all ready) and add a
field to the "contributions" table that references a
particular contributor by way of a primary key. Here is
an example:

tblContributor
---------------------
ContributorID PK
Name
Address
....


tblContributions
----------------------
ContributionID PK
ContributorID FK (tblContributor)
ImageFile
Node
....

To see who contributed what, you simply have to reference
the primary key for the users name, and this can be
widely used in any other form or report you may have.

Bryan

 

[John Vinson]

I have an access 2002 db managing content contributors to a taxonomic
(biological) web site. I have an imported table containing 180,000 records,
which is used locally as a reference to the taxonomic nodes managed by the
site's db app. These records each correspond to a taxonomic node in an
evolutionary tree.

Is the table too large for Access to use?

Shouldn't be. There is no explicit limit on the number of records;
tables with well over a million records are not uncommon.

When a contributor to the site submits a block of images (for example), I
would like to store the taxonomic node metadata (organism or group, common
or scientific name) in that persons record. That way I can look up a
contributor and see what they have submitted, and know what node on the site
it pertains to.

If you're only storing text metadata, not images, you'll be OK -
Access is LOUSY at storing images; but it looks like you don't plan to
do so.

I tried creating a smaller table of just the common names (4000 records) and
selecting them from a combo box. I then tried to use the requery method to
preload the data (see below), which didn't work. (data wouldn't preload -
see below)

Is there a "not too advanced" way to pull a record from the large table and
add it to a contributors records. Ideally, I would like to scroll an
alphabetic list from the large table instead of performing blind searches.

The critical point is to get your table structure correctly
normalized, and your indexing optimized. You SHOULD NOT "pull a record
from the large table and add it to a contributor's records" - you
should instead update a *link* (a Foreign Key) in some table to relate
the existing Contributor record to the existing taxonomic record. For
example, if Karen Strickler were to contribute an image of Osmia aglia
(and she might! she's got some great photos) you would add a record to
the Contributions table with Karen's unique ID, and the unique ID of
that bee. I don't know your table structures but for a project of this
size, they must be done RIGHT or you'll have no end of trouble!

You don't say what problem you're having with the combo. A Combo is
limited to 64000 rows; but you don't need to scroll it, you can use
Autocomplete and just type the first few letters of the entry. Could
you expand on the nature of the problem? What's the combo's RowSource?

 

[Nick Mirro]

Thanks for helping with this. The combo autocomplete works, but I would be
much better off if I could smoothly scroll entries, since there are so many
different name permutations. In a different database, I believe I used
'requery' to preload the rowsource data for a combo into system memory.
That way I could immediately scroll all records instead of waiting for the
data to be pulled from the drive. Here is the combo rowsource:

SELECT tblCommonNames.NameID, tblCommonNames.NodeName FROM tblCommonNames
ORDER BY tblCommonNames.NodeName;

When using a combo with 4000 records, it can take many many scrolls of the
mouse just to get to the "Gs." After each scroll, the slider resets itself
taking new rows into account. This is the problem.

As for normalization, I think my db is normalized, using autonumber primary
keys and integer foreign keys. The imported table has the following
columns:

NameID AutoNumber
NCBINodeID Number
NodeName Text
NameClass Number

I was planning to do as you suggested, just pull the NameID into a
ContributorID/NodeIDs table. I just can't find a way to smoothly browse the
contents of the 4000 record table, let alone the name list in the 180,000
record table (includes non-vernacular). I'll have many entries to make and
I just picture myself pulling scroll bar sliders all day long trying to find
best matches to submissions.

Nick

 

[Adrian Jansen]

Are you taking advantage of the combo box auto-complete feature ? Type G
and all rows starting with "G" come up. Can save a lot of scrolling.

Also sounds like some queries using the LIKE predicate would be useful here.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[Nick Mirro]

Well I'm still wondering how to get combo data preloaded. It worked for a
smaller database combo box. Is this not possible here?

I think the combination of that with a lettered combo might work well. Does
the afterupdate event you describe require a sub or function?

Nick

 

[John Vinson]

I think the combination of that with a lettered combo might work well. Does
the afterupdate event you describe require a sub or function?

A Sub. If you click the ... icon by the AfterUpdate event and choose
the Code Builder, or just select [Event procedure] from the dropdown,
Access will give you the Sub and End Sub lines for free.

 

[Nick Mirro]

Would you mind showing me code that would work for this? I'm no developer,
just the guy trying to get the website on the map.

Nick

I think the combination of that with a lettered combo might work well. Does
the afterupdate event you describe require a sub or function?

A Sub. If you click the ... icon by the AfterUpdate event and choose
the Code Builder, or just select [Event procedure] from the dropdown,
Access will give you the Sub and End Sub lines for free.

 

[Nick Mirro]

This is an access form. The web database is oracle.

Nick

 

[Ngan Bui]

Where is this "AutoComplete" function? I have Access XP
and can't find it in the combox properties or the help
file.

Thanks.
Ngan

 

[John Vinson]

Where is this "AutoComplete" function? I have Access XP
and can't find it in the combox properties or the help
file.

Sorry... brain fade. It's Auto Expand.

 

[Adrian Jansen]

The property is actually called Auto Expand, in the DATA tab of the combo
box properties. Its normally set to Yes by default, so it will normally be
active.

Auto complete is actually something else ( and is more nuisance than
anything else )

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control