Access 97 - Compact Database

  • Thread starter Programmer - wannaB
  • Start date

P

Programmer - wannaB

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. The Module error here>> "There were compilation errors during
the conversion or enabling of this [email protected] might be due to old DAO
syntax that is no longer supported.
For an example on how to fixup the code, click Help. @@[email protected]@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 be the same as
the db I was trying to compact. I open it and it compacts, and I can convert
it with no Table error, it still has the module error. OH I also tried this
after opening the db with exclusive access, and there is no security on this
file, ADMIN owns everything.

Can someone explain what has happened here?
Why would I get a no read permissions error on a local drive, as ADMIN ?
Then the db1 file created can this be used??
How can I compare to see what is different between the original and the db1?
How can I search all the Form Modules to find the DAO problem with the
module conversion?
 
Ad

Advertisements

P

Pieter Wijnen

Did you open a module in '97 & tried compile & save all modules before the
upgrade attempt?
If so try opening it with the /decompile switch (<path to msaccess> <yourdb>
/decompile)
& compile again.

If still no luck you may be using old syntax (remnants from Access 1.1 or
Access 2.0), no longer supported by Acc'2003

HTH

Pieter
 
T

Tony Toews [MVP]

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 [email protected] might be due to old DAO
syntax that is no longer supported.
For an example on how to fixup the code, click Help. @@[email protected]@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 be the same as
the db I was trying to compact. I open it and it compacts, and I can convert
it with no Table error, it still has the module error. OH I also tried this
after opening the db with exclusive access, and there is no security on this
file, ADMIN owns everything.

Can someone explain what has happened here?
Why would I get a no read permissions error on a local drive, as ADMIN ?
Then the db1 file created can this be used??
How can I compare to see what is different between the original and the db1?
How can I search all the Form Modules to find the DAO problem with the
module conversion?

Peter is correct in that compiling might help to find that DAO
problem.

However what I'd also do is import all the A97 objects into an A2000
MDB and see what happens.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
W

WANNABE

Thanks for the ideas guys!! I will try these on Friday, and post my results.
I appreciate every bit of help
======================================
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 [email protected] might be due to old DAO
syntax that is no longer supported.
For an example on how to fixup the code, click Help. @@[email protected]@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 be the same as
the db I was trying to compact. I open it and it compacts, and I can convert
it with no Table error, it still has the module error. OH I also tried this
after opening the db with exclusive access, and there is no security on this
file, ADMIN owns everything.

Can someone explain what has happened here?
Why would I get a no read permissions error on a local drive, as ADMIN ?
Then the db1 file created can this be used??
How can I compare to see what is different between the original and the db1?
How can I search all the Form Modules to find the DAO problem with the
module conversion?

Peter is correct in that compiling might help to find that DAO
problem.

However what I'd also do is import all the A97 objects into an A2000
MDB and see what happens.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
D

dave ivie

Programmer - wannaB said:
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. The Module error here>> "There were compilation errors during
the conversion or enabling of this [email protected] might be due to old DAO
syntax that is no longer supported.
For an example on how to fixup the code, click Help. @@[email protected]@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 be the same
as
the db I was trying to compact. I open it and it compacts, and I can
convert
it with no Table error, it still has the module error. OH I also tried
this
after opening the db with exclusive access, and there is no security on
this
file, ADMIN owns everything.

Can someone explain what has happened here?
Why would I get a no read permissions error on a local drive, as ADMIN ?
Then the db1 file created can this be used??
How can I compare to see what is different between the original and the
db1?
How can I search all the Form Modules to find the DAO problem with the
module conversion?
 
P

Programmer - wannaB

I imported all tables from 97 to clean 2003, and it displayed a popup that
said "Record is deleted", When I click on help I get this >>"Record is
deleted. (Error 3167)
You referred to a record that you deleted or that another user in a
multiuser environment deleted. Move to another record, and then try the
operation again."
I have narrowed it down to the table, and am looking to see what can be done
to prevent the error.

Any Ideas?
 
Ad

Advertisements

P

Pieter Wijnen

does suggest corruption (IMnHM)
have you tried to import the tabledefs only (no data)

Pieter
 
P

Programmer - wannaB

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.
 
P

Pieter Wijnen

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
 
P

Programmer - wannaB

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..
 
P

Pieter Wijnen

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")
Set NDb = CurrentDb()
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
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
 
Ad

Advertisements

W

WANNABE

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"
<[email protected]ine.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!!!
==============================================================
 
W

WANNABE

THANK YOU!!!
I will try that on Monday along with the code Peiter sent, and post my results.
I appreciate everyone's time and efforts.
========================================
Peter is correct in that compiling might help to find that DAO
problem.

However what I'd also do is import all the A97 objects into an
A2000 MDB and see what happens.

Hmm. I think I'd decompile (with full compact to discard the old
compiled code pages), then import everything into a new A97 MDB, and
*then* import into a new version. I don't ever want to let the
conversion fix something that ought to be fixed in the original
version, simply because I can't depend on the conversion process to
guess right about what should be fixed.

Get a correct source file in the old version, and then the
conversion to the new should come off without a hitch.
 
P

Programmer - wannaB

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...

====================================
WANNABE said:
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"
<[email protected]ine.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 said:
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..
 
P

Pieter Wijnen

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 said:
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...

