Access 97 - Compact Database

[Guest]

There's alway something - The 2 PK table is a different one that I have also
been working on, this one has an auto number PK, and the name of it is
"Request#". When I replace ID from your original code with Request# I get a
date error "Syntax error in date expression". I am guessing that it does nt
like the # sign, do I need to change the field name or is there a special
character I can use to push that through?
Thanks again!!
=============================

Hope it works

Pieter

OH I FEEL SO STUPID!! Your explaination of first part second part
will be very helpful, It does consist of 2 fields.
THANK YOU!!

By PK i mean the Primary key Field of Your Table (Unique Value)
This May consist of several fields
in which case you'll need to write something in the order of
ORs.FindFirst "FirstPartOfKey='" & NRs.Fields("FirstPartOfKey").Value &
"'
And SeccondPartOfKey = '" & NRs("SeccondPartOfKey").Value & "'"
Note the apostrophes ('), they will be needed for Text Fields

For Dates You'd Use #
ie
ORs.FindFirst "FirstPartOfKey=#" &
Format(NRs.Fields("FirstPartOfKey").Value,"yyyy-mm-dd") & "# ....
Numeric fields should not have qualifers

Pieter

in
message What is PK?? and where will I find it?

I have searched google and MicroSoft, and my own poor memory but I
can't
find
anything. Thank you..

:

you have to replace ID with the actual PK.
The Db will be opened by the OpenDatabase, There won't be an Access
instance
though.
ie
open a version 97 DB, when I already have the NDb open with access
2003.
Will act similar to a linked table only you have access to all the
object
definitions (not to be mistanken with the ability to run forms/reports
etc)
you can however do DAO Stuff (Run Insert, Update & Delete Queries)

<< DOES THIS GO ALONG WITH THE [WHILE NOT]>>
Everything Between While & Wend

HtH & clarifies

Pieter


"Programmer - wannaB" <[email protected]>
wrote
in
message After reading my own post, it dawned on me that, what I wrote could
have
been
taken the wrong way. I hope it was not.

I really do enjoy figuring out things that I don't fully understand.

I have it partly working, but I do have some questions,

If these are 2 different version DBs (97 and 2003) will <Set ODb =
DbEngine.Workspaces(0).OpenDatabase("MyOldDb.mdb")> open a version
97
DB,
when I already have the NDb open with access 2003.
I have this working to a point when it get to here
ORs.FindFirst "ID=" & NRs.Fields("ID").Value
it comes back with an error "Item not found in this collection"
And although I don't see any indication that there is any other
database
open, I do see, in the Locals window that the access97 name was
taken,
but
isn't that just a name space, and it doesn't mena that the DB is
open...
I
am a bit confussed, and any help would be appreciated...

====================================
:

WOW !!! Thanks, I love when this happens, reading stuff that I can
barley
follow, blows my mind.

Will try to check this on some home crap as a first run, but
probably
not
today.. AFTER A QUICK scan I'm wondering if I understand some of
this
correctly, can you verify, my guess work?
"Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
wrote in message not at all difficult

Sub CompareTables()
Dim ODb As DAO.Database, NDb As DAO.Database
Dim ORs As DAO.Recordset, NRs AS DAO.Recordset
Dim OFld As DAO.Field, NFld As DAO.Field

Set ODb = DbEngine.Workspaces(0).OpenDatabase("MyOldDb.mdb")
<<<
Sets
ODb to
the database Name I change MyOldDb.mdb to.>>>
Set NDb = CurrentDb() << This would be the original DB, but I am
guessing
that the previous command has set the currentDb() to the newly
opened
MDB
???>>
<< On the next 2 lines I follow the New Rcord set , Old Record set,
but
dont
know anything about MyTable and OpenSnapshot >>
Set NRs = NDb.OpenRecordset("SELECT * FROM
MyTable",DAO.DbOpenSnapshot)
Set ORs = ODb.OpenRecordset("SELECT * FROM
MyTable",DAO.DbOpenSnapshot)
While Not NRs.EOF
ORs.FindFirst "ID=" & NRs.Fields("ID").Value
IF ORs.NoMatch Then
Debug.Print "ID Not Found in ODb"
Else
For Each NFld In NRs.Fields
Set OFld = ORs.Fields(NFld.Name) Then
If IsNull(NFld.Value)=True And IsNull(OFld.Value) =
False
Then
Debug.Print NRs.Fields("ID").Value, NFld.Name
ElseIf IsNull(OFld.Value)=True And IsNull(NFld.Value) =
False
Then << DOES THIS GO ALONG WITH THE [WHILE NOT]>>
Debug.Print NRs.Fields("ID").Value, NFld.Name
ElseIf OFld.Value <>NFld.Value Then
Debug.Print NRs.Fields("ID").Value, NFld.Name
End If
Next
NRs.MoveNext
Wend
ORs.MoveFirst
While Not ORs.EOF
NRs.FindFirst "ID=" & ORs.Fields("ID").Value
IF NRs.NoMatch Then
Debug.Print "ID Not Found in NDb"
End If
ORs.Movenext
Wend
ORs.Close : Set ORs = Nothing
NRs.Close : Set NRs = Nothing
ODb.Close : Set ODb = Nothing
Set NDb = Nothing
End Sub

Typed on the fly, do a bug hunt

HTH
Pieter

WHAT IS A BUG HUNT, AND WHAT IS HTH??

THANK YOU!!!
==============================================================
"Programmer - wannaB" <[email protected]>
wrote
in
message in a perfect world there would be no corruption...

Do you have some code that I can get a start with to do a record
by
record
comparison?? I can hack some code out, but I do not know enough
commands
to
put it all together alone..

:

well..
seems to be a well hidden corruption in the '97 db
personally I'd write some code to do a record by record
comparison
(in
a
perfect world)
or rely on the users to report discrepancies (the real world)
or simply put
sometimes, how did you do it - don't ask

Pieter

"Programmer - wannaB"
<[email protected]>
wrote
in
message
Seems to be clean now, but I don't fully understand, Here's
what
I've
done.
Imported tables one by one into new DB, found table that would
not
import,
37581 records. not sure how to clean it so I converted 97db to
2003db,
then
imported the table that would not import from 97, from the
2003db
into
the
new DB, and I did not review all records but the record count
is
the
same,
37581. (I hope that reads clear enough)

Should I feel comfortable enough to do that same process on
the
production
DB?
Thanks again for all your time.

:

Programmer - wannaB
<[email protected]>
wrote:

I converted an access 97 mdb to access 2003, and there were
2
conversion
errors.

The Table error here>> "-1017: Could not find field
'Description'."
I figured this could be resolved by doing a compact and
repair,
before
converting.

See point 8 at
http://www.granite.ab.ca/access/corruption/symptoms.htm

8) 1017 - "Could not find field "Description" It's possible
the
problem is with a table that has a self-join and Memo fields,
and
has
referential integrity enforced. ACC2000: Table Corruption
After
Referential Integrity Checks - 296389 But it's more likely
to
be a
sign of corruption.

The Module error here>> "There were compilation errors
during
the conversion or enabling of this database.@This might be
due
to
old
DAO
syntax that is no longer supported.
For an example on how to fixup the code, click Help.
@@2@611705@1"
doesn't tell me much of anything, these were copied from the
error
table.

I attempted to run a compact on the access 97 DB and it
seems
to
fail,
giving me an error reads Records can't be read; no read
permission
on
'D:\data\testdb1.mdb'.
So I close out and notice that a db1.mdb file has been
created
in
the
same
folder where I was running this test, and that db appears to