ACCESS97 AND VB6 FROZE AFTER RUNNING THIS CODE 
Author Message
 ACCESS97 AND VB6 FROZE AFTER RUNNING THIS CODE

Hi, I've tested this code (with slight modification for the VB example)
under Acces 97 and VB 6.0 and it frozes both access and VB.

The function just browses two databases schema looking for discrepancies in
the
tabledef or Fields (of a given tabledef)or properties(of a given field).
So it does a very extensive search but It does not froze while browsing all
the elements in the
various collections but only when I close the databases by:

"Set newBDD = Nothing
  Set remoteBDD = Nothing"

If ANYBODY has ANY insight on this problem or knows where I might get some
help
PLEASE TELL ME I'm stuck in my project because of that and I'm desperate for
a
solution, I've checked Microsoft Knowledge base and have not found any thing
related
to this problem PLEASE HELP ME !!!!!!!!!!!

If you want to try this function just
1. create a new database (test0) put the function code in a module.
2. make a copy of one of your existing database (test1 -> test2)
3. set a breakpoint in the line before Set newBDD = Nothing
4. Call the function with the name of the 2 databases from step 2
i.e. findDiscrepancies("c:\mydatabase\test1.mdb","c:\mydatabase\test2.mdb")
5. After the execution stops at the breakpoint look at your watch and hit F5
with a database having 40 tables with around 50 fields per tables it takes
around 6 minutes
on a Pentium mmx 200 for Access to regain control, around the same under
VB6.

THANKS IN ADVANCE TO ANYONE THAT GIVES ME A HAND...

Public Function findDiscrepancies(newFileName As String, fileName As String)
As Variant
    Dim newField, oldField As Field
    Dim wrkJet As Workspace
    Dim isField, isProp As Boolean
    Dim myProp As Property
    Set newBDD = DBEngine(0).OpenDatabase(newFileName, , True)
    Set remoteBDD = DBEngine(0).OpenDatabase(fileName, , True)
    '1- Find the new tables
    Dim newTabledef, oldTabledef As TableDef
    Dim newTabledefs, oldTabledefs As TableDefs
    Dim aName, sSourceTable As String
    Dim newTables() As String
    Dim fieldDiscrepancies() As String
    On Error GoTo errorHandler
    Dim i, j As Integer
    i = 0
    j = 0
    isField = False
    Set oldTabledefs = remoteBDD.TableDefs
    ProgressBar.Min = 0
    ProgressBar.Max = newBDD.TableDefs.Count
    ProgressBar.Visible = True
    For Each newTabledef In newBDD.TableDefs
        ProgressBar.Value = ProgressBar.Value + 1
        If ((newTabledef.Attributes And DB_SYSTEMOBJECT) = 0) Then
            Set oldTabledef = oldTabledefs(newTabledef.Name)
            '2- if no error at this point the table exist look for new
fields
            For Each newField In newTabledef.Fields
                isField = True
                Set oldField = oldTabledef(newField.Name)
                '3- if no error at this point check for the fields
properties
                For Each myProp In newField.Properties
                    isProp = True
                    If (oldField.Properties(myProp.Name) <> myProp And
myProp.Name <> "OrdinalPosition" _
                    And myProp.Name <> "ColumnOrder")
                     'Msgbox "some bull"
                    End If
continue_property:
                Next
                isProp = False
continue_field:
            Next
            isField = False
continue:
        End If
    Next
    Set newBDD = Nothing
    Set remoteBDD = Nothing
    findDiscrepancies = newTables
    Exit Function
errorHandler:
       If (isProp = True) Then
           Resume continue_property
       End If
    If isField Then
        ReDim Preserve fieldDiscrepancies(j)
        fieldDiscrepancies(i) = newField.Name + vbCrLf + newTabledef.Name
        j = j + 1
        'MsgBox "New field " + vbCrLf + newField.Name + vbCrLf + "in Table "
+ vbCrLf + newTabledef.Name
        Resume continue_field
    Else
        ReDim Preserve newTables(i)
        newTables(i) = newTabledef.Name
        i = i + 1
        Resume continue 'No need to go through the fields of the new
table...
    End If
End Function



Fri, 05 Jul 2002 03:00:00 GMT  
 ACCESS97 AND VB6 FROZE AFTER RUNNING THIS CODE
Try:

newBDD.Close
remoteBDD.Close
Set newBDD = Nothing
Set remoteBDD = Nothing

You could also try Dim-ing the two variables before they are used.

HTH,

Matt.


Quote:
> Hi, I've tested this code (with slight modification for the VB example)
> under Acces 97 and VB 6.0 and it frozes both access and VB.

> The function just browses two databases schema looking for discrepancies
in
> the
> tabledef or Fields (of a given tabledef)or properties(of a given field).
> So it does a very extensive search but It does not froze while browsing
all
> the elements in the
> various collections but only when I close the databases by:

> "Set newBDD = Nothing
>   Set remoteBDD = Nothing"

> If ANYBODY has ANY insight on this problem or knows where I might get some
> help
> PLEASE TELL ME I'm stuck in my project because of that and I'm desperate
for
> a
> solution, I've checked Microsoft Knowledge base and have not found any
thing
> related
> to this problem PLEASE HELP ME !!!!!!!!!!!

> If you want to try this function just
> 1. create a new database (test0) put the function code in a module.
> 2. make a copy of one of your existing database (test1 -> test2)
> 3. set a breakpoint in the line before Set newBDD = Nothing
> 4. Call the function with the name of the 2 databases from step 2
> i.e.

