vba - Python / Access NameError: name '' is not defined

Question

I am trying to rewrite some old Access VBA codes to Python but I stuck with the following error: NameError: name 'ERTZ6635' is not defined

Old VBA Code

 Set ConsTable = DB.OpenRecordset("SELECT * FROM table1")
 ConsCount = 87404
  If ConsCount > 0 Then
    ConsTable.MoveFirst
    For I = 1 To ConsCount
      Set ConsBlendTable = DB.OpenRecordset("SELECT * FROM table2 WHERE CONS_BATCH = " & Char34(ConsTable!Batch))

Python code:

import win32com.client

dbe = win32com.client.Dispatch("DAO.DBEngine.120")
db = dbe.OpenDatabase(r"C:UsersxyzDesktopacess.accdb")
ConsTable = db.OpenRecordset("select * from table1")
ConsCount = 87404

if ConsCount>0:
    ConsTable.MoveFirst()
    for i in range(1, ConsCount):
        ConsBlendTable = db.OpenRecordset("SELECT * FROM table2 WHERE CONS_BATCH = " & eval(ConsTable.Fields["Batch"].Value))

And the ERTZ6635 value is the value in ConsTable.Fields["Batch"].Value

question from:https://stackoverflow.com/questions/65830887/python-access-nameerror-name-is-not-defined

Answer 1

In the VBA code, Char34() likely is a user-defined function since it is not a VBA built-in method. There is however a constant Chr34 for the 34th character in the ASCII table for double quotes. So by its name, this method may wrap double quotes around the input parameter value. This is important since you attempt to translate in Python with eval.

So simple answer is to include double quotes which you can interpolate with F-strings in Python.

sql = f"""SELECT * FROM table2 
          WHERE CONS_BATCH = "{ConsTable.Fields["Batch"].Value}"
       """
ConsBlendTable = db.OpenRecordset(sql)

However, this string interpolation of parameters into an SQL query is not advisable in any language including VBA and Python. Aside from security and efficiency issues, this code can still break if value itself contains double quotes.

Instead, consider parameterization which is supported in MS Access SQL engine via QueryDefs.

VBA (adjusted from earlier SQL concatenation)

Dim qdef As QueryDef
...

Set ConsTable = DB.OpenRecordset("SELECT * FROM table1")

' PREPARED STATEMENT (NO DATA)
sql = "PARAMETERS batch_prm TEXT(255);" _
    & "SELECT * FROM table2 WHERE CONS_BATCH = batch_prm"

ConsTable.MoveFirst
Do While Not ConsTable.EOF
   Set qdef = DB.CreateQueryDef("", sql)
   qdef!batchprm = ConsTable!Batch              ' BIND PARAMETER
   Set ConsBlendTable = qdef.OpenRecordset()    ' OPEN RECORDSET VIA QUERYDEF

    ...

    ConsBlendTable.Close
    ConsTable.MoveNext
Loop

ConsTable.Close

'RELEASE RESOURCES
Set ConsBlendTable = Nothing: Set ConsTable = Nothing
Set qdef = Nothing: Set DB = Nothing

Python (employing try/except for proper COM handling)

Therefore in Python, we similarly interface with QueryDef object. Below loops through every record in recordset with traditional DAO loop (i.e., translation of Do While Not rst.EOF).

import win32com.client

try:
    dbe = win32com.client.Dispatch("DAO.DBEngine.120")
    db = dbe.OpenDatabase(r"C:UsersxyzDesktopacess.accdb")

    ConsTable = db.OpenRecordset("SELECT * FROM table1")

    # PREPARED STATEMENT
    sql = """PARAMETERS batch_prm TEXT(255);
             SELECT * FROM table2 WHERE CONS_BATCH = batch_prm
          """

    ConsTable.MoveFirst()

    while ConsTable.EOF == False:
        qdef = db.CreateQueryDef("", sql)
        # BIND PARAMETER
        qdef.Parameters["batch_prm"].Value = ConsTable.Fields["Batch"].Value 

        # OPEN RECORDSET VIA QUERYDEF 
        ConsBlendTable = qdef.OpenRecordset()

        ...

        ConsBlendTable.Close()
        ConsTable.MoveNext()
                    
    ConsTable.Close()
        
except Exception as e:
    print(e)
    
finally:
    # RELEASE RESOURCES
    ConsBlendTable = None; ConsTable = None
    qdef = None; db = None; dbe = None
    del ConsBlendTable; del ConsTable; del qdef; del db; del dbe

---

Finally, I must note. Instead of direct translation of VBA, use Python's DB-API since it can directly query MS Access databases without DAO or COM objects, specifically with the well-maintained: pyodbc. And run with a JOIN instead of iterative loops via WHERE. And yes, pyodbc supports parameters with ? qmarks.

import pyodbc

dbname = r"C:UsersxyzDesktopacess.accdb"
constr = f"DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};DBQ={dbname};"

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

sql = """SELECT t1.Batch, t2.CONS_BATCH, ... 
         FROM table1 t1
         INNER JOIN tbale2 t2
             ON t1.Batch = t2.CONS_BATCH 
      """       

cur.execute(sql)

for row in cur.fetchall():
    ...

cur.close()
conn.close()