Evaluating References in Code [Long]

[jeremygetsmail]

I've got an adp (Metrix.adp) with a reference to another adp
(InteractSQL.adp). InteractSQL sits on a server, and is refered to by
all of the clients (Metrix), which sit on the client machines (There's
also a SQL Server that sits on the server, but that's besides the point
here.). Both adp files have references to ADOX. I've got to check
Metrix has an older version of ADOX, and react appropriately. I've
written code to do this, and it looks like it should work. But it
doesn't and I can't figure out why.

I would love any thoughts on what might be going wrong with my code. I
would also love any thoughts on other ways that I could go about
assessing what version of ADOX is being refered to in an adp to which
I have a reference set. Another way to go about this would be to figure
out what version of MDAC is on another computer on the network.

The code (below) runs in Metrix, and walks the collection of references
in that file. When it encounters the ADOX reference, it just gathers
the version. When it encounters the InteractSQL reference, it then
walks the reference collection of that object, again gathering the
version number when it finds the ADOX reference.

I've got two machines set up. The client machine has a reference to
ADOX 2.7 in the Metrix file. The InteractSQL file on the server has a
reference to ADOX 2.8 (a scenario we encounter a lot at client sites).
But the code reports back that they are both 2.7. When I go back to
InteractSQL.adp, though, it's reference is clearly still set to 2.8.

Thanks much for any help on this.

Jeremy
--
Jeremy Wallace
Fund for the City of New York

Function fnInteractReportRefsValid() As Boolean
On Error GoTo Error
Dim refParent As Access.Reference
Dim appChildDB As Application
Dim refChild As Access.Reference
Dim lngParentVersion As Long
Dim lngChildVersion As Long
Dim strInteractFile As String

'Can't know how the references will be named, so get rid of the
extension, if it's there
strInteractFile = Replace(INTERACTFILE, ".adp", "")

'Walk the collection of references set in Metrix.adp
For Each refParent In References
If refParent.Name = "ADOX" Then
'Gather the local mdac version, which we'll later compare to
the one in interact.
lngParentVersion = CLng(refParent.Major & "." &
refParent.Minor)
ElseIf refParent.Name Like "*" & strInteractFile & "*" Then
If refParent.IsBroken = True Then
'Report back if it's broken.
fnInteractReportRefsValid = False
Else
'Gather the version of mdac refered to in InteractSQL.adp.
Set appChildDB = New Access.Application
Call appChildDB.OpenAccessProject(refParent.FullPath)
For Each refChild In appChildDB.References
If refChild.Name = "ADOX" Then
lngChildVersion = CLng(refChild.Major & "." &
refChild.Minor)
End If
Next refChild
appChildDB.CloseCurrentDatabase
End If
End If
Next refParent

If lngChildVersion > lngParentVersion Then
'Do some stuff
End If

ExitPoint:
On Error Resume Next
'Clean-up code

Exit Function
Error:
'Error handling
End Function

[TC]

Not to answer your question, but, that code will never handle broken
references.

If /any/ refrence is broken, /all/ your VBA code will start to
malfunction. Your code will die, before you even get to check IsBroken.

To avoid this problem, you have to qualify every item that you use in
you code, with the name of the library in which that item occurs. Eg.
you could not say: msgbox "this" & vbcrlf & "that". It would have to be
Access.msgbox "this" & VBA.vbcrlf & "that". (I haven't checked those
library names, they might be wrong, but hopefully you get my point.)

There are other requirments that you have to meet also. It's difficult
- but doable. Google the Access groups for the term "disambiguate" (and
variants) for more information.

HTH,
TC

[jeremygetsmail]

TC,

Yeah, that's true. This is code that I inherited. At some point (though
not before the next release) I'll look into whether or not we should
just pull the IsBroken bit from this code. At this point I can't see
that it hurts much. It's possible that we'll fully disambiguate
everything in the code, but it's thousands of lines of code, so I'm not
holding my breath for that one.