findDiscrepancies("c:\mydatabase\test1.mdb","c:\mydatabase\test2.mdb")

- Show quoted text -

Quote:
> 5. After the execution stops at the breakpoint look at your watch and hit
F5
> with a database having 40 tables with around 50 fields per tables it takes
> around 6 minutes
> on a Pentium mmx 200 for Access to regain control, around the same under
> VB6.

> THANKS IN ADVANCE TO ANYONE THAT GIVES ME A HAND...

> Public Function findDiscrepancies(newFileName As String, fileName As
String)
> As Variant
>     Dim newField, oldField As Field
>     Dim wrkJet As Workspace
>     Dim isField, isProp As Boolean
>     Dim myProp As Property
>     Set newBDD = DBEngine(0).OpenDatabase(newFileName, , True)
>     Set remoteBDD = DBEngine(0).OpenDatabase(fileName, , True)
>     '1- Find the new tables
>     Dim newTabledef, oldTabledef As TableDef
>     Dim newTabledefs, oldTabledefs As TableDefs
>     Dim aName, sSourceTable As String
>     Dim newTables() As String
>     Dim fieldDiscrepancies() As String
>     On Error GoTo errorHandler
>     Dim i, j As Integer
>     i = 0
>     j = 0
>     isField = False
>     Set oldTabledefs = remoteBDD.TableDefs
>     ProgressBar.Min = 0
>     ProgressBar.Max = newBDD.TableDefs.Count
>     ProgressBar.Visible = True
>     For Each newTabledef In newBDD.TableDefs
>         ProgressBar.Value = ProgressBar.Value + 1
>         If ((newTabledef.Attributes And DB_SYSTEMOBJECT) = 0) Then
>             Set oldTabledef = oldTabledefs(newTabledef.Name)
>             '2- if no error at this point the table exist look for new
> fields
>             For Each newField In newTabledef.Fields
>                 isField = True
>                 Set oldField = oldTabledef(newField.Name)
>                 '3- if no error at this point check for the fields
> properties
>                 For Each myProp In newField.Properties
>                     isProp = True
>                     If (oldField.Properties(myProp.Name) <> myProp And
> myProp.Name <> "OrdinalPosition" _
>                     And myProp.Name <> "ColumnOrder")
>                      'Msgbox "some bull"
>                     End If
> continue_property:
>                 Next
>                 isProp = False
> continue_field:
>             Next
>             isField = False
> continue:
>         End If
>     Next
>     Set newBDD = Nothing
>     Set remoteBDD = Nothing
>     findDiscrepancies = newTables
>     Exit Function
> errorHandler:
>        If (isProp = True) Then
>            Resume continue_property
>        End If
>     If isField Then
>         ReDim Preserve fieldDiscrepancies(j)
>         fieldDiscrepancies(i) = newField.Name + vbCrLf + newTabledef.Name
>         j = j + 1
>         'MsgBox "New field " + vbCrLf + newField.Name + vbCrLf + "in Table
"
> + vbCrLf + newTabledef.Name
>         Resume continue_field
>     Else
>         ReDim Preserve newTables(i)
>         newTables(i) = newTabledef.Name
>         i = i + 1
>         Resume continue 'No need to go through the fields of the new
> table...
>     End If
> End Function



Sat, 06 Jul 2002 03:00:00 GMT  
 ACCESS97 AND VB6 FROZE AFTER RUNNING THIS CODE
Also try setting the Field variables to nothing before closing the databases.

Quote:

> Try:

> newBDD.Close
> remoteBDD.Close
> Set newBDD = Nothing
> Set remoteBDD = Nothing

> You could also try Dim-ing the two variables before they are used.

> HTH,

> Matt.



> > Hi, I've tested this code (with slight modification for the VB example)
> > under Acces 97 and VB 6.0 and it frozes both access and VB.

> > The function just browses two databases schema looking for discrepancies
> in
> > the
> > tabledef or Fields (of a given tabledef)or properties(of a given field).
> > So it does a very extensive search but It does not froze while browsing
> all
> > the elements in the
> > various collections but only when I close the databases by:

> > "Set newBDD = Nothing
> >   Set remoteBDD = Nothing"

> > If ANYBODY has ANY insight on this problem or knows where I might get some
> > help
> > PLEASE TELL ME I'm stuck in my project because of that and I'm desperate
> for
> > a
> > solution, I've checked Microsoft Knowledge base and have not found any
> thing
> > related
> > to this problem PLEASE HELP ME !!!!!!!!!!!

> > If you want to try this function just
> > 1. create a new database (test0) put the function code in a module.
> > 2. make a copy of one of your existing database (test1 -> test2)
> > 3. set a breakpoint in the line before Set newBDD = Nothing
> > 4. Call the function with the name of the 2 databases from step 2
> > i.e.
> findDiscrepancies("c:\mydatabase\test1.mdb","c:\mydatabase\test2.mdb")
> > 5. After the execution stops at the breakpoint look at your watch and hit
> F5
> > with a database having 40 tables with around 50 fields per tables it takes
> > around 6 minutes
> > on a Pentium mmx 200 for Access to regain control, around the same under
> > VB6.

> > THANKS IN ADVANCE TO ANYONE THAT GIVES ME A HAND...

