Late binding Access from Excel

[Rick]

I'm using late binding to avoid problems with different user versions of
Access 2003 and 2007. I'm automating Access from Excel, so my code is in
Excel. I removed my reference to Access in VBA Tools. My code works except
for the statement that calls the Access subroutine as below. I get a 7952
error, "Illegal function call." Help greatly appreciated.

Private Sub cmdPullData_Click()

Dim acApp As Object
Dim ws As Object
Dim dbs As Object
Dim myworkspace As String

'Access automation
Set acApp = GetObject(, "Access.Application")
Set ws = DAO.CreateWorkspace("myworkspace", "admin", "")
Set dbs = ws.OpenDatabase(strDB)

acApp.Run "DailyReport" << get 7952 error, "illegal function call"
....

End Sub

 

[BeWyched]

You are opening an Access container but not telling the system which Access
database to load.

Try:

....
Set acApp = GetObject(, "Access.Application")
acApp.OpenCurrentDatabase "path to database\name.mdb"
acApp.Run "DailyReport"

Good luck.

BW

 

[Rick]

Thanks!

I'm working and coding in Office 2003. I've read (primarily from Microsoft)
that since I'm coding my apps in an earlier version (2003), that my early
binding should work fine for users of 2007. However, I've had problems.
That's why I'm converting my early bindings to late bindings. Have you
experienced similar problems? Why would Microsoft make such a statement?

Thanks again.

 

[BeWyched]

Hi Rick

Yes, I have experienced similar problems, not automating Access from Excel
but other combinations. Allthough MS don't admit it I'm of the opinion that
VBA is being left to flounder as each succusive version of office appears -
perhaps others might want to comment on this. e.g. I tried to upgrade several
different Access applications from 2003 to 2007 but had to revert back to
2003 as several important methods and processes are simply not supported
through 2007. e.g. Replication, a major feature of a couple of my
applications, is not supported under the new Access 2007 file format which I
find astonishing. I get the impression that if VBA fits as new versions are
released, that's fine, if not, then tough - VBA is gradually being erroded.
My guess is that VB will eventually become the MS Office programming language
- afterall VB is VBA's big brother, offering all VBA's functionaility so, if
I were MS, why bother supporting a 'junior' language - I might be way off the
mark here but, if I had any money left after the credit-crunch, I'd bet quite
a bit on being correct!! Sorry to get on my hobby-horse so back to your
question: I always now use late binding when automating other Office
applications. Some of my Access applications automate MS Word and this proves
a logistical nightmare if using early binding as my users may have any of the
MS Word versions on their systems. I did revert to shipping the applications
with a 'batch' of Word library files inserting the appropriate file on
installation but this proved difficult - e.g. it all works well until the
users upgrades Word at which point automation fails. MS say that late binding
causes some performace issues but I've never noticed any so, my advice would
be to go for the late binding option whenever possible.

Anyone else got any views on the above?

Cheers.

BW

 

[BeWyched]

1. Thanks Chris - my point exactly. In order to 'upgrade' our current
applications to 2007 we can't adopt the new format! Surely, this is an strong
potential indication of the lack of future support. How many further Access
versions before .mdb is not offered as an option?

2. Distribution of .dll's - point taken. This was a method to ensure the
correct .dll was available to match the users', licenced, versions of Word.
However, I no longer use this method so my innocent piracy days are over!

3. VB vs. VB.net - symantics - you know what I meant.

P.S. any idea why Relication is not supported in the new format (or have I
missed the point and it's now called something else)? This does seem an
astonishing decision as, in this ever increasing mobile world, the ability to
replicate is ever more important.

Regards.

BW

 

[GeoffG]

This reply only comments on:

My guess is that VB will eventually become the MS Office
programming language afterall VB is VBA's big brother,
offering all VBA's functionaility so, if I were MS,
why bother supporting a 'junior' language - I might be
way off the mark here but, if I had any money left after
the credit-crunch, I'd bet quite a bit on being correct!!

Taking the above to mean VB (not VB.NET), I think the above is a
misunderstanding of the relative positions of VB and VBA.

VB is a stand-alone product designed to create Windows
applications. It consists of:

1. A development environment.
2. A VB forms engine.
3. A VB language engine.
4. A run-time engine.
5. An Automation infrastructure.
6. A native code compiler (to create executables).

In contrast, VBA is hosted by other COM-based applications to
provide automation; in other words, to provide a macro language
to those applications. To enable this functionality, VBA has the
same core components mentioned in paragraphs numbered 1, 3, 4 and
5 above. VBA has its own forms engine. VBA doesn't create
stand-alone Windows applications, but that, of course, isn't its
purpose. Microsoft use VBA to automate Microsoft Office and
other products and I believe third parties licence VBA to
automate their products.

Regards
Geoff