VBA create new table mysql

[ebbrey]

I have one more problem i was hoping to get solved today

I am using this code to create a new table in my mysql database:

Sub excelmysql()

' Connection variables
Dim conn As New ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String

' Table action variables
Dim i As Long ' counter
Dim SQLStr As String ' SQL to perform various actions
Dim table1 As String
Dim field1 As String, field2 As String
Dim rs As ADODB.Recordset
Dim vtype As Variant


Server_Name = "127.0.0.1" ' Enter your server name here - if running from a local computer use 127.0.0.1
Database_Name = "my_database" ' Enter your database name here
User_ID = "root" ' enter your user ID here
Password = "mypass" ' Enter your password here

Set conn = New ADODB.Connection
conn.Open "DRIVER={MySQL ODBC 5.1 Driver}" _
& ";SERVER=" & Server_Name _
& ";DATABASE=" & Database_Name _
& ";UID=" & User_ID _
& ";PWD=" & Password _
& ";OPTION=16427" 


vtype = Array("varchar(255)", "Text", "LongText", "Int(10)", "Float", "Double", "Date", "Time") ' array of commonly used MySQL variable types
table1 = "P" & Range("H1").Value
field1 = "field1text"
field2 = "field2text"


SQLStr = "CREATE TABLE " & table1 & "(" _
& field1 & " " & vtype(0) & "," _
& field2 & " " & vtype(4) _
& ")"
conn.Execute SQLStr


On Error Resume Next
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
On Error GoTo 0
End Sub

This code is working, but forces me to have some text infront of the name of the new table name. So when H1 = 123 the new table name is P123.
I have tried using
table1 = Range("H1").Value ' give me a System Error &H80040E14 (-2147217900)
table1 = "" & Range("H1").Value ' gives me the same System Error

and i cant use numbers either if i could have a standard first number.

table1 = "1" & Range("H1").Value ' same System Error
table1 = 1 & Range("H1").Value ' same System Error

What am i doing wrong? I only want the new table name to be the numbers in H1.

Thx in advance for any view or reply!

 

[Ruddles]

Wrap the table name in square brackets? Try:-

table1 = "[" & Range("H1").Value & "]"

 

[ebbrey]

No luck with the brackets. Gave me system error.

But if i remove the brackets and insert some text in H1 this works:
table1 = "" & Range("H1").Value & ""

But not with numbers.

 

[Norie]

Why would you want to use numbers when creating a table?

Anyway the correct syntax is CREATE TABLE `<tablename>`...

ie the tablename is enclosed in backward single quotes (don't know if that's the right name but it's something like that.

This worked for me.

Option Explicit
 
Sub excelmysql()
' Connection variables
Dim conn As New ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
    ' Table action variables
Dim i As Long    ' counter
Dim strSQL As String    ' SQL to perform various actions
Dim table1 As String
Dim field1 As String, field2 As String
Dim rs As ADODB.Recordset
Dim vtype As Variant

Server_Name = "localhost"    ' Enter your server name here - if running from a local computer use 127.0.0.1

Database_Name = "test"    ' Enter your database name here

User_ID = "root"    ' enter your user ID here

Password = "<the password>"    ' Enter your password here
 
    Set conn = New ADODB.Connection

    conn.Open "DRIVER={MySQL ODBC 5.1 Driver}" _
              & ";SERVER=" & Server_Name _
              & ";DATABASE=" & Database_Name _
              & ";UID=" & User_ID _
              & ";PWD=" & Password _
              & ";OPTION=16427"
 
    vtype = Array("varchar(255)", "Text", "LongText", "Int(10)", "Float", "Double", "Date", "Time")    ' array of commonly used MySQL variable types
    table1 = "P" & Range("H1").Value

    field1 = "field1text"

    field2 = "field2text"

    strSQL = "CREATE TABLE `" & table1 & "` (`" _
             & field1 & "` " & vtype(0) & ",`" _
             & field2 & "` " & vtype(4) _
             & ")"
 
    conn.Execute strSQL

    On Error Resume Next

    rs.Close

    Set rs = Nothing

    conn.Close

    Set conn = Nothing

    On Error GoTo 0

End Sub

By the way I use strSQL because for me it's telling me it's a string and it's for an SQL statement.

Just a personal thing I suppose.

 

[ebbrey]

Why would you want to use numbers when creating a table?
<tablename><the password="">

Because im trying to create a database for projects, where the tablenames are projectnumbers. The idea was that this would make it easier for me to link the data to web pages and to excel but its been throwing me a few problems so im considering using names instead.

Thx for your code , and regarding the naming, i agree with you, but i dont have enough experience in this yet so when i get some code here and some code there i end up with some weird naming.
</the></tablename>

 

[Ruddles]

Please don't use your data as table or field names: it isn't easier by any means and whenever someone does that, it makes the database fairies cry.

Use a single table called PROJECTS and have a field called PROJECT_NUMBER, then use a WHERE clause to extract the correct record(s).

 

[ebbrey]

Use a single table called PROJECTS and have a field called PROJECT_NUMBER, then use a WHERE clause to extract the correct record(s).

Yes, and this is what i went for