> > Public Function findDiscrepancies(newFileName As String, fileName As
> String)
> > As Variant
> >     Dim newField, oldField As Field
> >     Dim wrkJet As Workspace
> >     Dim isField, isProp As Boolean
> >     Dim myProp As Property
> >     Set newBDD = DBEngine(0).OpenDatabase(newFileName, , True)
> >     Set remoteBDD = DBEngine(0).OpenDatabase(fileName, , True)
> >     '1- Find the new tables
> >     Dim newTabledef, oldTabledef As TableDef
> >     Dim newTabledefs, oldTabledefs As TableDefs
> >     Dim aName, sSourceTable As String
> >     Dim newTables() As String
> >     Dim fieldDiscrepancies() As String
> >     On Error GoTo errorHandler
> >     Dim i, j As Integer
> >     i = 0
> >     j = 0
> >     isField = False
> >     Set oldTabledefs = remoteBDD.TableDefs
> >     ProgressBar.Min = 0
> >     ProgressBar.Max = newBDD.TableDefs.Count
> >     ProgressBar.Visible = True
> >     For Each newTabledef In newBDD.TableDefs
> >         ProgressBar.Value = ProgressBar.Value + 1
> >         If ((newTabledef.Attributes And DB_SYSTEMOBJECT) = 0) Then
> >             Set oldTabledef = oldTabledefs(newTabledef.Name)
> >             '2- if no error at this point the table exist look for new
> > fields
> >             For Each newField In newTabledef.Fields
> >                 isField = True
> >                 Set oldField = oldTabledef(newField.Name)
> >                 '3- if no error at this point check for the fields
> > properties
> >                 For Each myProp In newField.Properties
> >                     isProp = True
> >                     If (oldField.Properties(myProp.Name) <> myProp And
> > myProp.Name <> "OrdinalPosition" _
> >                     And myProp.Name <> "ColumnOrder")
> >                      'Msgbox "some bull"
> >                     End If
> > continue_property:
> >                 Next
> >                 isProp = False
> > continue_field:
> >             Next
> >             isField = False
> > continue:
> >         End If
> >     Next
> >     Set newBDD = Nothing
> >     Set remoteBDD = Nothing
> >     findDiscrepancies = newTables
> >     Exit Function
> > errorHandler:
> >        If (isProp = True) Then
> >            Resume continue_property
> >        End If
> >     If isField Then
> >         ReDim Preserve fieldDiscrepancies(j)
> >         fieldDiscrepancies(i) = newField.Name + vbCrLf + newTabledef.Name
> >         j = j + 1
> >         'MsgBox "New field " + vbCrLf + newField.Name + vbCrLf + "in Table
> "
> > + vbCrLf + newTabledef.Name
> >         Resume continue_field
> >     Else
> >         ReDim Preserve newTables(i)
> >         newTables(i) = newTabledef.Name
> >         i = i + 1
> >         Resume continue 'No need to go through the fields of the new
> > table...
> >     End If
> > End Function

--
--------------------------------------------------------------
Eric Wilson
Major: Computer Science
http://www.msu.edu/~wilsone8

"If the world is just one big simulation running on a computer,
then God is one hell of a programmer!"



Sat, 06 Jul 2002 03:00:00 GMT  
 ACCESS97 AND VB6 FROZE AFTER RUNNING THIS CODE
Hi Eric,
This does not make any difference,
The code freezes on the newBDD.Close.
Setting all the other variables to Nothing does not help either...
I'm really clueless about this one.



Quote:
> Also try setting the Field variables to nothing before closing the
databases.


> > Try:

> > newBDD.Close
> > remoteBDD.Close
> > Set newBDD = Nothing
> > Set remoteBDD = Nothing

> > You could also try Dim-ing the two variables before they are used.

> > HTH,

> > Matt.


message

> > > Hi, I've tested this code (with slight modification for the VB
example)
> > > under Acces 97 and VB 6.0 and it frozes both access and VB.

> > > The function just browses two databases schema looking for
discrepancies
> > in
> > > the
> > > tabledef or Fields (of a given tabledef)or properties(of a given
field).
> > > So it does a very extensive search but It does not froze while
browsing
> > all
> > > the elements in the
> > > various collections but only when I close the databases by:

> > > "Set newBDD = Nothing
> > >   Set remoteBDD = Nothing"

> > > If ANYBODY has ANY insight on this problem or knows where I might get
some
> > > help
> > > PLEASE TELL ME I'm stuck in my project because of that and I'm
desperate
> > for
> > > a
> > > solution, I've checked Microsoft Knowledge base and have not found any
> > thing
> > > related
> > > to this problem PLEASE HELP ME !!!!!!!!!!!

> > > If you want to try this function just
> > > 1. create a new database (test0) put the function code in a module.
> > > 2. make a copy of one of your existing database (test1 -> test2)
> > > 3. set a breakpoint in the line before Set newBDD = Nothing
> > > 4. Call the function with the name of the 2 databases from step 2
> > > i.e.
> > findDiscrepancies("c:\mydatabase\test1.mdb","c:\mydatabase\test2.mdb")
> > > 5. After the execution stops at the breakpoint look at your watch and
hit
> > F5
> > > with a database having 40 tables with around 50 fields per tables it
takes
> > > around 6 minutes
> > > on a Pentium mmx 200 for Access to regain control, around the same
under
> > > VB6.

> > > THANKS IN ADVANCE TO ANYONE THAT GIVES ME A HAND...

