34 days ago I posted a question on what is the VB code to know if a table is existing in MySQL, but I have not receive any reply. I hope that the following code will help the reader determine what is the problem after the "Do While Not .EOF" code.

Private Sub CheckTable()
Dim gcnDb As ADODB.Connection
Dim grsRs As ADODB.Recordset
Set gcnDb = New ADODB.Connection
Set grsRs = New ADODB.Recordset
DbNameMySQL = "mydata"
sTemp = "DATABASE=" & DbNameMySQL & ";"
With gcnDb
.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=" & ServerMySQL & ";" & sTemp & _
"USER=" & UserNameMySQL & ";" & _
"PASSWORD=" & PasswordMySQL & ";" & _
"PORT=" & PortMySQL & ";" & _
"OPTION=" & OptionMySQL & ";"
.CursorLocation = adUseClient
.Open
End With
Set grsRs = gcnDb.Execute("SHOW TABLES FROM " & DbNameMySQL)
With grsRs
If Not .RecordCount = 0 Then
.MoveFirst
Do While Not .EOF
If LCase(DbNameMySQL) = .Fields("TABLE").Value Then
MsgBox "Table already exist!"
Exit Do
End If
.MoveNext
Loop
End If
End With
grsRs.Close
Set grsRs = Nothing
gcnDb.Close
End Sub

Are you experiencing a defined error? If so, can you please post it? Or does the code exhibit undesired behaviour? If so, can you please describe it?

I am sorry I failed to mention that DbNameMySQL and sTemp were declared as public variable strings in the module. MySQL does not forbid creating a table name with the same name as the database. In my code, I was testing if it can detect the presence of "mydata" table. The error returned is "Item can not be found in the collection corresponding to the requested name or ordinal" and it points to the " .Fields("TABLE").Value" clause as the culprit.