error 2147024770 (8007007e)

[Guest]

In Access 2003 app, I have the following code:

Dim fsoSysObj As FileSystemObject
Dim fFile As File
Set fsoSysObj = New FileSystemObject
Set fFile = fsoSysObj.GetFile(Path & "smpdata.mdb")
fFile.Copy Path & "archive\" & Format(Now(), "mmddyy") & "smpdata.mdb"

On one machine, there is a VB runtime error with code -2147024770 (807007e).
It references line Set fsoSysObj = New FileSystemObject.

What's going on? How can I solve this on that machine?

Rod

 

[Guest]

Have you checked the References in the database on that machine for Windows
Script Host Object Model?

 

[Douglas J. Steele]

Or try using Late Binding, so that no reference is required:

Dim fsoSysObj As Object
Dim fFile As Object
Set fsoSysObj = CreateObject("Scripting.FileSystemObject")
Set fFile = fsoSysObj.GetFile(Path & "smpdata.mdb")
fFile.Copy Path & "archive\" & Format(Now(), "mmddyy") & "smpdata.mdb"

Of course, VBA has a built-in FileCopy command, so FSO isn't required at
all:

FileCopy Path & "smpdata.mdb", _
Path & "archive\" & Format(Now(), "mmddyy") & "smpdata.mdb"

(If the reason for using FSO to do the copy is that it doesn't object to
copying an open file, that's not a valid reason. Just because you CAN copy
an open MDB file doesn't mean you SHOULD.)

 

[Guest]

Thanks for the input Ralph. I'm kind of an amateur programmer using Access
VBA. It's not possible for me to go to every computer to check its
configuration. I was hoping it was enough that Access XP or 2003 is
installed. It kind of defeats the purpose of developing "deployable"
applications if every user has to configure his computer with special library
references. Any information you can provide to help me understand my best
chances for deploying an Access application would be appreciated.

Rod

 

[Guest]

Doug... Thanks for the idea. I'm really wanting to know why it would fail on
just one computer out of 20. If I make the change, what's to say that it
wouldn't fail on two other computers. This is the part I don't understand.

I don't know anything about Late Binding, but I can research it.

The mdb file is not open, so maybe FileCopy is the best option. It seems to
me like I tried it before and it didn't work. Thanks for any help you can
provide.

Rod

 

[Douglas J. Steele]

Well, you don't need to do any research on Late Binding, as I gave you all
the code. <g> Simply remove the reference, and try it.

Why it fails on some computers, but not on others, is likely due to problems
with the References collection caused by differences between their machines
and the machine where the application was developed. If you're interested in
more information, check
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html or my
December, 2003 "Access Answers" column in Pinnacle Publication's "Smart
Access". You can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html.

 

[Guest]

Thank you. I understand that there could be differences, but I don't
understand why, if we have the same software installed.

 

[Douglas J. Steele]

Even in the most controlled environments (such as where I work), typically
not every user is going to have exactly the same applications installed.
It's not sufficient to ensure that each user has the same version of Access
installed, with all of the same patches applied. Some other application
could have changed the version of a shared object.

 

[Guest]

OK... I'll read your articles! Thanks a bunch for taking the time to answer
my questions.

 

[David W. Fenton]

I'm kind of an amateur programmer using Access
VBA. It's not possible for me to go to every computer to check
its configuration. I was hoping it was enough that Access XP or
2003 is installed. It kind of defeats the purpose of developing
"deployable" applications if every user has to configure his
computer with special library references. Any information you can
provide to help me understand my best chances for deploying an
Access application would be appreciated.

This is why it's better to avoid using solutions that depend on
setting references. Use internal Access functions or, as Douglas has
shown you, use late binding for external libraries. Of course, late
binding will fail, too, on any machine that doesn't have your
external library properly registered.