> > > Public Function findDiscrepancies(newFileName As String, fileName As
> > String)
> > > As Variant
> > >     Dim newField, oldField As Field
> > >     Dim wrkJet As Workspace
> > >     Dim isField, isProp As Boolean
> > >     Dim myProp As Property
> > >     Set newBDD = DBEngine(0).OpenDatabase(newFileName, , True)
> > >     Set remoteBDD = DBEngine(0).OpenDatabase(fileName, , True)
> > >     '1- Find the new tables
> > >     Dim newTabledef, oldTabledef As TableDef
> > >     Dim newTabledefs, oldTabledefs As TableDefs
> > >     Dim aName, sSourceTable As String
> > >     Dim newTables() As String
> > >     Dim fieldDiscrepancies() As String
> > >     On Error GoTo errorHandler
> > >     Dim i, j As Integer
> > >     i = 0
> > >     j = 0
> > >     isField = False
> > >     Set oldTabledefs = remoteBDD.TableDefs
> > >     ProgressBar.Min = 0
> > >     ProgressBar.Max = newBDD.TableDefs.Count
> > >     ProgressBar.Visible = True
> > >     For Each newTabledef In newBDD.TableDefs
> > >         ProgressBar.Value = ProgressBar.Value + 1
> > >         If ((newTabledef.Attributes And DB_SYSTEMOBJECT) = 0) Then
> > >             Set oldTabledef = oldTabledefs(newTabledef.Name)
> > >             '2- if no error at this point the table exist look for new
> > > fields
> > >             For Each newField In newTabledef.Fields
> > >                 isField = True
> > >                 Set oldField = oldTabledef(newField.Name)
> > >                 '3- if no error at this point check for the fields
> > > properties
> > >                 For Each myProp In newField.Properties
> > >                     isProp = True
> > >                     If (oldField.Properties(myProp.Name) <> myProp And
> > > myProp.Name <> "OrdinalPosition" _
> > >                     And myProp.Name <> "ColumnOrder")
> > >                      'Msgbox "some bull"
> > >                     End If
> > > continue_property:
> > >                 Next
> > >                 isProp = False
> > > continue_field:
> > >             Next
> > >             isField = False
> > > continue:
> > >         End If
> > >     Next
> > >     Set newBDD = Nothing
> > >     Set remoteBDD = Nothing
> > >     findDiscrepancies = newTables
> > >     Exit Function
> > > errorHandler:
> > >        If (isProp = True) Then
> > >            Resume continue_property
> > >        End If
> > >     If isField Then
> > >         ReDim Preserve fieldDiscrepancies(j)
> > >         fieldDiscrepancies(i) = newField.Name + vbCrLf +
newTabledef.Name
> > >         j = j + 1
> > >         'MsgBox "New field " + vbCrLf + newField.Name + vbCrLf + "in
Table
> > "
> > > + vbCrLf + newTabledef.Name
> > >         Resume continue_field
> > >     Else
> > >         ReDim Preserve newTables(i)
> > >         newTables(i) = newTabledef.Name
> > >         i = i + 1
> > >         Resume continue 'No need to go through the fields of the new
> > > table...
> > >     End If
> > > End Function

> --
> --------------------------------------------------------------
> Eric Wilson
> Major: Computer Science
> http://www.msu.edu/~wilsone8

> "If the world is just one big simulation running on a computer,
> then God is one hell of a programmer!"



Sat, 06 Jul 2002 03:00:00 GMT  
 ACCESS97 AND VB6 FROZE AFTER RUNNING THIS CODE
Hi matt,
didn't make any difference...
Any other idea ?


Quote:
> Try:

> newBDD.Close
> remoteBDD.Close
> Set newBDD = Nothing
> Set remoteBDD = Nothing

> You could also try Dim-ing the two variables before they are used.

> HTH,

> Matt.



> > Hi, I've tested this code (with slight modification for the VB example)
> > under Acces 97 and VB 6.0 and it frozes both access and VB.

> > The function just browses two databases schema looking for discrepancies
> in
> > the
> > tabledef or Fields (of a given tabledef)or properties(of a given field).
> > So it does a very extensive search but It does not froze while browsing
> all
> > the elements in the
> > various collections but only when I close the databases by:

> > "Set newBDD = Nothing
> >   Set remoteBDD = Nothing"

> > If ANYBODY has ANY insight on this problem or knows where I might get
some
> > help
> > PLEASE TELL ME I'm stuck in my project because of that and I'm desperate
> for
> > a
> > solution, I've checked Microsoft Knowledge base and have not found any
> thing
> > related
> > to this problem PLEASE HELP ME !!!!!!!!!!!

> > If you want to try this function just
> > 1. create a new database (test0) put the function code in a module.
> > 2. make a copy of one of your existing database (test1 -> test2)
> > 3. set a breakpoint in the line before Set newBDD = Nothing
> > 4. Call the function with the name of the 2 databases from step 2
> > i.e.
> findDiscrepancies("c:\mydatabase\test1.mdb","c:\mydatabase\test2.mdb")
> > 5. After the execution stops at the breakpoint look at your watch and
hit
> F5
> > with a database having 40 tables with around 50 fields per tables it
takes
> > around 6 minutes
> > on a Pentium mmx 200 for Access to regain control, around the same under
> > VB6.

> > THANKS IN ADVANCE TO ANYONE THAT GIVES ME A HAND...

