Microsoft DAO and OS versions

[Rainbowhawk]

Hi Guys

I need a way for any database that opens to Load one of two reference library’s depending on the Operating System version

For various reasons we are forced to run Access 97 across all machines onsite, however due to recent hardware upgrades we have some machines running Windows 2000 and some running XP.

The machines running 2000 run the databases with no problems with

Microsoft DAO 3.51

The machines running XP run the databases with no problems with

Microsoft DAO 3.6

We have a large number of databases all hosted on a Network Drive and users access them as needed.

Going down the path of front ends for each machine is not practical, as the users can’t write information to the local C drive to update the version as amendments are made.

Yes I know we are antiquated, but unfortunately we have to work in the constraints provided.

All help appreciated.

Regards

Hawk

 

[Banana]

Actually, you don't worry about OS version as Office installs DAO based on Office's version, not OS version and has its own .dll stored in the Common Files.

But if you want to run an application on different versions of Access, the usual practice is to use late binding which absolves you from the obligation to reference the correct version.

If you google on "late binding VBA", I'm sure you'll find many hits providing example of how to use late binding in VBA.


BTW, are you letting users all share one copy of database on the shared folder?

 

[Rainbowhawk]

Thanks will have a look at “Late Binding VBA”

and Re

BTW, are you letting users all share one copy of database on the shared folder?

Unfortunately yes, not an ideal situation but we have numerous users updating the data, and as I mentioned the split FB/BE system just does not work with our set up, tried it once and all the users got access denied when they tried to copy the FE’s to their computers, did try putting the FE’s on the Network drive once as well and the network traffic went through the roof and we had all sorts of timeouts on other systems.

And just to make it really Fun, some machines have Office 2000, Some have 2003, all have Access 97.

 

[Banana]

I quite understand the problems with this. However, I'm afraid the setup will almost certainly lead to corruption. If I were in your shoes, I'd be rattling IT's cage to write up a batch script that has the necessary privilege to copy the front-end to users' machines or at least write a script where they can copy a throwaway copy from central location so it can go into say, /Temp folder and forget about it.

Good luck with late binding; if you're stuck on other points, do feel free to post back.

 

[DCrake]

I totally agree with Banana about FE BE setup. Bare in mind that if you have a shared front end on a central server and user log into this from their machine not only is there traffic with the data moving accross the wire but also the machine has to bring the application front end accross with it at the same time. Thus increasing the network payload x fold.

Again if this a mission critical system that is used by many users then the company policy should be amended to allow system updates as and when required to local users. Its a case of the tail wagging the dog. The IT department is not MD. If you explain the situation (in writing) and they reject your suggestion, then when, and I repeat when, the system falls over due to corruption then at least you have it in writing and can say "I told you so.."

Think of it as going to a football match, if everyone has the same entrance on their ticket there will be conjestion, fighting and squabbling on who gets in first. Give each person a different turnstile number then eveyone gets in without any issues and they can all see the match together.

Also it is hard to maintain traceability on who is logged in and from what machine.

David

 

[Rainbowhawk]

I fully agree with you about the FE/BE issue however, I work for a contactor working for a multinational, who’s IT is handled by their Parent company, The heavier used Databases do frequently fall over with corruption, we then blame them, they say “ok, can you fix it or do we need to restore the backup”,

Oh how nice it would be to work for a company who does not use 12 year old systems and they think they are modern. We even need a business case for having more 256mb of ram.

 

[Banana]

You have my sympathy.

I've had the fortune (?) of working with a similar client. The whole thing was basically held together with twine & toothpicks. No documentation, a litany of developers in and out, each having absolutely no idea what it did and coded defensively which made it even more obscure and unstable. Their stance? It's all our fault! We're the incompetents! Documentation? No, we don't need no steenkin' documentation!

<sigh> We've since parted the ways and all for the better, IMHO.


I would definitely take DCrake's advice to heart and faithfully document all of this so you have a case when you can talk to some decision maker and hopefully prod them in the right way.

 

[Fifty2One]