====================================
WANNABE said:
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"
<[email protected]ine.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!!!
==============================================================
in
message news:[email protected]
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 [email protected] might be due to
old
DAO
syntax that is no longer supported.
For an example on how to fixup the code, click Help. @@[email protected]@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 be
the
same
as
the db I was trying to compact. I open it and it compacts, and I
can
convert
it with no Table error, it still has the module error. OH I also
tried
this
after opening the db with exclusive access, and there is no
security
on
this
file, ADMIN owns everything.

Can someone explain what has happened here?
Why would I get a no read permissions error on a local drive, as
ADMIN
?
Then the db1 file created can this be used??
How can I compare to see what is different between the original
and
the
db1?
How can I search all the Form Modules to find the DAO problem
with
the
module conversion?

Peter is correct in that compiling might help to find that DAO
problem.

However what I'd also do is import all the A97 objects into an
A2000
MDB and see what happens.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
P

Programmer - wannaB

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..

Pieter Wijnen said:
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 said:
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...

====================================
WANNABE said:
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"
<[email protected]ine.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!!!
==============================================================
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 [email protected] might be due to
old
DAO
syntax that is no longer supported.
For an example on how to fixup the code, click Help. @@[email protected]@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 be
the
same
as
the db I was trying to compact. I open it and it compacts, and I
can
convert
it with no Table error, it still has the module error. OH I also
tried
this
after opening the db with exclusive access, and there is no
security
on
this
file, ADMIN owns everything.

Can someone explain what has happened here?
Why would I get a no read permissions error on a local drive, as
ADMIN
?
Then the db1 file created can this be used??
How can I compare to see what is different between the original
and
the
db1?
How can I search all the Form Modules to find the DAO problem
with
the
module conversion?

Peter is correct in that compiling might help to find that DAO
problem.

However what I'd also do is import all the A97 objects into an
A2000
MDB and see what happens.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Ad

Advertisements

P

Pieter Wijnen

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

Programmer - wannaB said:
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..

Pieter Wijnen said:
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


in
message news:[email protected]
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"
<[email protected]ine.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 [email protected] might be due
to
old
DAO
syntax that is no longer supported.
For an example on how to fixup the code, click Help.
@@[email protected]@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 be
the
same
as
the db I was trying to compact. I open it and it compacts, and
I
can
convert
it with no Table error, it still has the module error. OH I
also
tried
this
after opening the db with exclusive access, and there is no
security
on
this
file, ADMIN owns everything.

Can someone explain what has happened here?
Why would I get a no read permissions error on a local drive,
as
ADMIN
?
Then the db1 file created can this be used??
How can I compare to see what is different between the
original
and
the
db1?
How can I search all the Form Modules to find the DAO problem
with
the
module conversion?

Peter is correct in that compiling might help to find that DAO
problem.

However what I'd also do is import all the A97 objects into an
A2000
MDB and see what happens.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -
http://msmvps.com/blogs/access/
 
P

Programmer - wannaB

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

Pieter Wijnen said:
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

Programmer - wannaB said:
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..

Pieter Wijnen said:
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


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"
<[email protected]ine.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 [email protected] might be due
to
old
DAO
syntax that is no longer supported.
For an example on how to fixup the code, click Help.
@@[email protected]@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 be
the
same
as
the db I was trying to compact. I open it and it compacts, and
I
can
convert
it with no Table error, it still has the module error. OH I
also
tried
this
after opening the db with exclusive access, and there is no
security
on
this
file, ADMIN owns everything.

Can someone explain what has happened here?
Why would I get a no read permissions error on a local drive,
as
ADMIN
?
Then the db1 file created can this be used??
How can I compare to see what is different between the
original
and
the
db1?
How can I search all the Form Modules to find the DAO problem
with
the
module conversion?

Peter is correct in that compiling might help to find that DAO
problem.

However what I'd also do is import all the A97 objects into an
A2000
MDB and see what happens.

Tony
--
 
P

Pieter Wijnen

Hope it works

Pieter

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

Pieter Wijnen said:
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 news:[email protected]
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"
<[email protected]ine.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 [email protected] might be
due
to
old
DAO
syntax that is no longer supported.
For an example on how to fixup the code, click Help.
@@[email protected]@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
be
the
same
as
the db I was trying to compact. I open it and it compacts,
and
I
can
convert
it with no Table error, it still has the module error. OH
I
also
tried
this
after opening the db with exclusive access, and there is no
security
on
this
file, ADMIN owns everything.

Can someone explain what has happened here?
Why would I get a no read permissions error on a local
drive,
as
ADMIN
?
Then the db1 file created can this be used??
How can I compare to see what is different between the
original
and
the
db1?
How can I search all the Form Modules to find the DAO
problem
with
the
module conversion?

Peter is correct in that compiling might help to find that
DAO
problem.

However what I'd also do is import all the A97 objects into
an
A2000
MDB and see what happens.

Tony
--
 
Ad

Advertisements

P

Programmer - wannaB

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!!
=============================
Pieter Wijnen said:
Hope it works

Pieter

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

Pieter Wijnen said:
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"
<[email protected]ine.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 [email protected] might be
due
to
old
DAO
syntax that is no longer supported.
For an example on how to fixup the code, click Help.
@@[email protected]@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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top