CreateTableFrom Recordset in A97... from ADH 2000

[Pieter Linden]

I have a database of rental units etc that I'm using the
CreateTableFrom Recordset code from ADH 2000. Well, at the moment, I'm
doing a sanity check and testing it on my computer, with A2002, but
anyway....

I'm trying to create is building by building (a table for each pair of
buildings), so the format is something like this:

Building AptNo AptType Tenant Annual Income
Rent
---------------------------------------------------------------------------
A 4 Efficiency Smith $24,000
$675
A 9 1 Bedroom Johnson $32,000
$755
<blank line>
<blank line>
B 6 Efficiency Smith $24,000
$675
B 12 1 Bedroom Johnson $32,000
$755
B 23 2 Bedroom Jones $46,000
$755

---<new page>---

okay, creating the recordset in Access is easy. Since the code in the
book uses ADO, and A97 uses DAO, can I just copy ADO 2.x (like 2.8 for
WinXP/A2002) to the machine with A97/Win98, register it and use it?

Assuming that will work... the way I was thinking of doing this was to
run the function for each building, and for the second recordset, not
include the headers/field names but throw in a couple of blank rows.

Is this sane?

(At the end I can throw in the summary info - no sweat)... It's just
Apt Size, Income Level, Count Grouped by Apt Size.

but does the rest of this sound sane?

Thanks,
Pieter

