VB code to determine table in MySQL

[Dodong]

Last month 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

Any reply to the above problem is highly appreciated

[Killer42]

Last month 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

Any reply to the above problem is highly appreciated

I'm not sure I understand just what the problem is. Is this code failing to find the table, or what?

Here are some questions for you - not sure whether they will help you or not, but they'll help me (and possibly others) better understand the situation. I think in terms of MS Access, so may be simply speaking from ignorance.

1. Is mydata the name of the database, or the table, or both, or isn't there such a distinction in MySQL?
2. Will .Fields("TABLE" ).Value definitely return lowercase? If not, there could be a problem with the match.
3. This one is probably silly, but is it possible that it needs a semicolon on the end of the SQL string?
4. Am I correct in assuming that DbNameMySQL is a string? It doesn't seem to match your naming standard, from what little I can see here.

By the way, you really should put [CODE] tags around your code - makes it easier to read.

[Dodong]

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.

[Killer42]

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.

Ahah! So it's either .Fields, or the field name TABLE which is giving it a problem. (The actual error message is a critical piece of information in such an investigation.)

Seems to me, that has nothing to do with whether your table exists. And we both know that the .Fields collectrion has to be valid. So it would seem to follow that there is no field called TABLES returned by your query "SHOW TABLES FROM mydata". Can you check on this?

[Dodong]

"SHOW TABLES FROM mydata" has no error. In fact, I have tested it using "Msgbox .Recordcount" and it accurately counted the number of tables in "mydata" database.

[Killer42]

"SHOW TABLES FROM mydata" has no error. In fact, I have tested it using "Msgbox .Recordcount" and it accurately counted the number of tables in "mydata" database.

Yes, but what is the exact list of fields that it returns? The subsequent error message seems to imply that there is no field called TABLES.

[Dodong]

Thanks Killer42 for your time in taking a look at my problem. My ultimate objective in my code is to determine if a certain table already exists in MySQL database. I am new to MySQL and just don't know what is wrong with my code. I hope that you can help me.

[Killer42]

Thanks Killer42 for your time in taking a look at my problem. My ultimate objective in my code is to determine if a certain table already exists in MySQL database. I am new to MySQL and just don't know what is wrong with my code. I hope that you can help me.

I'm not familiar with the SHOW TABLES statement myself, but acording to the MySQL website it only returns a single column. Perhaps you could try referring to it as index (1) rather than by the name "TABLE".

[terreaultguy]

Last month 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

Any reply to the above problem is highly appreciated

Replace that line:
If LCase(DbNameMyS QL) = .Fields("TABLE" ).Value Then
With this line:
If LCase(DbNameMyS QL) = .Fields("Tables _in_mydata").Va lue Then

The problem is that aldo your EXECUTE is valid it yealds only one field
called "Tables_in_myda ta" NOT "TABLE"

THANKS FOR YOUR CODE it has helped me to find the code i neaded to list
my tables in my database