Brock, Christian T, PERSCOM
Christian.Brock at hoffman.army.mil
Wed Sep 24 13:44:01 CDT 2003
I have a database in access 2002 that needs to open an Access97 database and
then create a recordset for a table in it. This worked fine before I
converted the database from Access97 to access 2002, but does not now.
I can connect to the Access97 database using:
Set mdbsAccountTests = DBEngine(0).OpenDatabase(mstrPath & pstrdB)
But when I try to create the recordset:
Set rstAccountTests =
mdbsAccountTests.OpenRecordset(mstrAccountTestsSQL)
I get a 3343 error, Unrecognized database format.
I can't do anything about the access97 database being in Access97 format. It
is being generated by another program that can't be changed at this time. I
actually receive hundreds of these databases, so converting them to access
2002 is not feasible.
Christian Brock
-----Original Message-----
From: Gary Miller [mailto:gmiller at sistersnet.com]
Sent: Wednesday, 24 September 2003 14:20
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] DB design Brainteaser
Jim,
What a wonderfully thought out and detailed response. I am
still studying your model and it has brought up a couple of
questions in my mind that I need to straighten out with the
Company. Back to you soon and I really appreciate the time
that you have given this.
Gary Miller
----- Original Message -----
From: "Jim Dettman" <jimdettman at earthlink.net>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Wednesday, September 24, 2003 6:40 AM
Subject: RE: [AccessD] DB design Brainteaser
> Gary,
>
> Sorry I'm late getting back to you. Let's toy around
with an example and
> see how well it will fit. I've done this using a
surrogate key approach.
> No points off for any misses first time around<g>.
>
> tblOwners - One record per owner
> OwnerID - Autonumber - PK
> OwnerFirstName - Text
> OwnerLastName - Text
> etc
>
> tblFields - One record per field
> FieldID - Autonumber - PK
> FieldName - Text
> FieldLocation - Text (not sure how you'd specify this)
> etc.
>
> tblFieldOwners - One record per field/owner pair - This is
a many to many
> linking table.
> FieldOwnerID - Autonumber - PK
> OwnerID - CK1A
> FieldID - CK1B
>
> NOTE: As I mentioned at the top, the approach is using
surrogate keys.
> Here, it's really worthless. The approach causes
additional processing in
> that we need an index to validate the candidate key pair
of Owner/Field (you
> can only have one unique combination of each). So you
could do this:
>
> tblFieldOwners - One record per field/owner pair - This is
a many to many
> linking table.
> OwnerID - PK1A
> FieldID - PK1B
> SharePercentage - Decimal
>
> Which eliminates a field and an index. Depends on
whether or not you need
> to reference that relationship from other tables. For
consistency's sake,
> let's stick with the first.
>
>
> Now let's tackle the inventory side:
>
> tblProducts - One record per product
> ProductID - Autonumber - PK
> ProductName - Text
> ProductTypeID - Long - FK
> StockUOMID - Long - FK
>
> tblProductTypes - One record per product group (don't know
if you need this
> or not)
> ProductTypeID - Autonumber - PK
> TypeDescription - Text
>
> tblUOMs - One record per unit of measure
> UOMID - Autonumber - PK
> UOMCode - Text(6) - CK
> UOMDesc - Text
>
> Note: this would not be the full table. I've only
specified a few fields
> out of the total table that I would normally use. The UOM
table design that
> I have allows for conversion from one UOM to another.
This becomes helpful
> when buying/selling as the price can be based on a
Purchase/Sell unit of
> measure rather then the stocking one.
>
> tblLots - One record per lot
> LotID - Autonumber - PK
> HarvestDate - Date/time
> ExpireDate - Date/time
> ProductID - FK
> FieldID - FK
>
> tblLotOwners - One record per lot per field/Owner Pair. In
other words, for
> each lot, who owned that field at the time the lot was
harvested.
> LotOwnerID - Autonumber - PK
> LotID - FK
> OwnerID - FK
>
> tblLocations - One record per possible location (this
defines the master
> list of all valid locations to put something)
> LocationID - Autonumber - PK
> LocationCode - Text(6) - CK
> LocationType - Integer - Value Lookup (or a lookup
table) - I.e.. Rack, Bin,
> Silo, Barn, etc.
>
> tblLocTrack - One record per Lot/Loc
> LocTrackID - Autonumber - PK
> LocationID - Long - FK
> LotID - Long - FK
> LocTrackQty - Decimal - Note: in stock keeping UOM
> LocTrackExpirationDate - Date/time
> LastReceiptDate - Date/Time
> LastIssueDate - Date/Time
> LastCycleCount - Date/Time
>
>
> OK. Up to this point, we know what we have, who owns it
(not sure we have
> that totally covered yet), and where it is stored. Next
is a Inventory
> transaction table that tells us how the inventory gets
moved about. There
> is a lot of variation here in the way things can be done
and this is just
> one way. We can modify this as we go along. I'm sure
when we get into the
> selling logic that we'll need to or possibly add
additional tables.
>
> tblInvTransactions - One record per inventory transaction
(movement of
> inventory)
> InvTranID - Autonumber - PK
> TranDate - Date/time
> EnteredBy -
> EnteredOn - Date/time
> ProgramCode - Text(6) - I use this field to tag which
program created a
> transaction - great for trouble shooting.
> TranDate - Date/time
> TranType - Integer - Indicates type of document that
created this trans (ie.
> Balance Forward, Cust Order, PO, cycle count, physical inv
adj, Keypunch
> error, etc.)
> DocReference - Text(12) - Reference to document # that
created this
> transaction (ie. Customer Order Number)
> TranType - Text(1) - 'I'n or 'O'ut
> TranQty - Decimal - In stock keeping UOM
> LotID - Long - FK
> LocationID - Long - FK
>
> Note that we did not use the LocTrackID, but LotID and
LocationID. That's
> because tblInvTransactions is a historical table. The
records in it may far
> outlive the other records in the database, so it needs to
maintain a copy of
> actual data. We are breaking normalization rules a bit.
The correct way to
> do this is to use LocTrackID and have a
tblHistoricalInvTrans, which we move
> records to when we purge from the main table. At that
time, we would copy
> the actual field data from the other tables (thus creating
a flat file).
> It's up to you how you would want to handle it.
>
> I'm going to stop there and let you digest all that.
Looking back, I
> think the only thing not covered is what happens when
someone sells their
> ownership in a lot. I have to go back over you messages
and make sure I
> have this clear in my head. Fire away with any questions
you might have.
>
> It would also be nice if others chime in with comments.
>
> Jim Dettman
> President,
> Online Computer Services of WNY, Inc.
> (315) 699-3443
> jimdettman at earthlink.net
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
Gary Miller
> Sent: Tuesday, September 23, 2003 11:57 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] DB design Brainteaser
>
>
> Jim,
>
> I'll try and clarify a couple of things and expound on
> questions inline below...
>
> <<snip>>
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com