Is there any example to connect to SQL Server Compact

[ilovefreesw]

I am trying like this but it's failing.

import pyodbc

def list_tables_from_sdf(sdf_path):
    """
    Connects to a SQL Server Compact (.sdf) database and lists all user tables.
    """
    # Connection string for SQL Server Compact 4.0
    # The driver name must exactly match what was installed.
    conn_str = (
        r'DRIVER={Microsoft SQL Server Compact OLE DB 4.0};'
        rf'Data Source={sdf_path};'
    )
    
    tables = []
    try:
        # Establish connection
        with pyodbc.connect(conn_str) as conn:
            cursor = conn.cursor()
            
            # The 'TABLE' type filters for user-created tables.
            # You can also query without the WHERE clause to see system tables.
            query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'TABLE'"
            
            print("Executing query to find tables...")
            for row in cursor.execute(query):
                tables.append(row.TABLE_NAME)

    except pyodbc.Error as ex:
        sqlstate = ex.args[0]
        print(f"Database Error Occurred: {sqlstate}")
        print(ex)
        return None
    
    return tables

if __name__ == "__main__":
    sdf_file = r"D:\sdftest\MDProcesses.sdf"
    table_list = list_tables_from_sdf(sdf_file)
    
    if table_list:
        print("\nTables found inside the SDF:")
        for table_name in table_list:
            print(f"- {table_name}")
    else:
        print("Could not retrieve table list.")

But this works:

import pythoncom
import win32com.client

def list_tables_from_sdf(sdf_path):
    # Initialize COM
    pythoncom.CoInitialize()

    # Create ADO connection
    conn = win32com.client.Dispatch('ADODB.Connection')
    conn.ConnectionString = f"Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source={sdf_path};"
    conn.Open()

    # Use SQL query on INFORMATION_SCHEMA.TABLES instead of OpenSchema
    rs = win32com.client.Dispatch('ADODB.Recordset')
    rs.Open("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='TABLE'", conn)

    print("Tables inside the SDF:")
    while not rs.EOF:
        print(f"- {rs.Fields('TABLE_NAME').Value}")
        rs.MoveNext()

    rs.Close()
    conn.Close()


if __name__ == "__main__":
    sdf_file = r"D:\sdftest\MDProcesses.sdf"
    list_tables_from_sdf(sdf_file)

This one returns only 1 table for some reason. But I want all tables listed so tried using pyodbc but somehow that gives this error;

(sdftest) PS D:\sdftest> uv run python .\main.py
Database Error Occurred: IM002
('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
Could not retrieve table list.

I get this error using pyodbc for both; 4.0 and 3.5.

[gordthompson]

The Wikipedia article for SQL Server Compact says

An ODBC driver for SQL CE does not exist, nor is one planned.

[ilovefreesw]

Thanks. ChatGPT and Gemini kept giving me pyodbc code... so I had to clear it up. Never worked in Microsoft ecosystem from development pov.