Best practice I have used is to taylor everything to the slowest and oldest hardware and software configuration in the network. This will build crash proofing as most upgraded hardware and OS will run the previous versions.
If you need to build a business case to have upgrades to machines to at least bring them to post Y2K levels then start the two week process of submitting the business case after the next IT crash. While they wait for the fixes they should be able to self justify upgrading to at least software containing "2003" in their version.
A company who will not invest in any sort of continuous improvement will end up an a not viable business venture. Freshen up your resume and ask your immediate supervisor if he will write a letter of recomendation or personal reference letter.

 

[boblarson]

Personally, I have never seen a situation where you can late bind DAO/Jet and if users are running the same program file from different Operating Systems you are bound to have more problems.

I would say, if they do not let you have split databases with each user having their own frontend copy, then you need to go through and document HOW MUCH MONEY is being lost due to downtime, corruption, etc. Money talks much more to managers who are trying to beat the bottom line, where logical arguments fail. Figure out how much (approximately) it is costing every time the app becomes unavailable to users. How many users times so many hours times an average hourly salary. If you do that and can document it well, there is a fighting chance you can get it changed. I was able to show one place I worked that putting the frontend on the user's machine (and using my auto update tool) would save them approximately $150,000 per year due to how many times they were suffering corruption and needing us to restore their database, the time necessary for re-entering data, and the downtime of 100 workers who couldn't do their job until the database was fixed. When they went to the split system, corruption went away and if anyone had a frontend issue it was ONE person and not ONE HUNDRED who couldn't do their work.

 

[Banana]

*headdesk*

What the heck I was thinking?!?

I'd like to plead temporary insanity for my post about late binding DAO....

 

[ChrisO]

Hawk.

When sharing a FE the way you suggest you may like to verify something…

If you have a report with a sub-report, get a user to open it and they should see the sub-report.
While that report is open, get another user to open the report; do they see the sub-report?

A few years ago I found that situation reproducible with A97 and it was due to sharing the FE.
If you can reproduce that situation you have very good leverage to get the FE’s distributed.

The DAO thing can be handled by not using a reference to DAO at all and allow each FE to use the Access version default.
(But depending on how much DAO you are using that could mean a bit of a re-write.)

 

[datAdrenaline]

You CAN late bind to DAO ... sad thing is, you need to know what version you want to late bind to.

'Prepare the JET db engine to do its thing.
Set jetEngine = CreateObject("DAO.DBEngine.36")
Set workSpace = jetEngine.CreateWorkspace("#DEFAULT#", "Admin", "")
Set db = workSpace.OpenDatabase("C:\FullPathAndNameOfDb")

... Or ...

'Prepare the JET db engine to do its thing.
Set jetEngine = CreateObject("DAO.DBEngine.35")
Set workSpace = jetEngine.CreateWorkspace("#DEFAULT#", "Admin", "")
Set db = workSpace.OpenDatabase("C:\FullPathAndNameOfDb")

---- You can dynamically change your Late Binding with something like this ----

Dim db As Object
 
Const csDAO36 As String = "DAO.DbEngine.36"
Const csDAO35 As String = "DAO.DbEngine.35"
 
On Error Resume Next
 
Set db = CreateObject(csDAO36)
If db Is Nothing Then
    Err.Clear
    Set db = CreateObject(csDAO35)
    If db Is Nothing Then
        MsgBox "Definately a problem!"
        Exit Sub '(or Function)
    End If
End If
On Error Goto 0 '--- or direct to your error handler.

---- BUT -----

That is a LOT of coding effort (changing to late binding)!!! .... So ... it would be my advice to create two Front Ends ... one with a Referece to DAO 3.51, and the Other with a reference to DAO 3.6* ... Change there extensions to something OTHER than .MDB, I use .DLL to scare people ...

*{with early binding, you should NOT be trying to change the code design of your db when multiple folks could be connected to it}

--- Next ----

Create a 3rd Access file (in A97 format, so all users and open it) -- lets name it AppLauncher.mdb ... then drop/remove the DAO reference and create a blank module. Then add this code to the module ...

