convert code to vba 2010

Posted on 2012-09-18
Last Modified: 2012-09-18
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

Question by:fordraiders
    LVL 61

    Assisted Solution

    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.
    LVL 39

    Accepted Solution

    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
    LVL 3

    Author Closing Comment

    Thanks to both ..good suggestions..

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    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 …

    755 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

    16 Experts available now in Live!

    Get 1:1 Help Now