> > Public Function findDiscrepancies(newFileName As String, fileName As
> String)
> > As Variant
> >     Dim newField, oldField As Field
> >     Dim wrkJet As Workspace
> >     Dim isField, isProp As Boolean
> >     Dim myProp As Property
> >     Set newBDD = DBEngine(0).OpenDatabase(newFileName, , True)
> >     Set remoteBDD = DBEngine(0).OpenDatabase(fileName, , True)
> >     '1- Find the new tables
> >     Dim newTabledef, oldTabledef As TableDef
> >     Dim newTabledefs, oldTabledefs As TableDefs
> >     Dim aName, sSourceTable As String
> >     Dim newTables() As String
> >     Dim fieldDiscrepancies() As String
> >     On Error GoTo errorHandler
> >     Dim i, j As Integer
> >     i = 0
> >     j = 0
> >     isField = False
> >     Set oldTabledefs = remoteBDD.TableDefs
> >     ProgressBar.Min = 0
> >     ProgressBar.Max = newBDD.TableDefs.Count
> >     ProgressBar.Visible = True
> >     For Each newTabledef In newBDD.TableDefs
> >         ProgressBar.Value = ProgressBar.Value + 1
> >         If ((newTabledef.Attributes And DB_SYSTEMOBJECT) = 0) Then
> >             Set oldTabledef = oldTabledefs(newTabledef.Name)
> >             '2- if no error at this point the table exist look for new
> > fields
> >             For Each newField In newTabledef.Fields
> >                 isField = True
> >                 Set oldField = oldTabledef(newField.Name)
> >                 '3- if no error at this point check for the fields
> > properties
> >                 For Each myProp In newField.Properties
> >                     isProp = True
> >                     If (oldField.Properties(myProp.Name) <> myProp And
> > myProp.Name <> "OrdinalPosition" _
> >                     And myProp.Name <> "ColumnOrder")
> >                      'Msgbox "some bull"
> >                     End If
> > continue_property:
> >                 Next
> >                 isProp = False
> > continue_field:
> >             Next
> >             isField = False
> > continue:
> >         End If
> >     Next
> >     Set newBDD = Nothing
> >     Set remoteBDD = Nothing
> >     findDiscrepancies = newTables
> >     Exit Function
> > errorHandler:
> >        If (isProp = True) Then
> >            Resume continue_property
> >        End If
> >     If isField Then
> >         ReDim Preserve fieldDiscrepancies(j)
> >         fieldDiscrepancies(i) = newField.Name + vbCrLf +
newTabledef.Name
> >         j = j + 1
> >         'MsgBox "New field " + vbCrLf + newField.Name + vbCrLf + "in
Table
> "
> > + vbCrLf + newTabledef.Name
> >         Resume continue_field
> >     Else
> >         ReDim Preserve newTables(i)
> >         newTables(i) = newTabledef.Name
> >         i = i + 1
> >         Resume continue 'No need to go through the fields of the new
> > table...
> >     End If
> > End Function



Sat, 06 Jul 2002 03:00:00 GMT  
 ACCESS97 AND VB6 FROZE AFTER RUNNING THIS CODE
I couldn't help but notice the 'error' in the declarations:

Quote:
>    Dim newField, oldField As Field

That should be

Dim newField as Field, oldField as Field
or
Dim newField as Field
Dim oldField as Field
or even
Dim newField as Field: Dim oldField as Field

Even without option explicit the code given here
doesn't run, so I haven't checked any further.

(David L Graham)

Quote:

>Hi, I've tested this code (with slight modification for the VB example)
>under Acces 97 and VB 6.0 and it frozes both access and VB.

>The function just browses two databases schema looking for discrepancies in
>tabledef or Fields (of a given tabledef)or properties(of a given field).
>So it does a very extensive search but It does not froze while browsing all
>the elements in the
>various collections but only when I close the databases by:

>"Set newBDD = Nothing
>  Set remoteBDD = Nothing"

>If ANYBODY has ANY insight on this problem or knows where I might get some
>PLEASE TELL ME I'm stuck in my project because of that and I'm desperate
for
>solution, I've checked Microsoft Knowledge base and have not found any
thing
>related
>to this problem PLEASE HELP ME !!!!!!!!!!!

>If you want to try this function just
>1. create a new database (test0) put the function code in a module.
>2. make a copy of one of your existing database (test1 -> test2)
>3. set a breakpoint in the line before Set newBDD = Nothing
>4. Call the function with the name of the 2 databases from step 2
>i.e. findDiscrepancies("c:\mydatabase\test1.mdb","c:\mydatabase\test2.mdb")
>5. After the execution stops at the breakpoint look at your watch and hit
F5
>with a database having 40 tables with around 50 fields per tables it takes
>around 6 minutes
>on a Pentium mmx 200 for Access to regain control, around the same under
>VB6.

>THANKS IN ADVANCE TO ANYONE THAT GIVES ME A HAND...