In any case, if any one else has any thoughts about what to do to
return the correct version number from the
reference-within-a-reference, I'd love to hear them.

Thanks much.

Jeremy
--
Jeremy Wallace
Fund for the City of New York

[TC]

Well, I don't have any experience with Access Data Projects (or
whatever they're called) - but this looks wrong to me:

Set appChildDB = New Access.Application
Call appChildDB.OpenAccessProject(refParent.FullPath)
For Each refChild In appChildDB.References

If the references are a property of the access application object, you
would not need to open the access project. Conversly, if the references
are a property of the opened access project, you should save the object
reference that is returned by the OpenAccessProject method, and get at
the references though that object reference. IOW your code, as
currently written, is probably looking at the default references
collection that exists in Access /before/ any partiular database or
access project has been opened.

HTH,
TC

[Lyle Fairfield]

je************@gmail.com wrote:

I would love any thoughts on what might be going wrong with my code. I
would also love any thoughts on other ways that I could go about
assessing what version of ADOX is being refered to in an adp to which
I have a reference set.

If you don't use ADOX and don't set a reference to it you can pretty
much guarantee that the version will be 0000.000.

I don't know of anything that ADOX is needed for with an ADP. (MS-SQL
is extremely powerful!). Do you know of something that requires ADOX?
Could you tell us what it is?

[david epsom dot com dot au]

Looks ok to me:

create new application object

load file into new application object

look at references in new application object.

(david)
"TC" <aa**********@yahoo.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

Well, I don't have any experience with Access Data Projects (or
whatever they're called) - but this looks wrong to me:

Set appChildDB = New Access.Application
Call appChildDB.OpenAccessProject(refParent.FullPath)
For Each refChild In appChildDB.References

If the references are a property of the access application object, you
would not need to open the access project. Conversly, if the references
are a property of the opened access project, you should save the object
reference that is returned by the OpenAccessProject method, and get at
the references though that object reference. IOW your code, as
currently written, is probably looking at the default references
collection that exists in Access /before/ any partiular database or
access project has been opened.

HTH,
TC

[TC]

je************@gmail.com wrote:

It's possible that we'll fully disambiguate
everything in the code, but it's thousands of lines of code, so I'm not
holding my breath for that one.

You only need to disambiguate the code that checks for broken
references. Typically, this is only a few dozen lines of code. You put
this in a seperate module. The rest of the code (ie. the main code of
the application) is not affected at all.

HTH,
TC

[TC]

And that is precisely his error, imho! He is checking the references
which belong to the application object. He should be checking the
references which belong to the access project that he opened within the
application object. Those are not the same thing. He is checking the
wrong object, imho.

Cheers,
TC

[david epsom dot com dot au]

no, I look at App.References in my code, not
App.CurrentProject.references.

I don't know why that is, but there you are.

If MS had implemented App.CurrentProject.References,
then they probably could have implemented
App.CurrentProject.References(n).References,

Which would have been a useful feature.

Because the two logical helpful things they could
have done are
a) expose all the loaded references in app.references
or
b) expose the indirect references through
currentproject.references(n).references

But references of your references aren't exposed
by the application object by any method - you only
get shown the direct references, and to check the indirect
references you need to create other application objects.

(david)

"TC" <aa**********@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

And that is precisely his error, imho! He is checking the references
which belong to the application object. He should be checking the
references which belong to the access project that he opened within the
application object. Those are not the same thing. He is checking the
wrong object, imho.

Cheers,
TC

[TC]

Ok, let me check I understand what you're saying. You're saying that
the References collection of the Application object, does return the
references of the specific database or access project that is currently
open in that object? So, in the following sequence of actions, steps 3
and 6 would return different information?

1. automate a new instance of Access;
2. open a database or access project in that instance;
3. print all the details of the Application object, Refrences
collecion;
4. close that database or access project;
5. open a different one which definitely does have different references
to the previous one;
6. repeat step 3.

Cheers,
TC

[Terry Kreft]

Yes.

