Run Access queries through Excel VBA on an already open instance of Access

Question

I have a spreadsheet with Access query names listed. I want to run those queries in Access through Excel VBA, but instead of opening a new instance of Access, I want to work on the instance (and database) that is already open. The code I currently have is only for the creating new instance:

Sub RunQueries()

Dim appAccess As Object
Set appAccess = CreateObject("Access.Application")
appAccess.Visible = True
appAccess.OpenCurrentDatabase "D:\Access\tysql.accdb", False

Dim QueryName As String

For i = 2 To Queries.Count + 1
    QueryName = Cells(i, 1).Value
    appAccess.DoCmd.OpenQuery QueryName
Next i

End Sub

I would be grateful for any suggestions.

Couldn't you just use GetObject(, "Access.Application") instead of CreateObject("Access.Application")? — YowE3K
– YowE3K, Commented Dec 16, 2017 at 19:49
And if after usinfg GetObject, appAccess is Nothing, you can still use CreateObject. See : tushar-mehta.com/excel/vba/xl_doesnt_quit/index.htm Near solution 1 (and substituting "Access.Application" foe "Excel.Application" ) — Peter Pesch
– Peter Pesch, Commented Dec 16, 2017 at 19:53

Parfait · Accepted Answer · 2017-12-17 04:25:01Z

Actually, you are using the GUI side of MS Access and not its underlying database engine. Consider that route via ADO which involves opening no .accdb database to screen. In fact, you do not even need the MSAccess.exe GUI installed as an MS Office program!

Dim conn As Object

Set conn = CreateObject("ADODB.Connection")

conn.Open "DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ=C:\Path\To\Database\File.accdb;"

Dim QueryName As String

For i = 2 To Queries.Count + 1
    QueryName = Cells(i, 1).Value
    conn.Execute QueryName
Next i

Set conn = Nothing

The beauty of using Access as a database engine is that it is portable to other languages like any RDBMS not just Excel VBA. For example in Python (which I see is one of your profile tags) you can iterate through a csv (save sheet in that format) and run named queries in database.

import csv
import pyodbc

database = r'C:\Path\To\Database\File.accdb'
constr = "Driver={{Microsoft Access Driver (*.mdb, *.accdb)}};DBQ={0};".format(database)

db = pyodbc.connect(constr)
cur = db.cursor()

with open(r'C:\Path\To\Query\Names.csv', 'r') as f:
    r = csv.reader(f)
    for line in r:
        cur.execute(line[0])
        db.commit()

cur.close()
db.close()

Collectives™ on Stack Overflow

Run Access queries through Excel VBA on an already open instance of Access

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related