SYSTEM DSN连接MySQL时DSN对话框持续弹出，求正确调用方法

Hey there! Let's figure out why that DSN dialog keeps popping up when you try to connect to your MySQL database. The issue usually boils down to incomplete connection information or a small misstep in how you're calling the OpenDatabase method. Here's how to fix it:

1. 确保连接字符串包含所有必要信息 #

The most common reason for the popup is that your connString is missing critical details (like username/password) that the system needs to establish the connection without prompting you. For a SYSTEM DSN, your connection string should look something like this:

connString = "ODBC;DSN=DSNtestwebform;UID=your_db_username;PWD=your_db_password;"

• Replace your_db_username and your_db_password with your actual MySQL credentials. Even if you think the DSN has these saved, SYSTEM DSNs often don't store passwords by default, so explicitly adding them here is key.

2. 确认OpenDatabase参数的正确用法 #

You're already using dbDriverNoPrompt which is correct—it tells Access not to show any prompt dialogs. But this only works if the connection string has everything needed. A common mistake is passing the DSN name as the first argument to OpenDatabase; instead, leave that argument empty and put all connection details in the connString:

Set myDb = wrkODBC.OpenDatabase("", dbDriverNoPrompt, False, connString)

The first parameter is for a database filename (which doesn't apply here for ODBC connections), so leaving it blank is the right approach.

3. 尝试无DSN连接（若DSN配置出问题） #

If you still run into problems, bypassing the SYSTEM DSN entirely with a direct connection string can be more reliable. This way you don't have to rely on the DSN's configuration. Here's an example:

Dim connString As String
connString = "ODBC;DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=your_server_ip;DATABASE=your_database_name;UID=admin;PWD=your_password;"

Set wrkODBC = CreateWorkspace("", "admin", "", dbUseJet)
Set myDb = wrkODBC.OpenDatabase("", dbDriverNoPrompt, False, connString)

• Make sure to use the correct driver name for your MySQL ODBC driver (check ODBC Data Source Manager to find the exact name, e.g., MySQL ODBC 5.3 Unicode Driver for older versions).
• Replace your_server_ip, your_database_name, and credentials with your actual values.

4. 检查SYSTEM DSN的配置是否正确 #

Before diving deeper, confirm that your「DSNtestwebform」DSN is properly set up in the ODBC Data Source Manager:

• Go to Control Panel > Administrative Tools > ODBC Data Sources (64-bit/32-bit) (match your Access version's architecture)
• Navigate to the System DSN tab, select「DSNtestwebform」, and click Configure
• Verify all settings (server address, database name, etc.) are correct and test the connection to ensure it works without prompting for extra info.

完整可用示例 #

Putting it all together, here's a complete code snippet that should work without popups:

Sub ConnectToMySQL()
    Dim wrkODBC As Workspace
    Dim myDb As Database
    Dim connString As String
    
    ' 配置连接字符串
    connString = "ODBC;DSN=DSNtestwebform;UID=admin;PWD=your_actual_password;"
    
    ' 创建ODBC工作区
    Set wrkODBC = CreateWorkspace("", "admin", "", dbUseJet)
    
    ' 无提示打开数据库
    Set myDb = wrkODBC.OpenDatabase("", dbDriverNoPrompt, False, connString)
    
    ' 示例：执行查询确认连接成功
    Dim rs As Recordset
    Set rs = myDb.OpenRecordset("SELECT * FROM your_table LIMIT 1")
    If Not rs.EOF Then
        MsgBox "连接成功！第一条记录值：" & rs(0)
    End If
    
    ' 清理对象
    rs.Close
    myDb.Close
    wrkODBC.Close
    
    Set rs = Nothing
    Set myDb = Nothing
    Set wrkODBC = Nothing
End Sub

核心要点就是：确保连接字符串包含所有必要的认证和连接信息，搭配dbDriverNoPrompt参数使用，就能避开烦人的弹窗了。

内容的提问来源于stack exchange，提问作者Tzwenni