在VB6中连接到MySQL ODBC时使用ADODB.Recordset.Index

正如您所发布的那样，由于数据库不支持Seek或Index，因此就此而言，您有点不走运。

但是，如果您确实必须使用seek / index ，则建议将SQL查询的结果导入到本地.mdb文件中，然后使用该文件使记录集像其余代码所期望的那样工作。
从性能的角度来看，这有点邪恶，从长远来看，最好替换所有的搜索和索引调用，但这至少可以节省您的编码时间。

要创建本地数据库，您可以执行以下操作：

Function dimdbs(Temptable as String)
Dim tdfNew As TableDef
Dim prpLoop As Property
Dim strDbfullpath As String
Dim dbsn As Database
Dim idx As Index
Dim autofld As Field
'PARAMETERS: DBFULLPATH: FileName/Path of database to create

strDbfullpath = VBA.Environ$("TMP") & "\mydb.mdb"
If Dir(strDbfullpath) <> "" Then
           Set dbsn = DBEngine.Workspaces(0).OpenDatabase(strDbfullpath)
Else
    Set dbsn = DBEngine.CreateDatabase(strDbfullpath, dbLangGeneral)
End If

Set tdfNew = dbsn.CreateTableDef(Temptable)
With tdfNew
      ' Create fields and append them to the new TableDef
      ' object. This must be done before appending the
      ' TableDef object to the TableDefs collection of the
      ' database.
        Set autofld = .CreateField("autonum", dbLong)
        autofld.Attributes = dbAutoIncrField
        .Fields.Append autofld
        .Fields.Append .CreateField("foo", dbText, 3)
        .Fields.Append .CreateField("bar", dbLong)
        .Fields.Append .CreateField("foobar", dbText, 30)
        .Fields("foobar").AllowZeroLength = True
        Set idx = .CreateIndex("PrimaryKey")
        idx.Fields.Append .CreateField("autonum")
        idx.Unique = True
        idx.Primary = True
        .Indexes.Append idx

        Debug.Print "Properties of new TableDef object " & _
         "before appending to collection:"

      ' Enumerate Properties collection of new TableDef
      ' object.
      For Each prpLoop In .Properties
         On Error Resume Next
         If prpLoop <> "" Then Debug.Print "  " & _
           prpLoop.Name & " = " & prpLoop
         On Error GoTo 0
      Next prpLoop

      ' Append the new TableDef object to the Northwind
      ' database.
      If ObjectExists("Table", Temptable & "CompletedCourses", "Userdb") Then
        dbsn.Execute "Delete * FROM " & Temptable & "CompletedCourses"
      Else
      dbsn.TableDefs.Append tdfNew
      End If



      Debug.Print "Properties of new TableDef object " & _
         "after appending to collection:"

      ' Enumerate Properties collection of new TableDef
      ' object.
      For Each prpLoop In .Properties
         On Error Resume Next
         If prpLoop <> "" Then Debug.Print "  " & _
           prpLoop.Name & " = " & prpLoop
         On Error GoTo 0
      Next prpLoop

    End With
    Set idx = Nothing
    Set autofld = Nothing
End Function

要稍后找到并删除它，可以使用以下方法：

Function DeleteAllTempTables(strTempString As String, Optional tmpdbname As String = "\mydb.mdb", Optional strpath As String = "%TMP%")
Dim dbs2 As Database
Dim t As dao.TableDef, I As Integer
Dim strDbfullpath
If strpath = "%TMP%" Then
    strpath = VBA.Environ$("TMP")
End If
strDbfullpath = strpath & tmpdbname
If Dir(strDbfullpath) <> "" Then
    Set dbs2 = DBEngine.Workspaces(0).OpenDatabase(strDbfullpath)
Else
    Exit Function
End If
        strTempString = strTempString & "*"
      For I = dbs2.TableDefs.Count - 1 To 0 Step -1
         Set t = dbs2.TableDefs(I)
         If t.Name Like strTempString Then
            dbs2.TableDefs.Delete t.Name
         End If
      Next I
dbs2.Close

End Function

要从SQL导入到该数据库，您必须获取记录集并使用for循环添加每条记录（除非它是固定的ODBC连接，我认为您可以直接导入，但我没有示例代码）

Dim formrst As New ADODB.recordset
Set mysqlconn = New ADODB.Connection
Dim dbsRst As recordset
Dim dbs As Database
'opens the ADODB connection to my database
Call openConnect(mysqlconn)
'calls the above function to create the temp database
'Temptable is defined as a form-level variable so it can be unique to this form 
'and other forms/reports don't delete it
Call dimdbs(Temptable)
Me.RecordSource = "SELECT * FROM [" & Temptable & "] IN '" & VBA.Environ$("TMP") & "\mydb.mdb'"
Set dbs = DBEngine.Workspaces(0).OpenDatabase(VBA.Environ$("TMP") & "\mydb.mdb")
Set dbsRst = dbs.OpenRecordset(Temptable)

Set formrst.ActiveConnection = mysqlconn
Call Selectquery(formrst, strSQL & strwhere & SQLorderby, adLockReadOnly, adOpenForwardOnly)
With formrst
    Do Until .EOF
            dbsRst.AddNew
            dbsRst!foo = !foo
            dbsRst!bar = !bar
            dbsRst!foobar = !foobar
            dbsRst.Update
        .MoveNext
    Loop
    .Close
End With
dbsRst.Close
Set dbsRst = Nothing
dbs.Close
Set formrst = Nothing

您必须在保存时或最后在窗体关闭时重新导入数据，但是至少这只需要一个SQL语句，或者您可以直接通过ODBC连接来导入。

这远未达到最佳效果，但至少您可以将所有这些代码包含在一个或两个额外的函数调用中，并且不会干扰原始逻辑。

我必须对Allen Browne表示敬意，我从各处收集了这段代码，但是大多数我的代码可能来自于他的网站或受其启发（http://allenbrowne.com/）