(I'm trying hard not to say "of course")

--
Terry Kreft

"TC" <aa**********@yahoo.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

Ok, let me check I understand what you're saying. You're saying that
the References collection of the Application object, does return the
references of the specific database or access project that is currently
open in that object? So, in the following sequence of actions, steps 3
and 6 would return different information?

1. automate a new instance of Access;
2. open a database or access project in that instance;
3. print all the details of the Application object, Refrences
collecion;
4. close that database or access project;
5. open a different one which definitely does have different references
to the previous one;
6. repeat step 3.

Cheers,
TC

[jeremygetsmail]

Hmm. Terry, isn't that what my code does? It's definitely returning the
same information in those steps, though I was hoping it would return
different information...

Jeremy
--
Jeremy Wallace
Fund for the City of New York

[jeremygetsmail]

This is an excellent point, Lyle. I'm looking into what code we use
that has to be done with ADOX. So far it's looking a little thin. If we
end up being able to drop that reference, I'll owe you a cupcake. A
really big one.

Thanks much.

Jeremy
--
Jeremy Wallace
Fund for the City of New York

PS: Should I make that a redhead?

[Lyle Fairfield]

If I were rude I'd say the end result would be the same, but I'm not
rude. My evil twin, Kyle, might not be so circumspect!
Moving right along, I believe somewhere in the thread you indicated
that it might help to be able to ascertain the version of ADOX
available on the machine.
As you have specified an ADP, I beleive we can be assured of Access >=
2000 and an ADODB reference. I believe that the ADOX dll will reside in
the same directory as the msado dll. So, based on those assumptions I
offer this, which is a very minor adaption of some other code that I
run, [the one to use is ADOXVersion()]:

Option Explicit

Private Declare Sub CopyMemory _
Lib "kernel32" Alias "RtlMoveMemory" _
(Destination As Any, Source As Any, ByVal Length As Long)

Private Declare Function GetFileVersionInfo _
Lib "version.dll" Alias "GetFileVersionInfoA" _
(ByVal lptstrFilename As String, ByVal DWHandle As Long, _
ByVal dwLen As Long, lpData As Any) As Long

Private Declare Function GetFileVersionInfoSize _
Lib "version.dll" Alias "GetFileVersionInfoSizeA" _
(ByVal lptstrFilename As String, lpdwHandle As Long) As Long

Private Declare Function VerQueryValue _
Lib "version.dll" Alias "VerQueryValueA" _
(pBlock As Any, ByVal lpSubBlock As String, _
LPLPBuffer As Any, PULen As Long) As Long

Public Function VersionNumber(ByVal FullPath As String) As String
Dim Buffer() As Byte
Dim DWHandle As Long
Dim FileVersionInfoSize As Long
Dim LPLPBuffer As Long
Dim SubVersion As Integer
Dim Version As Integer

FileVersionInfoSize = GetFileVersionInfoSize(FullPath, DWHandle)
If FileVersionInfoSize <> 0 Then
ReDim Buffer(FileVersionInfoSize)
GetFileVersionInfo FullPath, 0, FileVersionInfoSize, Buffer(0)
VerQueryValue Buffer(0), "\", LPLPBuffer, FileVersionInfoSize
ReDim Buffer(51)
CopyMemory Buffer(0), ByVal LPLPBuffer, 52
CopyMemory Version, Buffer(10), 2
CopyMemory SubVersion, Buffer(14), 2
VersionNumber = "Version " & Version & "." & SubVersion
Else
VersionNumber = "Could Not Retrieve Version Information for " _
& FullPath
End If
End Function

Public Function ADOXVersion() As String
Dim Path As String
Dim Drive As String
Dim Dir As String
Dim File As String
Dim Ext As String

With WizHook
.Key = 51488399
.SplitPath References("ADODB").FullPath, Drive, Dir, File, Ext
End With

ADOXVersion = VersionNumber(Drive & Dir & "msadox.dll")
End Function

Notes:
The work of Wizhook could be done in any number of ways, but Wizhook
annoys David, so I use it whenever I can.
51488399 is the number of redheads who have turned me down. When it is
ANDed with the total number of redheads in the universe the result is
51488399.

[TC]

Why would you be saying "of course"?

What is obvious about the question at issue?

TC

[TC]

Why would you be saying "of course"?

What is obvious about the question at issue?

TC

[Terry Kreft]

Because anybody who had ever added references within Access must have
noticed that the list of references changes when you load a new database.

Empiricsm is all that is needed here not reasoniing.
--
Terry Kreft

"TC" <aa**********@yahoo.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Why would you be saying "of course"?

What is obvious about the question at issue?

TC

[TC]

Terry Kreft wrote:

Because anybody who had ever added references within Access must have
noticed that the list of references changes when you load a new database.
You think I don't know that? Pity it's nothing to do with the question
at hand - namely, WHAT OBJECT you access the current database's or
access project's references through.

Empiricsm is all that is needed here not reasoniing.

I understand your position. You have problems with the latter. I
recomment you stay with the former.
TC

[Terry Kreft]

If I thought you didn't know it then I wouldn't have said "Of course" in my
first post, now I'm starting to think I was being overgenerous.

You still haven't worked this out ?

Hmm, as I have seen no evidence of either empiriscm or reaoning in your
posts I'll take your last comment as the usual ravings of the intellectually
challenged.
--
Terry Kreft

"TC" <aa**********@yahoo.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

Terry Kreft wrote:

Because anybody who had ever added references within Access must have
noticed that the list of references changes when you load a new database.

You think I don't know that? Pity it's nothing to do with the question
at hand - namely, WHAT OBJECT you access the current database's or
access project's references through.

Empiricsm is all that is needed here not reasoniing.

I understand your position. You have problems with the latter. I
recomment you stay with the former.
TC

[TC]

Terry, see if you can follow along. Really try to concentrate.

- Person 'A' is involved in a technical discussion with person 'B'.
- Person 'A' asks person 'B' to clarify their position.
- Some random wanker 'C' drops into the thread, for the first time, to
tell person 'A' that the issue is obvious ("I'm trying hard not so say
"of course").
- Person 'A' asks person 'C' - twice - to justify their statement.
- Person 'C' replies with a fact that is, in truth, completely
irrelevant the issue at hand.
- Person 'A' restates the technical issue that the thread is about.
- Person 'C' replies with random invective.

See if you can work out which of those three people, is you.

TC

[Tim Marshall]

TC wrote:

See if you can work out which of those three people, is you.

Christmas is a very stressful time of year...

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[TC]

Ain't that the truth!

TC

[Terry Kreft]

Well, by my counting of responders in this branch of the thread you've just
called David Epsom a wanker, which in my experience is not true, I therefore
think you owe him an apology.

BTW my answer, if you had bothered to check, was to the question of whether
the references changed when the loaded database changed, but of course
you're too busy getting wound up about an irrelevant aside, which I have
already justified, to be concerned with irrelevancies such as that.

Oh, and trimming your reply where you randomly attempted to insult me
doesn't make it dissapear.

To be honest at the moment you needn't worry about me interrupting one of
your "technical" discussions again because your lack of rational thought and
apparent lack of technical knowledge makes it seem to me that it wouldn't be
a worthwhile experience for either of us.

<PLONK>

--
Terry Kreft

"TC" <aa**********@yahoo.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

Terry, see if you can follow along. Really try to concentrate.

- Person 'A' is involved in a technical discussion with person 'B'.
- Person 'A' asks person 'B' to clarify their position.
- Some random wanker 'C' drops into the thread, for the first time, to
tell person 'A' that the issue is obvious ("I'm trying hard not so say
"of course").
- Person 'A' asks person 'C' - twice - to justify their statement.
- Person 'C' replies with a fact that is, in truth, completely
irrelevant the issue at hand.
- Person 'A' restates the technical issue that the thread is about.
- Person 'C' replies with random invective.

See if you can work out which of those three people, is you.

TC

[david epsom dot com dot au]

You've done that wrong:

5. open a different one which definitely does have different references
6. repeat step 3.
Should be
5. repeat step 1.

The behaviour is not the same, and if you wish to use code
like that (instead of the code in the original question),
you should make your own tests.

(david)
"TC" <aa**********@yahoo.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com... Ok, let me check I understand what you're saying. You're saying that
the References collection of the Application object, does return the
references of the specific database or access project that is currently
open in that object? So, in the following sequence of actions, steps 3
and 6 would return different information?

1. automate a new instance of Access;
2. open a database or access project in that instance;
3. print all the details of the Application object, Refrences
collecion;
4. close that database or access project;
5. open a different one which definitely does have different references
to the previous one;
6. repeat step 3.

Cheers,
TC

[TC]

Terry Kreft wrote:

Well, by my counting of responders in this branch of the thread you've just
called David Epsom a wanker, which in my experience is not true, I therefore
think you owe him an apology.
Good Lord man, * Y O U * are person (C) in that script! Can't you
read the words on the page?

BTW my answer, if you had bothered to check, was to the question of whether
the references changed when the loaded database changed
You did answer that, which was fine. I have no objection to that,
that's the reason why people ask questions. They hope to get answers.
It was obvious from my reply to David Epsom, that I was asking him to
confirm my understanding of what he said. You stepped in & did that for
him. No poblems up to that point.

but of course you're too busy getting wound up about an irrelevant aside
It was an aside which obviously offended me. You could easily have
neutralized that by a suitably worded reply. You chose not to bother. I
chose not to accept that.

<PLONK>

We're certainly getting nowhere with this, and I have much more
pressing things to do with my time. Please do no ever reply to any of
my posts again. I'll try hard to deal with the fearful ramifications of
that. I'll advise my clients that my level of expertise will plummet
alarmingly.

TC

[Larry Linson]

"TC" <aa**********@yahoo.com> wrote

<PLONK>
pressing things to do with my time. Please do no ever reply to any of
my posts again.

PLONK implies that he won't ever _see_ your posts again.
I'll try hard to deal with the fearful ramifications of
that. I'll advise my clients that my level of expertise
will plummet alarmingly.

I don't have any idea of your level of expertise, but I can assure you that
Terry Kreft's level of expertise is quite high. I wouldn't, even in a fit of
pique, cut myself off from his excellent knowledge of Access. On the other
hand, as _he_ has PLONKed _you_, you don't really seem to have much choice
in the matter. :-)

