Solved

VBA code works in one DB but not another

Posted on 2013-10-23
10
345 Views
Last Modified: 2013-10-28
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
Comment
Question by:HKFuey
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 100 total points
ID: 39594062
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
 

Author Comment

by:HKFuey
ID: 39594083
Hi mbizup,

Microsoft Scripting Runtime reference is checked in both DB's. Attached screenshot is of the one not working.
References.bmp
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39594089
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
 

Author Comment

by:HKFuey
ID: 39594099
The copy is working OK it is the Shell command that gives the error.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39594111
<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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 61

Expert Comment

by:mbizup
ID: 39594115
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 400 total points
ID: 39594123
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
 

Author Comment

by:HKFuey
ID: 39594235
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
 
LVL 57
ID: 39594353
More then likely, it's pathing or security, both of which will give you that error message.

Jim.
0
 

Author Closing Comment

by:HKFuey
ID: 39605467
Thanks for the help.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now