• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 530
  • Last Modified:

convert code to vba 2010

access vba 2010 needed:

Is there a different way to write this code for office 2010 vba ?

This code runs a macro from an outside database and then compacts and repairs it..

Public Sub RunSap6()

Const dbPathName = "C:\Program Files\Crs\Sapbackend6.mdb"
Dim Acc As Object, DB As Object

      Set Acc = CreateObject("Access.Application")
      Set DB = Acc.Application.DBEngine.Workspaces(0).OpenDatabase(dbPathName, 0, False)
                Acc.OpenCurrentDatabase dbPathName, False
                Acc.DoCmd.RunMacro "DeleteMain_2012_All"
                Set Acc = Nothing
                Set DB = Nothing
                ' backend6
gsDBPath = "C:\Program Files\Crs\"
    On Error Resume Next
    Kill gsDBPath + "Sapbackend62.mdb"
    DBEngine.CompactDatabase gsDBPath + "Sapbackend6.mdb", gsDBPath + "Sapbackend62.mdb"
    Kill gsDBPath + "Sapbackend6.mdb"
    Name gsDBPath + "Sapbackend62.mdb" As gsDBPath + "Sapbackend6.mdb"
End Sub

2 Solutions
Your code looks okay as is.

What happens when you run it in an Access 2010 environment?

A couple of things that you may need to check:

-  You are using a .mdb extension in your code.  Has the file format been changed to .accdb?

- Make sure that you have permissions in the folder the database resides in.  If you have gone from Win XP to Win 7, your permissions on the Program Files folder may have changed.
You can do it from command file with two lines:
"c:\Program Files\Microsoft Office\Office14\MSACCESS.EXE"  "C:\Program Files\Crs\Sapbackend6.mdb" /X DeleteMain_2012_All
"c:\Program Files\Microsoft Office\Office14\MSACCESS.EXE"  "C:\Program Files\Crs\Sapbackend6.mdb" /compact

Open in new window

I don't see reasons to rename file before compacting. May be better to made backup?

"c:\Program Files" is not the best place for working DB. You will have many problems in Windows 7
FordraidersAuthor Commented:
Thanks to both ..good suggestions..

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now