comparing query properties in two databases

W

William Benson

Repeating a post submitted last night in the
microsoft.public.access.formscoding forum, because it got no responses
there. Thanks if someone can assist.

I wrote a procedure for comparing queries in 2 different databases (part of
a version control strategy) and *thought* it would be a slam dunk to test
the queries in DB1 for existence in DB2, and of those which existed, test
that they both had the same field names. Apparently the line below

For Each
F1 In Q1.Fields

is not working out. Any idea why? Thanks if so...



Sub CompareDB()
Dim WS As Workspace
Dim DB1 As Database
Dim DB2 As Database
Dim Q1 As QueryDef
Dim Q2 As QueryDef
Dim F1 As Field
Dim F2 As Field
Dim FldFound As Boolean
Dim QFound As Boolean
Dim Msg As String


' Create a new Microsoft Jet workspace.
Set WS = CreateWorkspace("NewJetWorkspace", "admin", "", dbUseJet)

Const DB1_Path As String = "c:\DB1.mdb"
Const DB2_Path As String = "c:\DB2.mdb"

Set DB1 = WS.OpenDatabase(DB1_Path)
Set DB2 = WS.OpenDatabase(DB2_Path)

For Each Q1 In DB1.QueryDefs
QFound = False
For Each Q2 In DB2.QueryDefs
If Q1.Name = Q2.Name Then
QFound = True
If Q2.Fields.Count <> Q1.Fields.Count Then
Msg = Msg & " | " & "FIELD COUNT differs"
End If
For Each F1 In Q1.Fields
FldFound = False
For Each F2 In Q2.Fields
If F1.Name = F2.Name Then
FldFound = True
Exit For
End If
Next F2
If Not FldFound Then
If InStr(Msg, " " & Q1.Name & " ") = 0 Then
Msg = Msg & " | " & Q1.Name & " "
End If
Msg = Msg & " | " & "FIELD " & F1.Name & " NOT FOUND"
End If
Next F1
Exit For
End If
Next Q2
If Not QFound Then
Msg = Msg & " | QUERY " & Q1.Name & " NOT FOUND"
End If
Next Q1
If Msg <> "" Then Msgbox Msg
End Sub
 
D

Dirk Goldgar

William Benson said:
Repeating a post submitted last night in the
microsoft.public.access.formscoding forum, because it got no responses
there. Thanks if someone can assist.
Click to expand...

You really ought to be prepared to wait as much as a couple of days for
a newsgroup response. Admittedly, most of the Access groups have lots
of people chomping at the bit to answer questions, so easy questions
tend to get answered quickly. You did post your question in the wrong
group (both times) -- the .modulesdaovba group, or the new
..modulescoding group, would have been more appropriate.
I wrote a procedure for comparing queries in 2 different databases
(part of a version control strategy) and *thought* it would be a slam
dunk to test the queries in DB1 for existence in DB2, and of those
which existed, test that they both had the same field names.
Apparently the line below


For Each F1 In Q1.Fields

is not working out. Any idea why? Thanks if so...
Click to expand...

It would have helped for you to say what you meant by "is not working
out". If you got an error, was it a compile-time error or a run-time
error? What was the error message? What was the error number?

I'm just guessing now, but I note that the Field object is the only one
of the object types you use that is defined in both the DAO and ADO
libraries. Maybe you have references set to both libraries, and Access
thinks you are declaring F1 and F2 as ADODB.Field objects, not DAO.Field
objects. If that's so, you'll get a type mismatch error at run time.
You can fix that by changing your declarations like so:

Dim F1 As DAO.Field
Dim F2 As DAO.Field

For future reference, the list of objects defined in both librariies is
as follows:

Connection
Error
Errors
Field
Fields
Parameter
Parameters
Property
Properties
Recordset

The following objects exist with the same names in the ADOX and DAO
models as well:

Group
Groups
Index
Indexes
Property
Properties
User
Users
 
W

William Benson

Will post in appropriate form next time, thanks for the tip about the other
forums!

"Not Working Out" means that no Fields returned and execution is jumping to
the Exit For when I use a line like
For Each F1 In Q1.Fields
I also found out that Q2.Fields.Count is always zero for all my queries.

I changed the declarations and still get Q2.Fields.Count and Q1.Fields.Count
are zero.

Dim Q1 As QueryDef
Dim Q2 As QueryDef
Dim F1 As DAO.Field
Dim F2 As DAO.Field