['69 Camaro]

Hi, Pieter.

Since the code in the
book uses ADO, and A97 uses DAO, can I just copy ADO 2.x (like 2.8 for
WinXP/A2002) to the machine with A97/Win98, register it and use it?
Do you have a second license for Access XP or does your EULA have a
provision for you to run the components of this software on a second
computer? The practice of copying software applications or their components
from one computer to another without a second license to run the software
and its components on the second computer is commonly called "software
piracy."

Access 97 uses Jet 3.5, which, since it predates ADO, wasn't designed to use
ADO. You run the risk of losing data, because there can easily be
incompatibiliti es between Jet 3.5 and ADO that Jet just can't work with, no
matter how many service packs have been applied.
Assuming that will work... the way I was thinking of doing this was to
run the function for each building, and for the second recordset, not
include the headers/field names but throw in a couple of blank rows.

Is this sane?
Uh, . . . no. If you intend to have a normalized relational database, then
you don't need to create a separate table for each building. A properly
normalized database will prevent anomolies, eliminate redundancies, keep the
database size down, make it easier to retrieve the data, et cetera. Blank
rows to separate the different groups of records (Bldg. A vs. Bldg. B)within
the table is not advisable.

It's difficult for me to advise you on how to modify the ADH code to suit
your needs, because I haven't seen the code. I don't know if this data
already resides in Excel spreadsheets that you need to import into Access
(or link to them), or whether the data is already in Access and you are
trying to manipulate it. I'm guessing that the data is currently in Excel
spreadsheets.

In that light, I would suggest creating a table with the first Recordset,
but instead of creating a new table with each of the subsequent Recordsets,
appending those records to this table. The newly created table should have
a few extra fields in order to be able to identify the building (building
name or city, perhaps?) and which half of the building pair (Bldg. A or B).
However, to normalize this table, I would replace the "actual building name"
(if that's what you wanted to use) with the foreign key that identifies that
building's record from a separate, related table. For example, that related
table might have the following fields and values (watch out for the word
wrap):

Field name: Data Type: Example values:
----------------------------------------------------------------------
Bldg_ID (PK) Autonumber 1
BldgName Text Hurricane Apartments
Addr Text 111 Main St.
City Text Key West
State Text FL
et cetera ...

As each record is inserted into the new table, your code would need to
identify which Bldg_ID and which half of the building pair (A or B) the
record should include for these two fields in the table.

It will be much, much easier to retrieve your data later for forms and
reports if you organize your data this way.
(At the end I can throw in the summary info - no sweat)... It's just
Apt Size, Income Level, Count Grouped by Apt Size.
If you organize your data in the way that I have described, then you can
easily and quickly create queries that calculate the summary information for
you, instead of trying to store these calculated values in your table. This
would prevent the need to update the calculated values every time you added
records or made changes to the records.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
"Pieter Linden" <pi********@hot mail.com> wrote in message
news:bf******** *************** ***@posting.goo gle.com... I have a database of rental units etc that I'm using the
CreateTableFrom Recordset code from ADH 2000. Well, at the moment, I'm
doing a sanity check and testing it on my computer, with A2002, but
anyway....

I'm trying to create is building by building (a table for each pair of
buildings), so the format is something like this:

Building AptNo AptType Tenant Annual Income
Rent
-------------------------------------------------------------------------- - A 4 Efficiency Smith $24,000
$675
A 9 1 Bedroom Johnson $32,000
$755
<blank line>
<blank line>
B 6 Efficiency Smith $24,000
$675
B 12 1 Bedroom Johnson $32,000
$755
B 23 2 Bedroom Jones $46,000
$755

---<new page>---

okay, creating the recordset in Access is easy. Since the code in the
book uses ADO, and A97 uses DAO, can I just copy ADO 2.x (like 2.8 for
WinXP/A2002) to the machine with A97/Win98, register it and use it?

Assuming that will work... the way I was thinking of doing this was to
run the function for each building, and for the second recordset, not
include the headers/field names but throw in a couple of blank rows.

Is this sane?

(At the end I can throw in the summary info - no sweat)... It's just
Apt Size, Income Level, Count Grouped by Apt Size.

but does the rest of this sound sane?

Thanks,
Pieter

[david epsom dot com dot au]

"Pieter Linden" <pi********@hot mail.com> wrote in message
news:bf******** *************** ***@posting.goo gle.com...

okay, creating the recordset in Access is easy. Since the code in the
book uses ADO, and A97 uses DAO, can I just copy ADO 2.x (like 2.8 for
WinXP/A2002) to the machine with A97/Win98, register it and use it?

Probably best to download ADO from MS (MDAC, Microsoft Data Access
Components), and also download the latest copy of Jet at the same time. You
also need to set a reference to ADO in your database, and fully reference
each of your recordset and field objects etc, so that the computer can tell
if you are using an ADODB recordset or a DAO.recordset etc.

Also, although this is quite legal and practical and the way it is designed
to work, it is also /not/ best practice. It loads two different versions of
the Jet database engine. Best practice is to use only one version of the
database engine at a time. (And very best practice with an A97 database is
to use only Jet 3.51, but that is mostly because Jet 3.51 is fast, stable,
and dependable). But there are not at present any known incompatibiliti es
between Jet 4 and Jet 3.51.

(david)

[Pieter Linden]

"'69 Camaro" <Fo************ **************@ Spameater.orgZE RO_SPAM> wrote in message news:<Ntu3d.197 8$Bg5.1291@trnd dc07>...

Hi, Pieter.

Since the code in the
book uses ADO, and A97 uses DAO, can I just copy ADO 2.x (like 2.8 for
WinXP/A2002) to the machine with A97/Win98, register it and use it?

Do you have a second license for Access XP or does your EULA have a
provision for you to run the components of this software on a second
computer? The practice of copying software applications or their components
from one computer to another without a second license to run the software
and its components on the second computer is commonly called "software
piracy."

Access 97 uses Jet 3.5, which, since it predates ADO, wasn't designed to use
ADO. You run the risk of losing data, because there can easily be
incompatibiliti es between Jet 3.5 and ADO that Jet just can't work with, no
matter how many service packs have been applied.

Assuming that will work... the way I was thinking of doing this was to
run the function for each building, and for the second recordset, not
include the headers/field names but throw in a couple of blank rows.

Is this sane?

Uh, . . . no. If you intend to have a normalized relational database, then
you don't need to create a separate table for each building. A properly
normalized database will prevent anomolies, eliminate redundancies, keep the
database size down, make it easier to retrieve the data, et cetera. Blank
rows to separate the different groups of records (Bldg. A vs. Bldg. B)within
the table is not advisable.

It's difficult for me to advise you on how to modify the ADH code to suit
your needs, because I haven't seen the code. I don't know if this data
already resides in Excel spreadsheets that you need to import into Access
(or link to them), or whether the data is already in Access and you are
trying to manipulate it. I'm guessing that the data is currently in Excel
spreadsheets.

In that light, I would suggest creating a table with the first Recordset,
but instead of creating a new table with each of the subsequent Recordsets,
appending those records to this table. The newly created table should have
a few extra fields in order to be able to identify the building (building
name or city, perhaps?) and which half of the building pair (Bldg. A or B).
However, to normalize this table, I would replace the "actual building name"
(if that's what you wanted to use) with the foreign key that identifies that
building's record from a separate, related table. For example, that related
table might have the following fields and values (watch out for the word
wrap):

Field name: Data Type: Example values:
----------------------------------------------------------------------
Bldg_ID (PK) Autonumber 1
BldgName Text Hurricane Apartments
Addr Text 111 Main St.
City Text Key West
State Text FL
et cetera ...

As each record is inserted into the new table, your code would need to
identify which Bldg_ID and which half of the building pair (A or B) the
record should include for these two fields in the table.

It will be much, much easier to retrieve your data later for forms and
reports if you organize your data this way.

(At the end I can throw in the summary info - no sweat)... It's just
Apt Size, Income Level, Count Grouped by Apt Size.

If you organize your data in the way that I have described, then you can
easily and quickly create queries that calculate the summary information for
you, instead of trying to store these calculated values in your table. This
would prevent the need to update the calculated values every time you added
records or made changes to the records.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
"Pieter Linden" <pi********@hot mail.com> wrote in message
news:bf******** *************** ***@posting.goo gle.com...

I have a database of rental units etc that I'm using the
CreateTableFrom Recordset code from ADH 2000. Well, at the moment, I'm
doing a sanity check and testing it on my computer, with A2002, but
anyway....

I'm trying to create is building by building (a table for each pair of
buildings), so the format is something like this:

Building AptNo AptType Tenant Annual Income
Rent
--------------------------------------------------------------------------

-

A 4 Efficiency Smith $24,000
$675
A 9 1 Bedroom Johnson $32,000
$755
<blank line>
<blank line>
B 6 Efficiency Smith $24,000
$675
B 12 1 Bedroom Johnson $32,000
$755
B 23 2 Bedroom Jones $46,000
$755

---<new page>---

okay, creating the recordset in Access is easy. Since the code in the
book uses ADO, and A97 uses DAO, can I just copy ADO 2.x (like 2.8 for
WinXP/A2002) to the machine with A97/Win98, register it and use it?

Assuming that will work... the way I was thinking of doing this was to
run the function for each building, and for the second recordset, not
include the headers/field names but throw in a couple of blank rows.

Is this sane?

(At the end I can throw in the summary info - no sweat)... It's just
Apt Size, Income Level, Count Grouped by Apt Size.

but does the rest of this sound sane?

Thanks,
Pieter

The A97 databse is on a different computer. Yeah, I know all about
relational databases... I guess I'll just have to see if I can't
rewrite Ken's stuff in DAO... (should be interesting!)

Oh, my database is normalized - I'd use a query to get a "tabular"
dataset and then send that to Word, where I'm doing the reporting.