How can I filter an ms-access databse, using QSqlTableModel and QLineEdit?

Question

I'm building a GUI that allows users to search information in a ms access database (yup. It has to be the ms access) The user has a textfield where he can type his search and the Tableview should update instantly. At the moment the DB disappears whenever you type a letter in the field.

Took me a while to figure out the problem: my SQL statement is simply not right. (Thanks to model.lastError)

The whole function looks like this:

    self.ui.Kistenschild_suchen.textChanged.connect(self.update_filter)

    
def update_filter(self, s):
    s = re.sub("[\W_]+", "", s)
    filter_str = 'Produkt LIKE %{}%"'.format(s)
    self.ui.model.setFilter(filter_str)
    print(self.ui.model.lastError())

In this case I typed k

The errormessage is:

PySide6.QtSql.QSqlError("-3100", "QODBC: Unable to execute statement", "[Microsoft][ODBC-Treiber für Microsoft Access] Syntaxfehler in Abfrageausdruck 'Produkt LIKE (%h%\")'.") at 0x000001CA4FB33B88>

Point of interest should be the '%h%")'."' Since it shows more characters than typed

I tried to change in several ways, like changing the % to * and? Still nothing

EDIT:

Here is the minimal reproducible example:

import re
import sys

from PySide6.QtCore import QSize, Qt
from PySide6.QtSql import QSqlDatabase, QSqlTableModel
from PySide6.QtWidgets import (
    QApplication,
    QLineEdit,
    QMainWindow,
    QTableView,
    QVBoxLayout,
    QWidget,
)

Driver= r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:\scripts\python\pyside_Tutorials\databases\chinook.accdb'

db = QSqlDatabase("QODBC")
db.setDatabaseName(Driver)
db.open()






class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()

        container = QWidget()
        layout = QVBoxLayout()

        self.search = QLineEdit()
        self.search.textChanged.connect(self.update_filter)
        self.table = QTableView()

        layout.addWidget(self.search)
        layout.addWidget(self.table)
        container.setLayout(layout)

        self.model = QSqlTableModel(db=db)

        self.table.setModel(self.model)

        self.model.setTable("Track")
        self.model.select()

        self.setMinimumSize(QSize(1024, 600))
        self.setCentralWidget(container)
        
        
        
     # tag::filter[]
    def update_filter(self, s):
        s = re.sub("[\W_]+", "", s)
        filter_str = 'Name LIKE "%{}%"'.format(s)
        self.model.setFilter(filter_str)
        print(self.model.lastError())
        print(s,type(s))

    # end::filter[]


        
        
app = QApplication(sys.argv)
window = MainWindow()
window.show()
app.exec()

This code gives me the errormessage: <PySide6.QtSql.QSqlError("-3010", "QODBC: Unable to execute statement", "[Microsoft][ODBC-Treiber für Microsoft Access] 1 Parameter wurden erwartet, aber es wurden zu wenig Parameter übergeben.") at 0x0000016FC7535108>

Which means something like: "1 parameter was expected but too few parameters were passed"

Answer 1

MS-Access needs a double apostrophe like:

def update_filter(self, s):
        s = re.sub(r"[\W_]+", "", s)
        filter_str = f"Produkt '%{s}%'"
        self.model.setFilter(filter_str)```