[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

VBA code works in one DB but not another

I have this code on the opening of a database, it checks the version and if not up to date it downloads and runs an installer for the current version: -
        Dim Fso As New FileSystemObject
        Fso.CopyFile "\\Server\Share\Install.exe", "C:\Folder\Install.exe", True
        Shell "C:\Folder\Install.exe", vbNormalFocus
         DoCmd.Quit
            

Open in new window


This was working fine but now I get "invalid procedure call or argument"

If I just navigate to the .exe and run it works fine. If I copy the code to a fresh database it runs fine. Can anyone help?
0
HKFuey
Asked:
HKFuey
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
mbizupCommented:
Check your references.

From the vba editor:

Tools --> references

Ensure that the references checked are the same for both databases, specifically in sure that you have the Microsoft Scripting Runtime reference checked.
0
 
HKFueyAuthor Commented:
Hi mbizup,

Microsoft Scripting Runtime reference is checked in both DB's. Attached screenshot is of the one not working.
References.bmp
0
 
mbizupCommented:
Alternatively, use FileCopy, which is built in and needs no references:

        FileCopy "\\Server\Share\Install.exe", "C:\Folder\Install.exe", True
        Shell "C:\Folder\Install.exe", vbNormalFocus
         DoCmd.Quit

Open in new window



Or use the fileSystemObject with late binding:

       Dim Fso As Object
       Set fso = CreateObject("Scripting.FileSystemObject")

        Fso.CopyFile "\\Server\Share\Install.exe", "C:\Folder\Install.exe", True
        Shell "C:\Folder\Install.exe", vbNormalFocus
         DoCmd.Quit

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
HKFueyAuthor Commented:
The copy is working OK it is the Shell command that gives the error.
0
 
Jeffrey CoachmanMIS LiasonCommented:
<No Points wanted>

You should also be able to use FileCopy...
(VBA reference not needed)

    FileCopy "\\Server\Share\Install.exe", "C:\Folder\Install.exe"

But FSO has many other aspects/advantages that may be of use to you in other parts of your database
So this is just an FYI...

JeffCoachman
0
 
mbizupCommented:
The shell command itself is syntactically OK...

Is the install file attempting to overwrite your currently open database by any chance?

What happens if you rename your database (temporarily) and try to run the same code?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Still might be references.

Check any unchecked reference (doesn't matter which), compile, ensure no errors, then save and close DB and Access.

Re-open and uncheck the reference just checked (will be last).  Again, compile, ensure no errors, then save and close DB and Access.

Try again.   If you still get the error, then it may be pathing and/or security that is the problem.

Take that exact DB, give it to another user who does not have an issue, and try it.   If it fails there, then the DB is corrupt.  If it works, then it is pathing and/or security that is the issue for the user having the problem.

Jim.
0
 
HKFueyAuthor Commented:
Re "What happens if you rename your database (temporarily) and try to run the same code?"

I get same error.

This is an old mdb that has been running for years. I tried importing everything to accdb and still get the error!
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
More then likely, it's pathing or security, both of which will give you that error message.

Jim.
0
 
HKFueyAuthor Commented:
Thanks for the help.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now