Public Function OpenApplication()
 
    Const strDBLocation As String = "[URL="file://\\FileServer\FileShare\"]\\FileServer\FileShare\[/URL]"
 
    Const csDAO36 As String = "DAO.DbEngine.36"
    Const csDAO35 As String = "DAO.DbEngine.35"
 
    Dim db As Object
    Dim strAccessLocation As String
    Dim strShellString As String
    Dim x As Double
    Dim strVersion As String
 
    On Error Resume Next
 
    'Determine DAO version assume 36
    strVersion = "36"
    Set db = CreateObject(csDAO36)
 
    'If that doesn't work, try "35", exit if it fails
    If db Is Nothing Then
        err.Clear
        strVersion = "35"
        Set db = CreateObject(csDAO35)
        If db Is Nothing Then
            MsgBox "Can't run app!"
            DoCmd.Quit acQuitSaveNone
        End If
    End If
    Set db = Nothing
 
    On Error GoTo 0
 
    'Find Access
    strAccessLocation = SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE"
 
    'Append file name on to the location
    strDBLocation = strDBLocation & "NameOfApp" & strVersion & ".DLL"
 
    'Build and execute the shell() string
    strShellString = Chr(34) & strAccessLocation & Chr(34) & " " & _
                     Chr(34) & strDBLocation & Chr(34)
 
    'Launch the right app
    x = Shell(strShellString, vbMaximizedFocus)
 
    'Quit the Launcher
    DoCmd.Quit acQuitSaveNone
 
End Function

--- Then ---
Create an Macro named AutoExec and add one action to it ....

Action: RunCode
Function Name: =OpenApplication()

Of couse SAVE the module, and macro then quit the AppLauncher

------

Now ... when you users open the folder to launch your app, they will see 3 files:

AppLauncher.mdb
NameOfApp35.dll
NameOfApp36.dll

They will naturally dbl-click the AppLauncher, which will then launch you app ...

-----

Hope this idea helps! ... Due take note: this is COMPLETELY AIR CODE and no testing or compile has been done.

 

[Rainbowhawk]

Thanks datAdrenaline,

Will give that a try,

ChrisO, not had any problems with sub reports that i can see but will keep my eyes open.

Will keep you posted

Thanks

Hawk

 

[Rainbowhawk]

Hi Guys

Here is the solution I have ended up with, it seems to work, and the intention is to add it to each database,

I ended up looking at the Windows directory to establish which OS was installed (2000 or XP) as OS was reporting WinNT for both, however our standard build is producing C:\windows for XP and C:\winnt for 2000.

I created a Table into which goes the name of the First form or Splash Screen you want to load i.e. Interface, Splash etc

Table Zmage

Fieldname: FirstForm set as pimary key

I created a module to unload the DAO reference if it was present and then load the correct version.

Module

Public Function OpenApplication()
 
On Error GoTo err
 
Dim db As Object
Dim op As String
 
op = VBA.Environ("windir")
 
If op = "C:\winnt" Then
 
     On Error Resume Next
        'set version 3.5
 
        References.Remove References("DAO")
        Set db = References.AddFromFile("\\GBBQBDC612FAP01\BranstonData$\Databases\DLL\DAO350.DLL")
 
Else
 
     On Error Resume Next
        'set version 3.6
       References.Remove References("DAO")
        Set db = References.AddFromFile("\\GBBQBDC612FAP01\BranstonData$\Databases\DLL\dao360.dll")
 
End If
 
Exit Function
 
err:
 
MsgBox err.Description
 
End Function

New Splash Screen

I then created a new splash screen that contains the following code

Private Sub Form_Load()
 
OpenApplication
 
End Sub
 
 
Private Sub Form_Timer()
 
Dim db As DAO.Database
Dim snp As DAO.recordset
Dim frm As String
 
Set db = CurrentDb
Set snp = db.OpenRecordset("zMage", dbOpenSnapshot)
 
frm = snp![FirstForm]
 
    DoCmd.Close
 
    DoCmd.OpenForm (frm)
 
End Sub

I set the timer to 2 seconds but that I think is just preference.

