[RESOLVED] Automation of MS Access from VB6 - Tutorial

[camoore]

The forum contains (or at least contained - since I can not locate it today) an excellent tutorial about the automation of Excel from VB6, written by Si the Geek (it used to be referenced in Si's signature at the bottom of his posts). Inter alia this dealt with the advantages of late binding, in particular so as to render code more readily compatible with later versions of Excel. Great tutorial, vmt. Very useful.

My current question is whether there exists any similar tutorial dealing with the control and automation of MS Access from VB6 code? I seek information generally, but in particular whether there is an advantage in late binding to Access - similar to that recommended for Excel - which would be beneficial in terms of rendering VB6 code more compatible with later MS Access versions.

Would be grateful for any pointers please.

camoore
Wales, UK

[si_the_geek]

My Excel tutorial is available in our Office Development FAQs (at the top of the Office Development forum), and there should be info in those FAQs about automating Access too.

Late Binding is probably possible, but from what I've seen Access has far more variation between versions than Excel has, so whether it is worth it or not probably depends on the kind of things you are doing.

[camoore]

Yes, I found the reference to your Excel Automation tutorial. Glad it is still there. I did try to find it using the forum search routine, but gather that the website has numerous glitches at present due to a new update.

There are indeed several tutorials/thread references to aspects of Access automation from within VB6 (eg. by RobDog) but I have not been able to find any which takes one through the process from basics to late binding and a bit more advanced as does yours for Excel automation. That is ideally what I was looking for.

I note from your tutorial that you recommend late binding, at least once the program has been written and got working with early binding. I did this with all the Excel elements of our program and, after a snag or two, it is working fine. It seems that a way to test this is to remove MS Excel X.0 Object Library from the project reference list - as your tutorial states - and if the program still works, it is late bound. That we achieved.

The code we are using for automation of MSAccess is quite different. I derived it from VB6 training books, several helpful replies to posts at this forum and from both MS articles and other google searches. What I find interesting is that if I remove the project reference
to MSAccess Y.0 Object Library all my code seems still to work fine. This might imply that our code is working late-bound? It is not relying on look-ups in the Object Library?

I wonder what you think in a general way. I ask the question at this stage without going in to code detail of what we are running, but suffice to say we gain access to MSAccess database.mdb files in one of two ways : a. By establishing a connection string to an ADODC control and associated Datagrid (which in turn is linked to bound text boxes) and b. by establishing an ADODB connection for a different purpose. Both work OK with the project reference to the MSAccess Y.0 Object library removed from the project.

If we can count on this still being Ok when installed on machines running a later version of MS Office (and hence a later version of MSAccess) it would seem a good thing, lest continuing to make a project reference to an earlier MSAccess Object Library might cause a clash when run on a machine with a later version of MSAccess installed.

All further comments appreciated.

camoore
Wales, UK

[si_the_geek]

By using ADODC/ADODB (or other database connection technology), you are just using the file as a database - you are not using Access in any way whatsoever.

For that kind of activity, you should not have any Reference to Access, and should not worry about versions etc. All you need to worry about is whether the Provider/Driver you specify in the connection string is installed (Jet is pre-installed by default, but not necessarily the 'right' version).

[camoore]

Thank you Si,

I have found one other area of our code (compact database, based on a response from robdog) where we originally took the early-bound approach and have now modified it to, what I think is, a late-bound method.

The following code shows my before (early) and after (late) code :

    'Dim oApp As ACCESS.Application                            'This was old code for early binding
    
    'Set oApp = New ACCESS.Application                       'This was old code for early binding
    
    Dim oApp As Object                                              'Code for late binding (see next line)

    Set oApp = CreateObject("Access.Application")         'Open Access by late binding
    
    oApp.DBEngine.CompactDatabase "C:\RP\Temp1.mdb", "C:\RP\Temp2.mdb"
    
    'The .mdb file now in C:\RP\Temp1.mdb is compacted and the result
    'is placed into C:\RP\Temp2.mdb

    Set oApp = Nothing

This method accords with the Si_the_Geek Excel tutorial about late binding for Excel. By Dim-ing an Object and then creating that object as an Access application, dependency upon a MS Access Y.0 Object Library is eliminated and I believe that the program will now run under any compatible version of Access (that used when written or later).

The code shown above works OK with NO project reference being called up to Microsoft Access Y.0 Object Library in the VB6 source code. I think that this now makes it late-bound.

camoore

Wales, UK

[si_the_geek]

It is Late-Bound, and it will work with any compatible version of Access... but which ones will be compatible is not something you can assume, as Access has changed significantly over the years (and I think this is one of the bits that changed at least once).

There are alternative methods to compact+repair an .mdb file which don't use Access at all (so the user doesn't need to have it), but those methods all have their own drawbacks (eg: you can use JRO, but you may have installation problems).

[camoore]

Thank you, Si, for further comment. We will shortly test the code on Access 2010. The code lines :

oApp.DBEngine.CompactRepair "C:\RP\Temp1.mdb", "C:\RP\Temp2.mdb", False

and

oApp.DBEngine.RepairDatabase "C:\RP\Temp1.mdb"

Do not work - I get an error message that the method is not supported. These were derived from the FAQ answer by RobDog888 at thread 402074. Maybe these methods were introduced with a later Access version than that with which I am developing the program (which is Access 2000 or Access9). They do not work with early binding either.

Anyway it has been shown that Late Binding to Access does work, but it remains to be seen whether all later versions of Access remain compatible with all the features of the earlier versions. Naturally features which were only introduced with later versions can not be expected to be recognised by earlier versions - just as you advise about Excel in your automation tutorial.

camoore

Wales, UK

[camoore]

Will mark this thread closed. In case this does not work, request that Si the Geek do this for me.

Thank you again for your assistance Si, as always.

camoore

Wales, UK