Preferences for loading unbound data?

[Bill Stock]

The few times in the past that I've loaded unbound data, I've tended to
cheat and use temp tables (not really unbound) or use code for small
datasets.

I'm currently involved in a project that has numerous tables in the 200
column range, with several thousand rows of data. A consulting review prior
to my involvement stressed the wasted space and database speed as the major
impetus for normalization. Although the db actually works fairly well.
However, it's a bitch to manipulate the data with 200 column tables with
relative years and categories mashed together as columns. Plus having all
the data categories/types hard coded means that the same data types exists
with different spellings all over the place.

Unfortunately normalizing the data has broken most of the existing forms. My
initial thought was to use "smart labels" for the text boxes and use a
collection (class) to load and save the data. In fact this has worked fairly
well for one of the more complex forms, but there are quite a few more to
go. These forms have a very specific layout with hard coded labels (not
continuous) and a mixture of detailed and summarized records. It's looking
like building temp tables and binding them to the existing forms is still
the best way to go, given the "customized " layout of these forms.

So I'm curious, what's your preference for loading data to unbound forms?

[MacDermott]

Re: Preferences for loading unbound data?

First, a couple of general comments:
In my experience, normalizing the data is often a much bigger task than
rewriting the forms, so if you've got that part done, you're well on your
way.
I'm not so sure than non-normalized data (empty columns) wastes a lot of
space, although repetitious storing of information (another kind of
non-normalized data) certainly can. And you're very right - 200 columns is
way rough to deal with. Plus, that many columns is unusual unless you're
actually storing data in the column names - a real no-no.

I'm not sure what you mean by "unbound data" - forms can be unbound if they
have no RecordSource, but the data???
In any case, temp tables are going to cause quite a performance hit, because
they require you to write out to the hard drive. Not to mention if you're
permitting updates, you then have to figure out how to integrate the temp
table data back into the main data table(s). Is there a reason you can't
just use a SELECT query of some sort?

"Bill Stock" <Me7@Privacy.ne t> wrote in message
news:KvSdnV7bMI f_4cDenZ2dnUVZ_ tKdnZ2d@rogers. com...[color=blue]
> The few times in the past that I've loaded unbound data, I've tended to
> cheat and use temp tables (not really unbound) or use code for small
> datasets.
>
> I'm currently involved in a project that has numerous tables in the 200
> column range, with several thousand rows of data. A consulting review[/color]
prior[color=blue]
> to my involvement stressed the wasted space and database speed as the[/color]
major[color=blue]
> impetus for normalization. Although the db actually works fairly well.
> However, it's a bitch to manipulate the data with 200 column tables with
> relative years and categories mashed together as columns. Plus having all
> the data categories/types hard coded means that the same data types exists
> with different spellings all over the place.
>
> Unfortunately normalizing the data has broken most of the existing forms.[/color]
My[color=blue]
> initial thought was to use "smart labels" for the text boxes and use a
> collection (class) to load and save the data. In fact this has worked[/color]
fairly[color=blue]
> well for one of the more complex forms, but there are quite a few more to
> go. These forms have a very specific layout with hard coded labels (not
> continuous) and a mixture of detailed and summarized records. It's looking
> like building temp tables and binding them to the existing forms is still
> the best way to go, given the "customized " layout of these forms.
>
> So I'm curious, what's your preference for loading data to unbound forms?
>
>
>[/color]

[Bill Stock]

Re: Preferences for loading unbound data?


"MacDermott " <macdermott@nos pam.com> wrote in message
news:wdg7f.1871 1$vw6.1151@news read1.news.atl. earthlink.net.. .[color=blue]
> First, a couple of general comments:
> In my experience, normalizing the data is often a much bigger task than
> rewriting the forms, so if you've got that part done, you're well on your
> way.[/color]

The normalization and data conversion was fairly interesting work. Putting
it all back together is not. :)

[color=blue]
> I'm not so sure than non-normalized data (empty columns) wastes a lot of
> space, although repetitious storing of information (another kind of
> non-normalized data) certainly can. And you're very right - 200 columns
> is
> way rough to deal with. Plus, that many columns is unusual unless you're
> actually storing data in the column names - a real no-no.[/color]

Yes, they were.
[color=blue]
> I'm not sure what you mean by "unbound data" - forms can be unbound if
> they
> have no RecordSource, but the data???
> In any case, temp tables are going to cause quite a performance hit,
> because
> they require you to write out to the hard drive. Not to mention if you're
> permitting updates, you then have to figure out how to integrate the temp
> table data back into the main data table(s). Is there a reason you can't
> just use a SELECT query of some sort?[/color]

90% of the forms require updates. I was thinking I could get away with
SELECT queries for the ones that do not. But quite a few of the forms have a
mixture of detailed records, summaries and numerous exceptions to
category/type*relative year combinations. This leaves me with too many
subforms, very complex queries or unions. The temp tables seemed the lesser
of way too many evils. Plus they will allow me a simple path to updates. I
would have liked them to regroup their forms and show like data together,
but I'm stuck with multiple subforms per tab.

The temp tables should not be too bad, as the data is always filtered by
product. So I'm only loading a small number of records at any given time.