>Public Function findDiscrepancies(newFileName As String, fileName As
String)
>As Variant
>    Dim newField, oldField As Field
>    Dim wrkJet As Workspace
>    Dim isField, isProp As Boolean
>    Dim myProp As Property
>    Set newBDD = DBEngine(0).OpenDatabase(newFileName, , True)
>    Set remoteBDD = DBEngine(0).OpenDatabase(fileName, , True)
>    '1- Find the new tables
>    Dim newTabledef, oldTabledef As TableDef
>    Dim newTabledefs, oldTabledefs As TableDefs
>    Dim aName, sSourceTable As String
>    Dim newTables() As String
>    Dim fieldDiscrepancies() As String
>    On Error GoTo errorHandler
>    Dim i, j As Integer
>    i = 0
>    j = 0
>    isField = False
>    Set oldTabledefs = remoteBDD.TableDefs
>    ProgressBar.Min = 0
>    ProgressBar.Max = newBDD.TableDefs.Count
>    ProgressBar.Visible = True
>    For Each newTabledef In newBDD.TableDefs
>        ProgressBar.Value = ProgressBar.Value + 1
>        If ((newTabledef.Attributes And DB_SYSTEMOBJECT) = 0) Then
>            Set oldTabledef = oldTabledefs(newTabledef.Name)
>            '2- if no error at this point the table exist look for new
>fields
>            For Each newField In newTabledef.Fields
>                isField = True
>                Set oldField = oldTabledef(newField.Name)
>                '3- if no error at this point check for the fields
>properties
>                For Each myProp In newField.Properties
>                    isProp = True
>                    If (oldField.Properties(myProp.Name) <> myProp And
>myProp.Name <> "OrdinalPosition" _
>                    And myProp.Name <> "ColumnOrder")
>                     'Msgbox "some bull"
>                    End If
>continue_property:
>                Next
>                isProp = False
>continue_field:
>            Next
>            isField = False
>continue:
>        End If
>    Next
>    Set newBDD = Nothing
>    Set remoteBDD = Nothing
>    findDiscrepancies = newTables
>    Exit Function
>errorHandler:
>       If (isProp = True) Then
>           Resume continue_property
>       End If
>    If isField Then
>        ReDim Preserve fieldDiscrepancies(j)
>        fieldDiscrepancies(i) = newField.Name + vbCrLf + newTabledef.Name
>        j = j + 1
>        'MsgBox "New field " + vbCrLf + newField.Name + vbCrLf + "in Table
"
>+ vbCrLf + newTabledef.Name
>        Resume continue_field
>    Else
>        ReDim Preserve newTables(i)
>        newTables(i) = newTabledef.Name
>        i = i + 1
>        Resume continue 'No need to go through the fields of the new
>table...
>    End If
>End Function



Sun, 07 Jul 2002 03:00:00 GMT  
 ACCESS97 AND VB6 FROZE AFTER RUNNING THIS CODE
this was as far as i got:

Quote:
>     Dim newField, oldField As Field

    dim newField as field
Quote:
>     Dim newTabledefs As TableDef, oldTabledefs As TableDefs

    dim newTabledefs as tabledefs
Quote:
>                Set oldField = oldTabledef(newField.Name)

    set oldField = oldTabledef.fields(newField.name)

Quote:
>                If (oldField.Properties(myProp.Name) <> myProp) then

    Invalid operation.  What were you intending here?

(David L Graham)

Quote:
>So it does a very extensive search but It does not froze while
>the elements in the
>various collections but only when I close the databases by:



Quote:
>Hi david,
>Thanks for your answer,
>Of course you're right about the declaration, newField is interpreted as a
>Variant
>which is not a big deal here, but 2 variables declaration were missing from
>my previous code
>(They were global) and I forgot to suppress some lines concerning a
>ProgressBar that I
>pass to the function in its "real version" I've done the changes and the
new
>version is attached
>to this mail.



>> I couldn't help but notice the 'error' in the declarations:

>> >    Dim newField, oldField As Field

>> That should be

>> Dim newField as Field, oldField as Field
>> or
>> Dim newField as Field
>> Dim oldField as Field
>> or even
>> Dim newField as Field: Dim oldField as Field

>> Even without option explicit the code given here
>> doesn't run, so I haven't checked any further.

>> (David L Graham)


>> >Hi, I've tested this code (with slight modification for the VB example)
>> >under Acces 97 and VB 6.0 and it frozes both access and VB.

>> >The function just browses two databases schema looking for discrepancies
>in
>> >tabledef or Fields (of a given tabledef)or properties(of a given field).
>> >So it does a very extensive search but It does not froze while browsing
>all
>> >the elements in the
>> >various collections but only when I close the databases by:

>> >"Set newBDD = Nothing
>> >  Set remoteBDD = Nothing"

>> >If ANYBODY has ANY insight on this problem or knows where I might get
>some
>> >PLEASE TELL ME I'm stuck in my project because of that and I'm desperate
>> for
>> >solution, I've checked Microsoft Knowledge base and have not found any
>> thing
>> >related
>> >to this problem PLEASE HELP ME !!!!!!!!!!!

>> >If you want to try this function just
>> >1. create a new database (test0) put the function code in a module.
>> >2. make a copy of one of your existing database (test1 -> test2)
>> >3. set a breakpoint in the line before Set newBDD = Nothing
>> >4. Call the function with the name of the 2 databases from step 2
>> >i.e.
>findDiscrepancies("c:\mydatabase\test1.mdb","c:\mydatabase\test2.mdb")
>> >5. After the execution stops at the breakpoint look at your watch and
hit
>> F5
>> >with a database having 40 tables with around 50 fields per tables it
>takes
>> >around 6 minutes
>> >on a Pentium mmx 200 for Access to regain control, around the same under
>> >VB6.

>> >THANKS IN ADVANCE TO ANYONE THAT GIVES ME A HAND...

