473,320 Members | 1,838 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

CreateTableFromRecordset in A97... from ADH 2000

I have a database of rental units etc that I'm using the
CreateTableFromRecordset 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
Nov 13 '05 #1
3 2143
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
incompatibilities 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********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om... I have a database of rental units etc that I'm using the
CreateTableFromRecordset 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

Nov 13 '05 #2

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
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 incompatibilities
between Jet 4 and Jet 3.51.

(david)
Nov 13 '05 #3
"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM> wrote in message news:<Ntu3d.1978$Bg5.1291@trnddc07>...
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
incompatibilities 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********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
I have a database of rental units etc that I'm using the
CreateTableFromRecordset 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.
Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Dagwood | last post by:
Good morning: At least it's morning where I am. :) I have a rather newbie question I'm afraid. I have VisualStudio.NET, and have installed it along with SQL server. However I can't seem to...
2
by: Jay Chan | last post by:
We have just installed a SQL Server 2000 (SP 3A) onto a computer that has Windows-2003 Server on it. Now, we cannot get access to that database server from other computers. Seem like this may be an...
10
by: noname | last post by:
MSDE 2000 Release A installed under windows 2000 pro will not communicate with SQL Server Manager nor MS Access on peer computer. Can someone help? Have set DISABLENETWORKPROTOCOLS=0 at install...
2
by: Scott | last post by:
Any help would be grateful :-) Problem: When I test my installation of Access 2000 MDE, I get the following error: "Unrecognised Format".. This means that Access 97 cannot read Access 2000. ...
1
by: tskelley | last post by:
Something strange has happened on my computer. I am unable to link tables from SQL Server 2000. I have tried from Access 97 and 2000, but keep getting the error "The Microsoft Jet database engine...
2
by: N. Graves | last post by:
I'm using the replace function in an unbound text field to clean up some data. I have Access 2000 on both an XP and 2000 machines. When I use this DB on an XP machine works great last a long...
47
by: ship | last post by:
Hi We need some advice: We are thinking of upgrading our Access database from Access 2000 to Access 2004. How stable is MS Office 2003? (particularly Access 2003). We are just a small...
5
by: Mo | last post by:
Hello all, I'm trying to set up an access 2000 .adp project connecting to a SQL server 2005 database. I can set up the connection ok, but once I have completed the setup process, I get the...
3
by: NEWSGROUPS | last post by:
I am in the midst of trying to convert about 25 Access 2000 to Access 2003. The new environment consists of Office/Access 2003 and Outlook 2003. When converting the back ends I have no problems....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.