[color=blue]
> "Bill Stock" <Me7@Privacy.ne t> wrote in message
> news:KvSdnV7bMI f_4cDenZ2dnUVZ_ tKdnZ2d@rogers. com...[color=green]
>> The few times in the past that I've loaded unbound data, I've tended to
>> cheat and use temp tables (not really unbound) or use code for small
>> datasets.
>>
>> I'm currently involved in a project that has numerous tables in the 200
>> column range, with several thousand rows of data. A consulting review[/color]
> prior[color=green]
>> to my involvement stressed the wasted space and database speed as the[/color]
> major[color=green]
>> impetus for normalization. Although the db actually works fairly well.
>> However, it's a bitch to manipulate the data with 200 column tables with
>> relative years and categories mashed together as columns. Plus having all
>> the data categories/types hard coded means that the same data types
>> exists
>> with different spellings all over the place.
>>
>> Unfortunately normalizing the data has broken most of the existing forms.[/color]
> My[color=green]
>> initial thought was to use "smart labels" for the text boxes and use a
>> collection (class) to load and save the data. In fact this has worked[/color]
> fairly[color=green]
>> well for one of the more complex forms, but there are quite a few more to
>> go. These forms have a very specific layout with hard coded labels (not
>> continuous) and a mixture of detailed and summarized records. It's
>> looking
>> like building temp tables and binding them to the existing forms is still
>> the best way to go, given the "customized " layout of these forms.
>>
>> So I'm curious, what's your preference for loading data to unbound forms?
>>
>>
>>[/color]
>
>[/color]

[MacDermott]

Re: Preferences for loading unbound data?

Perhaps you could post an example of a case where you think a temp table
would be a good solution.
For example, how do you propose to create/populate this table? How will you
do updates from it?
I'm still mystified as to how this could be a workable approach...
[color=blue]
>
> 90% of the forms require updates. I was thinking I could get away with
> SELECT queries for the ones that do not. But quite a few of the forms have[/color]
a[color=blue]
> mixture of detailed records, summaries and numerous exceptions to
> category/type*relative year combinations. This leaves me with too many
> subforms, very complex queries or unions. The temp tables seemed the[/color]
lesser[color=blue]
> of way too many evils. Plus they will allow me a simple path to updates. I
> would have liked them to regroup their forms and show like data together,
> but I'm stuck with multiple subforms per tab.
>
> The temp tables should not be too bad, as the data is always filtered by
> product. So I'm only loading a small number of records at any given time.
>
>[color=green]
> > "Bill Stock" <Me7@Privacy.ne t> wrote in message
> > news:KvSdnV7bMI f_4cDenZ2dnUVZ_ tKdnZ2d@rogers. com...[color=darkred]
> >> The few times in the past that I've loaded unbound data, I've tended to
> >> cheat and use temp tables (not really unbound) or use code for small
> >> datasets.
> >>
> >> I'm currently involved in a project that has numerous tables in the 200
> >> column range, with several thousand rows of data. A consulting review[/color]
> > prior[color=darkred]
> >> to my involvement stressed the wasted space and database speed as the[/color]
> > major[color=darkred]
> >> impetus for normalization. Although the db actually works fairly well.
> >> However, it's a bitch to manipulate the data with 200 column tables[/color][/color][/color]
with[color=blue][color=green][color=darkred]
> >> relative years and categories mashed together as columns. Plus having[/color][/color][/color]
all[color=blue][color=green][color=darkred]
> >> the data categories/types hard coded means that the same data types
> >> exists
> >> with different spellings all over the place.
> >>
> >> Unfortunately normalizing the data has broken most of the existing[/color][/color][/color]
forms.[color=blue][color=green]
> > My[color=darkred]
> >> initial thought was to use "smart labels" for the text boxes and use a
> >> collection (class) to load and save the data. In fact this has worked[/color]
> > fairly[color=darkred]
> >> well for one of the more complex forms, but there are quite a few more[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> >> go. These forms have a very specific layout with hard coded labels (not
> >> continuous) and a mixture of detailed and summarized records. It's
> >> looking
> >> like building temp tables and binding them to the existing forms is[/color][/color][/color]
still[color=blue][color=green][color=darkred]
> >> the best way to go, given the "customized " layout of these forms.
> >>
> >> So I'm curious, what's your preference for loading data to unbound[/color][/color][/color]
forms?[color=blue][color=green][color=darkred]
> >>
> >>
> >>[/color]
> >
> >[/color]
>
>[/color]

[Larry Linson]

Re: Preferences for loading unbound data?

"Bill Stock" <Me7@Privacy.ne t> wrote
[color=blue]
> So I'm curious, what's your preference for
> loading data to unbound forms?[/color]

My very strong preference is "don't do it" because I (almost? absolutely?)
never create a unbound form for handling data.

Unbound forms are just fine for switchboards and control funtions (like
choosing limits and running reports), not for handling data.

I've worked on a few unbound forms for handling data, when doing maintenance
on someone else's database. And, since making it over to suit my taste was
not what I was contracted to do, I didn't, but just worked with what was
there.

Larry Linson
Microsoft Access MVP