>> >Public Function findDiscrepancies(newFileName As String, fileName As
>> String)
>> >As Variant
>> >    Dim newField, oldField As Field
>> >    Dim wrkJet As Workspace
>> >    Dim isField, isProp As Boolean
>> >    Dim myProp As Property
>> >    Set newBDD = DBEngine(0).OpenDatabase(newFileName, , True)
>> >    Set remoteBDD = DBEngine(0).OpenDatabase(fileName, , True)
>> >    '1- Find the new tables
>> >    Dim newTabledef, oldTabledef As TableDef
>> >    Dim newTabledefs, oldTabledefs As TableDefs
>> >    Dim aName, sSourceTable As String
>> >    Dim newTables() As String
>> >    Dim fieldDiscrepancies() As String
>> >    On Error GoTo errorHandler
>> >    Dim i, j As Integer
>> >    i = 0
>> >    j = 0
>> >    isField = False
>> >    Set oldTabledefs = remoteBDD.TableDefs
>> >    ProgressBar.Min = 0
>> >    ProgressBar.Max = newBDD.TableDefs.Count
>> >    ProgressBar.Visible = True
>> >    For Each newTabledef In newBDD.TableDefs
>> >        ProgressBar.Value = ProgressBar.Value + 1
>> >        If ((newTabledef.Attributes And DB_SYSTEMOBJECT) = 0) Then
>> >            Set oldTabledef = oldTabledefs(newTabledef.Name)
>> >            '2- if no error at this point the table exist look for new
>> >fields
>> >            For Each newField In newTabledef.Fields
>> >                isField = True
>> >                Set oldField = oldTabledef(newField.Name)
>> >                '3- if no error at this point check for the fields
>> >properties
>> >                For Each myProp In newField.Properties
>> >                    isProp = True
>> >                    If (oldField.Properties(myProp.Name) <> myProp And
>> >myProp.Name <> "OrdinalPosition" _
>> >                    And myProp.Name <> "ColumnOrder")
>> >                     'Msgbox "some bull"
>> >                    End If
>> >continue_property:
>> >                Next
>> >                isProp = False
>> >continue_field:
>> >            Next
>> >            isField = False
>> >continue:
>> >        End If
>> >    Next
>> >    Set newBDD = Nothing
>> >    Set remoteBDD = Nothing
>> >    findDiscrepancies = newTables
>> >    Exit Function
>> >errorHandler:
>> >       If (isProp = True) Then
>> >           Resume continue_property
>> >       End If
>> >    If isField Then
>> >        ReDim Preserve fieldDiscrepancies(j)
>> >        fieldDiscrepancies(i) = newField.Name + vbCrLf +
newTabledef.Name
>> >        j = j + 1
>> >        'MsgBox "New field " + vbCrLf + newField.Name + vbCrLf + "in
>Table
>> "
>> >+ vbCrLf + newTabledef.Name
>> >        Resume continue_field
>> >    Else
>> >        ReDim Preserve newTables(i)
>> >        newTables(i) = newTabledef.Name
>> >        i = i + 1
>> >        Resume continue 'No need to go through the fields of the new
>> >table...
>> >    End If
>> >End Function



Wed, 17 Jul 2002 03:00:00 GMT  
 ACCESS97 AND VB6 FROZE AFTER RUNNING THIS CODE
Hi David

Quote:
> this was as far as i got:

> >                If (oldField.Properties(myProp.Name) <> myProp) then

>     Invalid operation.  What were you intending here?

Well that's how I can detect if a property has changed. When I get an invcalid
operation which
is trapped by the error handling mechanism That means I cannot access the value
for the given
property because by default Access changes oldField.Properties(myProp.Name) to
oldField.Properties(myProp.Name) .value. What I really wanted to do is to
compare the two
peoperty objects oldField.Properties(myProp.Name) and myProp to check if they
are the same
so what I would need is an overloaded comparison operator on properties.
In this case is the value of a property returns Invalid operation I don't do a
thing, the error
is trapped and then the execution goes to the next property after jumping to

continue_property:
                Next

I hope that answers your question.
BUT nonetheless the code should work you just have to make sure that the
de{*filter*} only stops for
unhandled exceptions. Go to Tools-Options-advanced and select the "Break on
unhandled errors"
select button. that should do it.

Quote:
> (David L Graham)

> >So it does a very extensive search but It does not froze while
> >the elements in the
> >various collections but only when I close the databases by:



> >Hi david,
> >Thanks for your answer,
> >Of course you're right about the declaration, newField is interpreted as a
> >Variant
> >which is not a big deal here, but 2 variables declaration were missing from
> >my previous code
> >(They were global) and I forgot to suppress some lines concerning a
> >ProgressBar that I
> >pass to the function in its "real version" I've done the changes and the
> new
> >version is attached
> >to this mail.



> >> I couldn't help but notice the 'error' in the declarations:

> >> >    Dim newField, oldField As Field

> >> That should be

> >> Dim newField as Field, oldField as Field
> >> or
> >> Dim newField as Field
> >> Dim oldField as Field
> >> or even
> >> Dim newField as Field: Dim oldField as Field

> >> Even without option explicit the code given here
> >> doesn't run, so I haven't checked any further.

> >> (David L Graham)


> >> >Hi, I've tested this code (with slight modification for the VB example)
> >> >under Acces 97 and VB 6.0 and it frozes both access and VB.

> >> >The function just browses two databases schema looking for discrepancies
> >in
> >> >tabledef or Fields (of a given tabledef)or properties(of a given field).
> >> >So it does a very extensive search but It does not froze while browsing
> >all
> >> >the elements in the
> >> >various collections but only when I close the databases by:

> >> >"Set newBDD = Nothing
> >> >  Set remoteBDD = Nothing"

