CODEWITHSUNDEEP
excelms-accessvba
ASH
ASH

Reputation: 20362

Trying to run Access Queries from Excel

I just encountered two issues. For one thing, I can't seem to run a Make Table Query in Access, from Excel. I'm getting an error message that says 'Table Risk Rating already exists'. There must be a way to run a Make table Query from Excel. Also, and more importantly, my code seems very unstable. If I run it by hitting F8 over and over, everything works fine. If I run it by a button click event, I get the following error: 'The remote server machine does not exist or is unavailable'. It seems like Excel is losing it's communication with Access. That's just a guess. This thing is on my desktop, so I don't really think this the remote machine is unavailable.

Here is my code.

Sub RunQueriesInAccess()

Dim AC As Access.Application

    Set AC = CreateObject("Access.Application")
    strDatabasePath = ThisWorkbook.Path & "\Database1.accdb"
    With AC
        .OpenCurrentDatabase (strDatabasePath)
        .CurrentDb.Execute "qry_RISK_RATING"
        .CurrentDb.Execute "qry_Delete_ALLL"

            Set DB = AC.CurrentDb
            Set qry = DB.QueryDefs("qry_DATA_HIST")
            qry.Parameters(0) = Worksheets("Impact Analysis New").Range("B1").Value
            qry.Execute

            Set qry = DB.QueryDefs("qry_LIMIT_HIST")
            qry.Parameters(0) = Worksheets("Impact Analysis New").Range("B1").Value
            qry.Execute

        .Quit
    End With

ActiveWorkbook.RefreshAll

End Sub

Any idea what's going on here?

Thanks!!

Upvotes: 0

Views: 259

Answers (1)

Erik A
Erik A

Reputation: 32682

I'm going to answer what I know, but can't replicate vague instability.

Using .CurrentDb.Execute, you can execute action queries, but can't overwrite tables using a CREATE TABLE or SELECT ... INTO query.

Using .DoCmd.SetWarnings False and .DoCmd.OpenQuery, however, you can. You can use .DoCmd.SetParameter if your parameterized queries are also creating tables.

You are using a lot of undeclared variables in your piece of code. I'm going to declare them for you and use late binding for the Access.Application, instead of your early/late binding combo thing you got there.

Sub RunQueriesInAccess()

    Dim AC As Object
    Set AC = CreateObject("Access.Application")
    Dim strDatabasePath As String
    strDatabasePath = ThisWorkbook.Path & "\Database1.accdb"

    With AC
        .OpenCurrentDatabase (strDatabasePath)
        Dim db As Object
        Set db = .CurrentDb
        .DoCmd.SetWarnings False
        .DoCmd.OpenQuery "qry_RISK_RATING"
        .DoCmd.OpenQuery "qry_Delete_ALLL"
        Dim qry As Object
        Set qry = db.QueryDefs("qry_DATA_HIST")
        qry.Parameters(0) = Worksheets("Impact Analysis New").Range("B1").Value
        qry.Execute

        Set qry = db.QueryDefs("qry_LIMIT_HIST")
        qry.Parameters(0) = Worksheets("Impact Analysis New").Range("B1").Value
        qry.Execute
        .DoCmd.SetWarnings True
        .Quit
    End With
    ActiveWorkbook.RefreshAll
End Sub

Upvotes: 1

Related Questions