For Each Q1 In DB1.QueryDefs
QFound = False
For Each Q2 In DB2.QueryDefs
If Q1.Name = Q2.Name Then
QFound = True
If Q2.Fields.Count <> Q1.Fields.Count Then
Msg = Msg & " | " & "FIELD COUNT differs"
End If
For Each F1 In Q1.Fields
FldFound = False
For Each F2 In Q2.Fields
If F1.Name = F2.Name Then
FldFound = True
Exit For
End If
Next F2
If Not FldFound Then
If InStr(Msg, " " & Q1.Name & " ") = 0 Then
Msg = Msg & " | " & Q1.Name & " "
End If
Msg = Msg & " | " & "FIELD " & F1.Name & " NOT FOUND"
End If
Next F1
Exit For
End If
Next Q2
If Not QFound Then
Msg = Msg & " | QUERY " & Q1.Name & " NOT FOUND"
End If
Next Q1


1) I do have references to both ADO and DAO set. I understand that
 
D

Dirk Goldgar

William Benson said:
Will post in appropriate form next time, thanks for the tip about the
other forums!

"Not Working Out" means that no Fields returned and execution is
jumping to the Exit For when I use a line like
For Each F1 In Q1.Fields
I also found out that Q2.Fields.Count is always zero for all my
queries.
Click to expand...

For *all* your queries? That's odd. I just tried your code with a
couple of databases of my own, and it worked fine. Are these queries of
yours all action queries? I think only SELECT queries have any fields
in their Fields collections.
 
W

William Benson

You are right, the problem was I being fooled by action queries. Also, the
code is not very good since it is only a one-sided check. It tells me if
queries in DB1 got transferred to DB2, and whether field counts agree, and
whether all fields in the found DB1 queries have the same field names as
similar queries in DB2 ... but does not show queries in DB2 which are not in
DB1, but doesn't point out fields in DB2 queries which are missing or named
differently from their counterparts in DB1. The coding to fix these
omissions is fairly simplistic, I am just explaining the limitation of what
was already coded in case anyone thought it was complete -- it is not.

Thanks for solving my problem Dirk.

Sub CompareDB()
Dim ws As Workspace
Dim DB1 As Database
Dim DB2 As Database
Dim Q1 As QueryDef
Dim Q2 As QueryDef
Dim F1 As DAO.Field
Dim F2 As DAO.Field
Dim FldFound As Boolean
Dim QFound As Boolean
Dim Msg As String


' Create a new Microsoft Jet workspace.
Set ws = CreateWorkspace("NewJetWorkspace", "admin", "", dbUseJet)

Const DB1_Path As String = "c:\DB1.mdb"
Const DB2_Path As String = "c:\DB2.mdb"

Set DB1 = ws.OpenDatabase(DB1_Path)
Set DB2 = ws.OpenDatabase(DB2_Path)

For Each Q1 In DB1.QueryDefs
QFound = False
For Each Q2 In DB2.QueryDefs
If Q1.Name = Q2.Name Then
QFound = True
If Q2.Fields.Count <> Q1.Fields.Count Then
Msg = Msg & " QUERY " & Q1.Name & " | " & _
"FIELD COUNT differs" & vbCrLf
End If
For Each F1 In Q1.Fields
FldFound = False
For Each F2 In Q2.Fields
If F1.Name = F2.Name Then
FldFound = True
Exit For
End If
Next F2
If Not FldFound Then
Msg = Msg & " QUERY " & Q1.Name & " | FIELD " & _
F1.Name & " NOT FOUND" & vbCrLf
End If
Next F1
Exit For
End If
Next Q2
If Not QFound Then
Msg = Msg & " QUERY " & Q1.Name & " NOT FOUND" & vbCrLf
End If
Next Q1
If Msg <> "" Then MsgBox Msg
End Sub
 
D

Dirk Goldgar

William Benson said:
You are right, the problem was I being fooled by action queries.
Also, the code is not very good since it is only a one-sided check.
It tells me if queries in DB1 got transferred to DB2, and whether
field counts agree, and whether all fields in the found DB1 queries
have the same field names as similar queries in DB2 ... but does not
show queries in DB2 which are not in DB1, but doesn't point out
fields in DB2 queries which are missing or named differently from
their counterparts in DB1. The coding to fix these omissions is
fairly simplistic, I am just explaining the limitation of what was
already coded in case anyone thought it was complete -- it is not.
Click to expand...

Question: if these queries were supposed to have been transferred from
one database to the other, why not compare the querydefs' SQL
properties? That will catch action queries that differ. I'm not sure
whether you should expect the matching queries to have exactly identical
SQL or not, but you could at least eliminate queries that have identical
SQL from further examination.

Good luck!
 
W

William Benson

Another programmer was futzing with the queries, all I felt reasonably
secure about was testing query existence, column count and name.

Thanks for the suggestion Dirk.
 

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

Similar Threads

comparing Queries / Fields in 2 databases 2
Setting Focus after error 5
Urgent Query transferring files from mail to folder could someone help? 0
Need help in printing vba code 0
TRICKY ROADBLOCK: multi-user/linked table report issue 1
Insert Recepient's name in .oft template 0
same filter code for 2 subforms in 1 mainform - only 1 work 2
How to distribute/spread/map cost of project across different year 3

Top