Access/2003 set references from code?

[Kevin Witty]

I periodically need to rebuild my development copy of an Access 2003 database
by importing everything from the old copy into a new copy. After the import,
I can programmatically do everything I have to to set various properties
except adding the references I need to Office 11, Scripting RunTime, and VBA
extensibility. Is there code I can use to do this so I don't have to add
them manually?

Thanks,

Kevin

 

[Douglas J. Steele]

There are 2 separate methods available to add references programmatically:
AddFromFile or AddFromGUID. Check Help for example of each.

 

[Albert D.Kallal]

I don't think you need to add any references. The defaults are:


Visual Basic for Applications
Microsoft Access 11 Object library
OLE automation
DAO 3.6
ADO 2.1

The only time you need office 11 is if you use some tool bar stuff.

in fact, you can still use the command bars collection..but not "define" the
data types used (that is what I do).

For word, Excel, and things like the Scripting runtime, you REALLY want to
use late binding.

The above approaches would eliminate your need to set references (and, you
can't set refs in a mde anyway..and you *should* be distributing a mde to
your users).

I suppose you can use code to set the references for your mdb, and then when
you distribute your application as a mde, then those refs will be set. If
your only two refs that you add are office 11, and scripting, then you
likely could risk your approach, and not have broken refs (broken refs are
likely the most common problem in this newsgroup..and thus you as a
developer must take EXTRA precautions to avoid this problem, hence my
suggestions to use late binding for the scripting object and eliminate the
need to set refs is not only to advoid having to set refs, but in fact will
VERY MUCH improve the reliably of your software in terms of broken refs.

 

[Kevin Witty]

Thanks, Albert. I was able to eliminate the references to VBA extensibility
and Office 11, but I don't grok how to set up for late binding with Scripting
Runtime. Could you point me to an example?

(Always appreciate your help!)

Kevin

 

[Douglas J. Steele]

Here's an example:

Public Function GetFreeSpace(ByVal strDriveLetter As String) As Currency
Dim fso As Object

Set fso = CreateObject("Scripting.FileSystemObject")
GetFreeSpace = fso.GetDrive(strDriveLetter).FreeSpace
Set fso = Nothing

End Function

 

[Kevin Witty]

Thanks, Doug. Is there a reference somewhere which translates references
into the text required for CreateObject? I think I tried
"Microsoft.Scripting.Runtime", and it failed.

Kevin

"It's always easy when you know how. Getting there's the bitch.")

PS: Why Currency? Rounds to 2 decimals automatically?

 

[Douglas J. Steele]

When you have the reference set, open up the Object Browser (F2 when you're
in the VB Editor). Find your reference in the top combo box and select it.
That's the first part you need to use. In this case, it's Scripting. Find
the object you're trying to instantiate in the list of classes. That'll give
you the second part. Note that this is the same as if you had a reference
and were declaring the object:

Dim fso As Scripting.FileSystemObject


I wondered about the currency myself when I looked at that snippet from my
files. I believe it's because of how large the value can be. Currency is an
8 byte data type, which can handle values between -922,337,203,685,477.5808
and 922,337,203,685,477.5807. A Long Integer can only handle values
between -2,147,483,648 and 2,147,483,647

 

[Kevin Witty]

(See below before answering)

Sorry, Doug, I thought I was out of the woods, but without setting a
reference to MS Office 11, I still don't see how to use late binding to make
this code work:

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)

All Object Browser shows me for a search on Office is msoCondition and
msoFileType.

I know Albert said I shouldn't need references to Office 11 except for
Command Bars, but he probably uses a slicker method than I do to get an easy
file dialog <g>.

Below:

Think I finally figured it out:

Dim obj As Object
Set obj = CreateObject("msoFileDialogType")
Dim fd As Object
Set fd = Application.FileDialog(msoFileDialogFilePicker)

seems to work. I was thinking, wrongly, that obj would have to used
somewhere below, not realizing that the CreateObject was sufficient. Duhh!

Hope this helps someone else as dense as I am. Doing an Object Browser
search on msoFileDialogFilePicker showed me msoFileDialogType as a class of
Office, and I finally figured out I just needed to create an
msoFileDialogType object, and not one for Office itself.

It should be noted that a search on Office, and then choosing msoFileType
does NOT display msoFileDialogFilePicker as a member. Tacky.

Maybe I'm finally starting to grok this stuff, thanks to the good folks
here. I'm definitely renewing my MSDN subscription!

And I still think that if I designed and programmed as badly as MS does, I
wouldn't have a job now.

Kevin

 

[Douglas J. Steele]

Don't use the File Dialog. Instead, use the API calls demonstrated in
http://www.mvps.org/access/api/api0001.htm at "The Access Web"

To answer your question, though, I would assume it would be:

Dim obj As Object
Set obj = CreateObject("Office.FileDialog")
Dim fd As Object
Set fd = Application.FileDialog(3)

You can't use built-in constants like msoFileDialogFilePicker: you have to
determine what their value is (or else declare them as constants: Const
msoFileDialogFilePicker As Integer = 3)