References Architecture

[arichins]

I'm looking for info on how where VBA references are recorded. Is it on the local machine, or are they associated with the Access file itself?

I have an Access data project which is shared by 32 bit and 64 bit computers. Our IT department keeps pushing out windows patches which disable an active X control that my ADP project uses. As a work around, we built scripts which re-register the DLL, one version for 32-bit (referencing system32), and one for 64-bit (referencing syswow64). But occasionally it will break on some 32-bit machines, and I find that the reference to that dll is pointed to the non-existant syswow64 folder.


At first I thought they must be running the 64bit script against some of the 32 bit machines. It seems that these references are stored on the local PC, because on most of the machines, the control is registered to the appropriate place. But on the other hand I've never had to set this up on an individual basis, so I'm wondering if something else could be causing the problem?

 

[boblarson]

References are set and kept inside the Access file itself. But, if the object library is not available (i.e. not installed on the machine) it will generate an error and show MISSING next to the name.

Do you have the frontend file as a copy on each person's machine? You aren't running it from a central location are you?

 

[gemma-the-husky]

i don't have long, but here's part of some code I use that checks references. you can repair them in code, but not with a mde/accde.

i found this on the web, so you should be able to find code to fix broken references as well.

Function brokenrefs() As Long
Dim brok As Long
Dim refcount As Long
Dim x As Long
Dim brefs As Long
Dim refstrg As String
Dim missing As String
    brok = 0
    missing = ""
 
    refcount = Access.References.Count
    For x = refcount To 1 Step -1
        'MsgBox ("Checking: " & Access.References(x).Name)
        On Error GoTo broken
        If Access.References(x).IsBroken Then
            brok = brok + 1
            missing = missing & Access.References(x).Name & vbCrLf
        End If
 
nextref:
    Next
   If brok > 0 Then
    'call function to list missing refs
        refstrg = missingrefs
        If brefs = 1 Then
            Call MsgBox("Sorry: " & " your app name"  & " cannot start. There is " & brefs & " broken reference. Please repair it and try again. " & vbCrLf & vbCrLf & _
                "Missing/Broken Reference: " & vbCrLf & vbCrLf & refstrg)
        Else
            Call MsgBox("Sorry: " & "your app name" & " cannot start. There are " & brefs & " broken references. Please repair them and try again. " & vbCrLf & vbCrLf & _
                "Missing/Broken References: " & vbCrLf & vbCrLf & refstrg)
        End If
    End If
    brokenrefs = brok
    Exit Function
 
broken:
    brok = brok + 1
    Resume nextref
End Function

 

[arichins]

References are set and kept inside the Access file itself. But, if the object library is not available (i.e. not installed on the machine) it will generate an error and show MISSING next to the name.

Do you have the frontend file as a copy on each person's machine? You aren't running it from a central location are you?

They're sharing a front end file. It seems like the references would be stored with the file, but there may be more to it, because when I log in to a 64 bit machine, the reference is to syswow64, when I log into a 32 bit machine, it's referenced to the system32 folder. It just gets messed up every once in awhile.

 

[boblarson]

They're sharing a front end file.

That is your problem right there. STOP DOING THAT!!!!

Each user should be running their OWN copy of the frontend. It should not be shared. And even more so when you have different things like the 32bit vs. 64 bit going on.

EDIT: Read this to understand more of why.
http://www.kallal.ca/Articles/split/index.htm

 

[arichins]

That is your problem right there. STOP DOING THAT!!!!

Each user should be running their OWN copy of the frontend. It should not be shared. And even more so when you have different things like the 32bit vs. 64 bit going on.

EDIT: Read this to understand more of why.
http://www.kallal.ca/Articles/split/index.htm

The database is split, by nature of the ADP architecture, and I have development copy, and can export updates to a read-only copy that everyone else uses. It's worked great for years until this little bug.
What other conflicts might arise from having them share the same front end file if it's read only? It seems that Access would be designed to handle that.

It's worked pretty good until this little bug. It's also strange that in most cases, the reference is right according to the type of machine its on. But thanks for your suggestion, it's something to keep in mind.

 

[boblarson]

It's worked pretty good until this little bug.

It isn't a bug. It is you using Access in a way that it is not able to handle.

It's also strange that in most cases, the reference is right according to the type of machine its on. But thanks for your suggestion, it's something to keep in mind.

It's more than something to keep in mind. If you would care to read the article I gave you the link to you will find the explanation of why it is IMPERATIVE that you do it the way I have said. By the way, you will have a hard time to try to find any Microsoft MVP's or anyone CURRENTLY on the actual Access development team at Microsoft who will dispute what I've said.