Initial Tests seem to work, just to need to do a few more before we start rolling it out.

Thanks for all your help; I could not have found the solution without you.

Hawk

 

[DCrake]

I like the concept but I think what would improve it is by checking the current DAO referenced before deciding as to whether it needs to be changed. For every instance that the database is opened you are removing and adding the same DLL

Also can you explain what is the logic behind the following

Dim db As DAO.Database
Dim snp As DAO.recordset
Dim frm As String
 
Set db = CurrentDb
Set snp = db.OpenRecordset("zMage", dbOpenSnapshot)
 
frm = snp![FirstForm]
 
    DoCmd.Close
 
    DoCmd.OpenForm (frm)

You also have neglected to close and release the variables

snp.Close
db.Close
Set snp = Nothing
Set db = Nothing

David

 

[Rainbowhawk]

Hi David

You are quite right I did forget close and release the variables, my bag,

RE:

I like the concept but I think what would improve it is by checking the current DAO referenced before deciding as to whether it needs to be changed. For every instance that the database is opened you are removing and adding the same DLL

The problem is that both DLL's are present on all machines as part of the standard build, however if 3.51 is referenced in the database which is opened in XP, it falls over, same happens with 3.6 and Windows 2000.

The only way I could get round it was to unload it, and re reference as required.

Hawk

 

[DCrake]

I understand but after you have determined which O/S is installed you immediately remove the DAO reference and add the appropriate dll. But why remove it if it is the correct one in the first place.

Now we are talking about local instances of the application and not a shared front end are we?

If not then BOOM....
Imagine user A logs in and they want 3.5 then user B logs in and they want 3.6 as soon as user A tries to do anything... Bang... where's my 3.5 gone?

This MUST be done one single user access entry points only.

David

 

[Rainbowhawk]

David,

Read the earlier posts, these are shared files on a network drive,

Have to remove it as it was falling over if I did'nt 3.5 and 3.6 have the same reference name and both are available on the build,

In tests, users logging in from both OS's at the same time have not had any issues, (no idea why) and when they exit the references are set to whichever OS logged in last.

Have tried various function tests and it seems to hold togther.

Hawk

 

[datAdrenaline]

>> Read the earlier posts, these are shared files on a network drive, <<

You are headed for corruption with the process you have implemented.

>> In tests, users logging in from both OS's at the same time have not had any issues, (no idea why) and when they exit the references are set to whichever OS logged in last. <<

You have been lucky. You should NEVER modify the code or schema of a db that is opened by more than one person. End of story.

>> Going down the path of front ends for each machine is not practical, as the users can’t write information to the local C drive to update the version as amendments are made. <<

Then give each user their own folder on network share, then split an place the FE there ... use bob's autoupdater (or similar) to keep the FE's up to date.

...........................


In addition, I would like to comment on ChrisO's statement of:
"The DAO thing can be handled by not using a reference to DAO at all."

Un-referencing DAO is possible simply because Access will use/create a HIDDEN reference to DAO, thus retaining the functionality of CurrentDb. When doing this you simply declare your DAO objects as generic objects (or variants) and any DAO constants you use, will have to be converted to their literals:

So ... your code would be written something like this:

Dim db As Object 'DAO.Database
Dim snp As Object 'DAO.recordset
Dim frm As String

Set db = CurrentDb
Set snp = db.OpenRecordset("zMage", 4) '4 = dbOpenSnapshot

--------------

So ... if you do not un-reference, then you really need two Front Ends ... or better yet! ... figure out WHY the DAO version is not being "honored" (for lack of a better term) that is specified in the called db app, especially since both dll's are on the launching machine. In theory, you *should* set the app to DAO 3.51 PLUS FORCE your users (via a short cut -- which could reside in the network share) to use Access 97 in order to open your app, since all your client boxes have A97.

But ... I did not catch the "Problems" that you said were incurred by the differing OS's, care to share them as we may have solutions to those specific problems in stead of going through all these slightly undesirable solutions?

 

[ChrisO]

Actually, Brent, I was thinking more along the lines of This.