none
excel vba crashes when I tried to open Access database RRS feed

  • Question

  • Hi 

       I have a written a excel vba macro in excel 2013 and has also used acces data objects .Here is my code

      sub Button1_Click()
    On Error GoTo ErrorHandler
    Dim stDB As String
      Dim stCon As String
     Application.ScreenUpdating = False


            Set cnt = New ADODB.Connection
            Set rst = New ADODB.Recordset

            'Pathway and name of the database
            stDB = "\\testsite\DavWWWRoot\sites\test\SiteAssets\macess_Connection.accdb"

            'Create the connectionstring.
            stCon = "Provider=Microsoft.Ace.OLEDB.12.0;Persist Security Info = False;" & _
            "Data Source=" & stDB & " ; "

            'Open the connection

             cnt.Open stCon
             rst.ActiveConnection = cnt
             rst.CursorLocation = adUseServer
            rst.Source = "SELECT * FROM Open_msaccess"
            rst.Open

            rst.Close
            Set rst = Nothing
            Set cnt = Nothing

    ErrorHandler:
             MsgBox Err.Description, Err.HelpContext

    end sub

    when it reaches rst.open .

    .excel 2013 crashes .Could you please suggest ?

    thanks in advance


    nain1987

    Friday, September 5, 2014 4:42 AM

All replies

  • Global oApp As Object

     

    Sub OpenAccess()

     

       Dim LPath As String

       Dim LCategoryID As Long

      

       'Path to Access database

       LPath = "C:\Users\Excel\Desktop\Coding\Microsoft Access\Northwind.mdb"

      

       'Open Access and make visible

       Set oApp = CreateObject("Access.Application")

       oApp.Visible = True

      

       'Open Access database as defined by LPath variable

       oApp.OpenCurrentDatabase LPath

      

       'Open form of interest

       oApp.DoCmd.OpenForm "Form1"

      

    End Sub

     

    ********  *******  ********  *******  ********  *******  ********  ******* 

     

    ‘RUN MS ACCESS MACRO FROM EXCEL:

    Sub AccessTest1()

          Dim A As Object

          Set A = CreateObject("Access.Application")

          A.Visible = False

          A.OpenCurrentDatabase ("C:\Users\Excel\Desktop\Coding\Microsoft Access\Northwind.mdb")

          A.Application.Run "ExportToExcelTest"

    End Sub

     

    ********  *******  ********  *******  ********  *******  ********  ******* 

    ‘ Run a delete query in MS Access

    Sub OpDaHus01()

        Dim strDatabasePath As String

        Dim appAccess As Access.Application

        Dim strSQL As String

       

        strDatabasePath = "C:\Users\Ryan\Desktop\Coding\Microsoft Access\Northwind_2012.mdb"

        strSQL = "DELETE tblTest.* FROM tblTest;"

        Set appAccess = New Access.Application

        With appAccess

            .OpenCurrentDatabase strDatabasePath

            .DoCmd.RunSQL strSQL

            .Quit

        End With

        Set appAccess = Nothing

     

    End Sub

     

    ********  *******  ********  *******  ********  *******  ********  ******* 

     

    If you need to run the code from a Macro, it needs to be a Public Function (rather than Sub), and it needs to be in a standard module (not a Form, Report or Class module).


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, September 6, 2014 1:23 PM
  • Hi  

      Thanks , but excel 2013 works fine for some users and not for some . Well i assume there cannot be anything wrong with the  vb macro code of excel 2013 .

    All user's including me are using access engine 2010 32 bit version and excel 2013 32 bit version for a 64 bit OS  system  , But only i am facing this issue

    could you please suggest ? 


    nain1987

    Wednesday, September 10, 2014 5:39 AM