What you have is like playing Russian Roulette for years and then all of a sudden the bullet hits you and you wonder why as it was going along quite well over the years. It is just plain LUCK that you have not had issues before.

So, if you care to ask around (including on the Microsoft Answers forums) and such - ask the question Do I Need To Give Users Their Own Copy Of The Front End and you will get the same answer. But, ignore me at your own risk. It is what it is and the article I pointed you to goes into why it is what it is. Here's an excerpt if you can't be bothered to read it in full:

There are good many reasons as to why we install the software part on EACH workstation. A few reasons are:

• <LI class=MsoNormal>The software now does not have to travel across the network each time it is used. It would be a waste of network resources if applications such as word etc. had to be loaded across the network and use up precious network bandwidth. Remember, networks are MUCH slower then your hard disk to load files. Remember, the network is a shared resource. If you have two users on the network pulling data, then you have 1/2 the amount of resources available. You must treat your network with the utmost respect, as it is a delicate and precious (limited) resource. When you have 4 people on the same network, you have 1/4 the amount of performance. You MUST thus be very careful. Imagine how slow things would be if all users loaded their software from the network each time?
  <LI class=MsoNormal>If one user makes a change to their printer setup or just a general change to their settings, they have their OWN copy of the application to play with, and NOT interfere with other users on the network. Can you imagine two users fighting over how their excel sheet should be laid out at the same time!!
  
• If a user's software crashes, then no other users are affected. Can you imagine if word were to freeze up, or lock on your computer and everyone else in the building also experienced a freeze up or lock up. Again, it makes sense to install the application part on each pc as to ISOLATE individual’s problems that might occur in the application. So, installing software on each PC is a MUST DO from a reliability point of view. You don't want to allow ONE user's problem to become the problem of everyone else. This is again why we split a database in ms-access in multi-user mode (otherwise you will get NO reliability).

I could add MANY more reasons to the above list, but suffice to say that it makes a lot of sense to install the software on each computer. I don’t think there are too many people that have trouble understanding the above concepts.

 

[boblarson]

And one more thing regarding the references.

If you are sharing the frontend file, then the first person who opens that file sets the references. Then if someone else opens it, and has a different bit dll they are using, the file is only open once but each computer is fighting each other for which dll to use as it is set in the references.

So, it can lead to errors for users at the best and corruption of the file on the other end.

 

[arichins]

Not sure if pushing the copy out would solve this problem. The file path to the active x control isn't stored with the adp file. I can see that clearly because in all but two users (so far), the path to the activex control is appropriate to the type of OS they are running (syswow64 vs system32). I can download a copy of the file, open it on my machine and the reference is to syswow64, then move it to a 32 bit machine and the reference says system32. I suspect this is occuring when I push out a form from my 64bit machine to the users adp file. But why it affects only some users is a mystery. So if that's the case, perhaps I should try replacing the whole file rather than doing an export would work.

It's not right to say that Access was not designed to be shared in a network folder, MS lists it as an option. There are just positives and negatives to it, like with anything.

I didn't see anything compelling in that article for reasons to have the front end on each user's machine. In our environment, sure the front end has to "travel across the network, wasting precious resources"...ok, a little over-dramatic. I don't think my 15 mb front end is going to clog our network. And we've never had a problem with one sue crashing and affecting others. Maybe that would be a problem with an unsplit DB, but we're using adp as a front end and SQL Server as a back end.

 

[boblarson]

So, it is your choice. I told you what is wrong. You came here and asked what was wrong. I told you what was wrong but you do not believe me. I do have 15 years of Access development experience, have been on these forums for over 11 years and have over 30,000 posts and have the highest "reputation" point total on this site (next to the artificially inflated values that Jon, the owner has). I have been a Microsoft Access MVP for 3 years. I have received the Microsoft Community Contributor award and, I have had FACE TO FACE contact with the Access development team at Microsoft for 3 years. I am a professional Access developer. It's what I do for a living.

So, while I have been known to be wrong on occasion, I do know what I am talking about in this area and have given you your answer. If you choose to implement it, you will see your problem disappear and you may even find some other benefits.

But, you do have the choice to ignore what I have said. And I am going to end this conversation at this point because there is nothing more I can add and it isn't worth my time to keep arguing the point.

Good day to you.

 

[arichins]