[TC]

Larry Linson wrote:

I don't have any idea of your level of expertise,

Well, I must have made at least 6000 posts at various times in the last
10 years, and I doubt that any more than 2 or 3 of those were
questions. All the rest were answers. So that should give you some
idea.

But I come here very rarely now. There are too many egos & posturing. I
enjoy a robust discussion of technical issues, but that seems very hard
in this group.

You won't see any of that behaviour in the MS beta newsgroups. Look
there for a good example of how it should go. There are robust
discussions of Office & Access 12 at present, but no-one is making
snide comments, or being disrespectful to others.

I post in those groups, with the same tone I have posted here. But I've
not got into any arguments there. Maybe that should tell you something.

Cheers,
TC

[jeremygetsmail]

Lyle,

One red-headed cupcake headed your way, Lyle. Turns out it was just one
function that was using ADOX, and it was quite easily rebuilt using ADO
and an SP. Thanks much.

Jeremy

[Lyle Fairfield]

Great; red-headed cupcakes will generally motivate me to do anything!

[david epsom dot com dot au]

I offer the following code because I continue to be
unconvinced of the need for something more complex:

Private Declare Function GetFileVersionInfo& Lib "Version" Alias
"GetFileVersionInfoA" (ByVal FileName$, ByVal lptr&, ByVal lSize&, lpvData
As Any)
Public Function gfn_GetVersion(FileName$)
'2003/02/26 dlg --minimalist version -- Right to attribution retained
Dim iBuf(0 To 99) As Integer

