Code to Verify Source?

  • Thread starter Thread starter Jeffrey M via AccessMonster.com
  • Start date Start date
J

Jeffrey M via AccessMonster.com

As part of an Access Module, I import data from a dozen or so Excel files.
Once in a while, a process goes down and the file isn't created... and my
module comes to a screeching halt. Is there a way I can have a module
quickly verify that the source files are in place before I start my "big
bang" one each morning? How would I code that?

thx,
jeff
 
If the files are all in the same subdirectory, you may want to look in Help
for the FileSearch Object. There are several different possibilities
depending on what you are doing.
 
Use the Dir function in VBA to determine whether the file exists, i.e.,

If Len(Dir("filepathhere"))=0 Then
'Oops no file
End if
 
The Dir function is fine if you are looking for one specific file or folder;
however, since he has "a dozen or so", the FileSearch would be a better option
 
FileSearch is part of the Office library...I don't believe it would be
available in a runtime environment and it causes a need for an unnecessary
reference. Also, IMO Dir is a lot more straight forward. If he is looking
for multiple files, it is just a matter of using a wildcard.
 
There is no reason it would not be available in a runtime environment
provided the users are on the same version of office. It is also possible to
deliver any reference library files with a runtime, so that is not a
technical issue, only a procedural one.

If you prefer the Dir function, use it. Personally, if I am looking for
more than one file, I prefer the FileSearch object. At least it is not an
ActiveX control :)
 
Actually, the type libraries are NOT distributable....legally anyway. Plus,
you're assuming that all of Office is installed, in the same location, and
the same version as the one you used during development. Its issues like
this that result in the reference problems frequently mentioned in the NGs.
My philosophy is that if you can avoid setting a reference to anything other
than the basics, you're better off.
 
I agree that unnecessary references should be avoided. I do not agree with
your statement regarding legal distribution. At least as I recall from a
couple of years ago in 2000, that the developers toolkit gives you the right
to distribute the runtime version and any supporing libraries required to
support the application. the packager would include the necessary files to
create a setup.exe and .cab file.

Now, if you are just creating an mde and passing it around, that is a
different issue.
 
The Office type library is separate from Access, and isn't distributable.
The FileSearch object is in that library.
 
I am researching this, Paul, and will get back either with an apology or a
little gloating. Looser buys the next round :)
 
I'll give you this much...

http://support.microsoft.com/?kbid=210613

See the note towards the bottom. This is because the Office type library
doesn't get installed with the runtime.

Now, technically, the FileSearch property is implemented/available in the
runtime, but the property is just an accessor function to the FileSearch
object in the Office type library. It MIGHT work if you've installed a
runtime version of Access and the proper version of Office is already
installed. I've never bothered to check that...but its iffy anyway. If
you're installing runtime, you have to assume Office isn't installed, so...

1. You cannot have a reference to the Office library
2. You have to declare your variable for the FileSearch object as a variant
and assign it from the FileSearch property of the Application object...no
intellisense
3. You cannot use the enums associated with the object...you have to create
your own constants
4. You have to trap for any error and implement an alternate method (i.e.,
Dir or an API function) in the event that it doesn't work

Given 4, why bother? Sure, it might be a little more robust than Dir for
searching sub folders, but if you can't count on using it, it isn't much
good.

And even if you could legally redistribute, in the latest PDW, the generated
installer doesn't register any components referenced by the developer (i.e.,
libraries, ActiveX) anyway. You have to register them using a work around
(or another installer).

Less references = Less headaches!
 
Paul,

I have been researching this, and I can't get a clear answer on what is
allowed and what is not when it comes to distribution. I don't disagree with
anything you say. Perhaps in an application that may be distributed widely
where you don't know what the end user's system is going to be, it would be
wise to avoid any headaches. In my environment, all the users have the same
configuration, so I can pretty well know what will and will not work.

I do not use ActiveX controls for exactly the reasons you state.

I still remember, however, that in the Access Developers Edition for 2000,
when you put together an install package, it would include all references and
the runtime. It would only install the reference libraries if they did not
exist on the target machine or were an older version.
We had four clients using the system. One of the clients had multiple
locations. There was a mix of everyting from Windows 95 to 2000. We never
had a problem with any of the installs.
 
This article seems pretty definitive...

http://support.microsoft.com/kb/q249843/


PDW is ignorant about licensing...it will include a referenced component as
part of your package...whether or not it is legally redistributable.

Also, whenever you install an ActiveX component, it is almost mandatory that
you review what PDW is suggesting because it often neglects to include a
referenced component's dependencies. PDW can create a bad installer that is
missing required components, or worse an installer that will screw up the
target PC. If you're distributing anything beyond the basics, you really
must know all the components that should included in the installer to allow
the component to function properly (and not screw up the target PC). If you
installing in a fairly homogenous environment, this may not be as big a
concern, but if the configuration of the target is unknown, you need to do
some homework.

Funny thing...looking at the install image for the Access 2000 runtime, I
see MSO9.DLL is in the package. It must be crippled with a runtime install
though, because otherwise FileSearch would be available in the runtime
without having Office installed (see article I posted earlier).
 
I owe you a coke. However, we recently had a problem where some of our users
switched to 2003 while some were still on 2000. This caused problems, so we
just copied the libraries we need into place and everything kept working.
 
Oh, one more thing. Note the article said it did include the files. So, I
was right that we did it that way when we were using 2000 Developer, just did
not know we were being evil criminals endangering the financial stability of
Microsoft.
 
Back
Top