How to loop through MS Access tables using Python

Question

from win32com.client import Dispatch

oAccess = Dispatch("Access.Application")
oAccess.Visible = False
oAccess.OpenCurrentDatabase(my_db)
oDB = oAccess.CurrentDB

for tbl in oDB.TableDefs:
     print(table.Name)
     tbl.RefreshLink

I've also done:

for tbl in oAccess.TableDefs:
     print(table.Name)
     tbl.RefreshLink

Error: 'function' object has no attribute 'TableDefs'

I'm starting to understand how to manipulate Windows using win32com, but for some reason it seems like ".TableDefs" isn't recognized. Am I going about it the wrong way?

I know this can be done in VBA. I've been tasked with switching everything over to Python.

Erik A · Accepted Answer · 2021-02-15 17:41:00Z

2

Your first error, here, is that VBA knows CurrentDb is a method, and can't assign a method to a variable, so it invokes the method.

Python, however, has 0 problems with assigning a method to a variable, so just does so. Which means you need the parentheses to invoke the method:

oDB = oAccess.CurrentDb()

This fixes the immediate issue (same goes for tbl.RefreshLink, this likely should be tbl.RefreshLink()).

Furthermore, you never define table, only tbl, so you likely want print(tbl.Name).

answered Feb 15, 2021 at 17:41

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mofongo Over a year ago

Thank you! I started my project using "oAccess.CurentDb()" with the parentheses. The reason I removed them is because it opens up a blank access db that I can't exit out of, even when I call 'quit'. imgur.com/OWngiBe Any idea on how stop that or why it does it?

Erik A Over a year ago

That's the Access application opened by your Python session. It will remain open until you deallocate the COM object. So after oAccess.Quit you will still need oAccess = None (and possibly gc.collect() to force garbage collection). Btw, oAccess.Visible = False to hide it.

Mofongo Over a year ago

I did oAccess.Quit and oAccess = None and that instance of Access is still there. When I do gc.collect() it says name 'gc' is not defined. is that not the name of it?

Erik A Over a year ago

@Mofongo Try import gc. Sometimes the application remains open through open references, so try removing those as well (oDB = None, tbl = None)

Collectives™ on Stack Overflow

How to loop through MS Access tables using Python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related