Call GetFileVersionInfo(FileName$, 0&, 200, iBuf(0))
gfn_GetVersion = iBuf(25) & "." & iBuf(24) & "." & iBuf(27) & "." &
iBuf(26) 'FILE VERSION

End Function
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

If I were rude I'd say the end result would be the same, but I'm not
rude. My evil twin, Kyle, might not be so circumspect!
Moving right along, I believe somewhere in the thread you indicated
that it might help to be able to ascertain the version of ADOX
available on the machine.
As you have specified an ADP, I beleive we can be assured of Access >=
2000 and an ADODB reference. I believe that the ADOX dll will reside in
the same directory as the msado dll. So, based on those assumptions I
offer this, which is a very minor adaption of some other code that I
run, [the one to use is ADOXVersion()]:

Option Explicit

Private Declare Sub CopyMemory _
Lib "kernel32" Alias "RtlMoveMemory" _
(Destination As Any, Source As Any, ByVal Length As Long)

Private Declare Function GetFileVersionInfo _
Lib "version.dll" Alias "GetFileVersionInfoA" _
(ByVal lptstrFilename As String, ByVal DWHandle As Long, _
ByVal dwLen As Long, lpData As Any) As Long

Private Declare Function GetFileVersionInfoSize _
Lib "version.dll" Alias "GetFileVersionInfoSizeA" _
(ByVal lptstrFilename As String, lpdwHandle As Long) As Long

