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

 

[Chris O'C via AccessMonster.com]

The legal term for redistributing Word library files to pcs which don't have
that version installed is called software piracy. Don't do it unless you buy
a license for Word - for that version - on the pc you want to redistribute
the library files on.

Chris
Microsoft MVP

 

[Chris O'C via AccessMonster.com]

If you upgraded from Access 2003 to Access 2007, why didn't you keep it in
mdb format? Access 2007 supports replication and user level security in mdb
format files.

There isn't anything available in accdb format that can't be done in mdb
format except enhanced sharepoint server functionality. If you're not using
sharepoint, no reason to convert to accdb format.

Chris
Microsoft MVP

 

[Chris O'C via AccessMonster.com]

Microsoft doesn't support vb. It supports vb.net which is a different
language.

I doubt Microsoft will completely replace vba with vb.net in Office apps
without macro recording because Office is designed with power users in mind
as developers. Power users still want point and click, not vb.net coding, to
automate their spreadsheets, Word docs and db apps.

Chris
Microsoft MVP

 

[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

 

[Chris O'C via AccessMonster.com]

It doesn't matter if Microsoft doesn't support mdb format in future versions
of Access. Older versions of Access won't stop working if the newest version
of Access doesn't support mdbs. Customers will refuse to upgrade to the new
versions of Access and keep using what they've got if the cost of converting
to the new format is either too expensive or decided to be an unneeded cost.
Companies will be economizing for several more years if they want to stay in
business, so it's easy to predict an Access version released in the next 2
years which doesn't support legacy mdbs and vba won't be too popular.

Replication isn't supported in the accdb format because Microsoft recommends
sharepoint server for offsite data integration. Compare the prices of
sharepoint server for internet data apps to the price of Access and you'll
see why.

Chris
Microsoft MVP

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

If you upgraded from Access 2003 to Access 2007, why didn't you keep it in
mdb format? Access 2007 supports replication and user level security in mdb

[quoted text clipped - 13 lines]

 

[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