• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • 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..
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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