Private Declare Function VerQueryValue _
Lib "version.dll" Alias "VerQueryValueA" _
(pBlock As Any, ByVal lpSubBlock As String, _
LPLPBuffer As Any, PULen As Long) As Long

Public Function VersionNumber(ByVal FullPath As String) As String
Dim Buffer() As Byte
Dim DWHandle As Long
Dim FileVersionInfoSize As Long
Dim LPLPBuffer As Long
Dim SubVersion As Integer
Dim Version As Integer

FileVersionInfoSize = GetFileVersionInfoSize(FullPath, DWHandle)
If FileVersionInfoSize <> 0 Then
ReDim Buffer(FileVersionInfoSize)
GetFileVersionInfo FullPath, 0, FileVersionInfoSize, Buffer(0)
VerQueryValue Buffer(0), "\", LPLPBuffer, FileVersionInfoSize
ReDim Buffer(51)
CopyMemory Buffer(0), ByVal LPLPBuffer, 52
CopyMemory Version, Buffer(10), 2
CopyMemory SubVersion, Buffer(14), 2
VersionNumber = "Version " & Version & "." & SubVersion
Else
VersionNumber = "Could Not Retrieve Version Information for " _
& FullPath
End If
End Function

Public Function ADOXVersion() As String
Dim Path As String
Dim Drive As String
Dim Dir As String
Dim File As String
Dim Ext As String

With WizHook
.Key = 51488399
.SplitPath References("ADODB").FullPath, Drive, Dir, File, Ext
End With

ADOXVersion = VersionNumber(Drive & Dir & "msadox.dll")
End Function

Notes:
The work of Wizhook could be done in any number of ways, but Wizhook
annoys David, so I use it whenever I can.
51488399 is the number of redheads who have turned me down. When it is
ANDed with the total number of redheads in the universe the result is
51488399.

[Lyle Fairfield]

david epsom dot com dot au wrote:

I offer the following code because I continue to be
unconvinced of the need for something more complex:

Private Declare Function GetFileVersionInfo& Lib "Version" Alias
"GetFileVersionInfoA" (ByVal FileName$, ByVal lptr&, ByVal lSize&, lpvData
As Any)
Public Function gfn_GetVersion(FileName$)
'2003/02/26 dlg --minimalist version -- Right to attribution retained
Dim iBuf(0 To 99) As Integer

Call GetFileVersionInfo(FileName$, 0&, 200, iBuf(0))
gfn_GetVersion = iBuf(25) & "." & iBuf(24) & "." & iBuf(27) & "." &
iBuf(26) 'FILE VERSION

End Function

Very nice!

--
Lyle Fairfield