So, it is your choice. I told you what is wrong. You came here and asked what was wrong. I told you what was wrong but you do not believe me. I do have 15 years of Access development experience, have been on these forums for over 11 years and have over 30,000 posts and have the highest "reputation" point total on this site (next to the artificially inflated values that Jon, the owner has). I have been a Microsoft Access MVP for 3 years. I have received the Microsoft Community Contributor award and, I have had FACE TO FACE contact with the Access development team at Microsoft for 3 years. I am a professional Access developer. It's what I do for a living.

So, while I have been known to be wrong on occasion, I do know what I am talking about in this area and have given you your answer. If you choose to implement it, you will see your problem disappear and you may even find some other benefits.

But, you do have the choice to ignore what I have said. And I am going to end this conversation at this point because there is nothing more I can add and it isn't worth my time to keep arguing the point.

Good day to you.

I believe in your skills Bob, but just haven't seen any first hand problems with users sharing a front end, which we've been using for 7 years. We probably only have 15 or so people in there at any given time, so maybe it would be a bigger problem with more users.

Also, even if everyone had their own local front end file, I'd still need to update their copy as a whole file replacment, or at the individual form level. Which is the same function I'd be doing to a single file on the network. So distributing a front end, or storing it on the network share is another matter here.

 

[John Big Booty]

Dude at the moment you are riding in a truck full of nitroglycerin down a rough road. Just because it's not blown in the past doesn't mean it wont in the future. By ignoring Bob's advise the further down the road you get before it does blow, the bigger the mess you will be cleaning up when it does go.

Good luck on the suicide run

 

[Catalina]

Why does someone ask for help if he just chooses to argue over it?
Don't question Bob's knowledge and experience, he knows his business.

Catalina

 

[John Big Booty]

Why does someone ask for help if he just chooses to argue over it?
Don't question Bob's knowledge and experience, he knows his business.

Catalina

Many people feel that because they've been cruising along with no incident that there is no imperative for them to change they way they do things. Then when the inevitable crunch comes, they start crying; "But why did no one worn me"

 

[Catalina]

I guess it's like driving for years without using the seat belt.
And then one day you hit a tree and you crash your head through the windshield.

Should have listened to the people who warned you and strongly advised you to use it.

Catalina

 

[arichins]

Why does someone ask for help if he just chooses to argue over it?
Don't question Bob's knowledge and experience, he knows his business.

Catalina

If you read the thread, I didn't ask for advice on whether or not my front end should be on a network folder or each uses machine. That off-shoot was totally irrelevant, and would not fix this problem.

This problem was caused in the first place by a microsoft security updating disabling an active x control on the local machine. Exporting forms from a 64-bit machine to 32-bit re-introduced the problem for some users, but again, wether or not that were done to a file on the users workstation or a network share is not relevant.

So far, I've not seen any compelling reason why I should be afraid to continue using it on a shared folder. Sorry, but whining like a baby about how much of an Access God you are doesn't sway me.

And thank you for your concern Big John Booty, but if this ever does become a problem, I'm sure I can mangage to distribute the file out without anyone dying.

 

[gemma-the-husky]

i am not saying this is the trouble - but if it is system architecture that is causing an intermittent problem for some users - then you are never going to be able to solve it, with a shared front end.

by the same token, if you ever go to using a mde/accde instead of an "open" database - you will also find it is not possible to repair the references at all.

good luck

 

[Pat Hartman]

The library references are stored in the FE. Access will "promote" a reference to a newer version but will not "demote" a reference to an older version. When the FE is originally placed on the server, it goes out with the references at the lowest level (or at least it should). In the STANDARD (whether you like it or not) setup, each user has his own copy of the FE so if he has newer libraries and access promotes the references, it only happens in HIS copy and so doesn't affect anyone else. With your setup, you have a mixed environment and so when a person with new libraries opens the FE, the references get promoted and when he closes the database, they get saved breaking the FE for people with older reference files.

I'm going to guess that the problem is hard to track down because, the actual FE file may not get updated until the last person logs out and so it may be that person's references that get saved for next time.

You have been warned. It is poor practice to physically share the same FE when all OS's and Access versions and ActiveX versions are identical. It is suicide when they are different.

 

[Vassago]

Actually, it's very likely that it could be causing your problem. You're having multiple people use the same copy of a front end database at the same time from different versions of Windows.

 

[boblarson]

Looks like it is pointless to continue this thread as the OP's last visit was the date of the last post. One could hope that they may return and have figured out that we weren't blowing smoke out of our butts, but it is highly likely that they will not return to this forum.