> >> >If ANYBODY has ANY insight on this problem or knows where I might get
> >some
> >> >PLEASE TELL ME I'm stuck in my project because of that and I'm desperate
> >> for
> >> >solution, I've checked Microsoft Knowledge base and have not found any
> >> thing
> >> >related
> >> >to this problem PLEASE HELP ME !!!!!!!!!!!

> >> >If you want to try this function just
> >> >1. create a new database (test0) put the function code in a module.
> >> >2. make a copy of one of your existing database (test1 -> test2)
> >> >3. set a breakpoint in the line before Set newBDD = Nothing
> >> >4. Call the function with the name of the 2 databases from step 2
> >> >i.e.
> >findDiscrepancies("c:\mydatabase\test1.mdb","c:\mydatabase\test2.mdb")
> >> >5. After the execution stops at the breakpoint look at your watch and
> hit
> >> F5
> >> >with a database having 40 tables with around 50 fields per tables it
> >takes
> >> >around 6 minutes
> >> >on a Pentium mmx 200 for Access to regain control, around the same under
> >> >VB6.

> >> >THANKS IN ADVANCE TO ANYONE THAT GIVES ME A HAND...

> >> >Public Function findDiscrepancies(newFileName As String, fileName As
> >> String)
> >> >As Variant
> >> >    Dim newField, oldField As Field
> >> >    Dim wrkJet As Workspace
> >> >    Dim isField, isProp As Boolean
> >> >    Dim myProp As Property
> >> >    Set newBDD = DBEngine(0).OpenDatabase(newFileName, , True)
> >> >    Set remoteBDD = DBEngine(0).OpenDatabase(fileName, , True)
> >> >    '1- Find the new tables
> >> >    Dim newTabledef, oldTabledef As TableDef
> >> >    Dim newTabledefs, oldTabledefs As TableDefs
> >> >    Dim aName, sSourceTable As String
> >> >    Dim newTables() As String
> >> >    Dim fieldDiscrepancies() As String
> >> >    On Error GoTo errorHandler
> >> >    Dim i, j As Integer
> >> >    i = 0
> >> >    j = 0
> >> >    isField = False
> >> >    Set oldTabledefs = remoteBDD.TableDefs
> >> >    ProgressBar.Min = 0
> >> >    ProgressBar.Max = newBDD.TableDefs.Count
> >> >    ProgressBar.Visible = True
> >> >    For Each newTabledef In newBDD.TableDefs
> >> >        ProgressBar.Value = ProgressBar.Value + 1
> >> >        If ((newTabledef.Attributes And DB_SYSTEMOBJECT) = 0) Then
> >> >            Set oldTabledef = oldTabledefs(newTabledef.Name)
> >> >            '2- if no error at this point the table exist look for new
> >> >fields
> >> >            For Each newField In newTabledef.Fields
> >> >                isField = True
> >> >                Set oldField = oldTabledef(newField.Name)
> >> >                '3- if no error at this point check for the fields
> >> >properties
> >> >                For Each myProp In newField.Properties
> >> >                    isProp = True
> >> >                    If (oldField.Properties(myProp.Name) <> myProp And
> >> >myProp.Name <> "OrdinalPosition" _
> >> >                    And myProp.Name <> "ColumnOrder")
> >> >                     'Msgbox "some bull"
> >> >                    End If
> >> >continue_property:
> >> >                Next
> >> >                isProp = False
> >> >continue_field:
> >> >            Next
> >> >            isField = False
> >> >continue:
> >> >        End If
> >> >    Next
> >> >    Set newBDD = Nothing
> >> >    Set remoteBDD = Nothing
> >> >    findDiscrepancies = newTables
> >> >    Exit Function
> >> >errorHandler:
> >> >       If (isProp = True) Then
> >> >           Resume continue_property
> >> >       End If
> >> >    If isField Then
> >> >        ReDim Preserve fieldDiscrepancies(j)
> >> >        fieldDiscrepancies(i) = newField.Name + vbCrLf +
> newTabledef.Name
> >> >        j = j + 1
> >> >        'MsgBox "New field " + vbCrLf + newField.Name + vbCrLf + "in
> >Table
> >> "
> >> >+ vbCrLf + newTabledef.Name
> >> >        Resume continue_field
> >> >    Else
> >> >        ReDim Preserve newTables(i)
> >> >        newTables(i) = newTabledef.Name
> >> >        i = i + 1
> >> >        Resume continue 'No need to go through the fields of the new
> >> >table...
> >> >    End If
> >> >End Function

--
Marc(-Andre) Fontaine   (<>R|8/\/_|/\\//\ _|E|>|
Duke Energy Tower  5444  Westheimer
Suite 1000 Houston Texas 77056   Work: 713-350-4461 Home: 713-784-1180


Thu, 18 Jul 2002 03:00:00 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. running Access97 reports from my VB6 frontend

2. ADO VB6 Access97 Unable to run Access Query -2147217900

3. ADO VB6 Access97 Unable to run Access Query -2147217900

4. ADO VB6 Access97 Unable to run Access Query -2147217900

5. Access97 won't close after running DAO code

6. Access97 VBA code to VB6

7. VB6/Access97 Bill of Materials code request

8. Access97 Code to VB6

9. running code from Access that runs code in an excel spreadsheet

10. Running VBA code in EXcel from VB6 using EXcel Objects

11. Code works when run in VB6, gets error when compiled (Browse for Outlook Folder)

12. Word freezes trying to run macro or open vba using Alt-F11

 

 
Powered by phpBB® Forum Software