MYSQL 查詢資料表單存在否、新增資料表

最近鑽研vba+mql，或許有人說vba+access 就很棒了，但access有資料上限的天花板，若資料量有一定程度，早早進去sql的領域，才是長遠之計(早點碰sql語法ㄏㄏ)。

做了一個簡單的vba副程式，來產生mysql資料庫的資料表"table"。

但覺得不夠又寫了一個檢查資料表是否存在的vba 

範例說明：

作一個按鈕，放入以下陣列。

陣列說明：

finance_table 是要產生的資料表

field_NAME 是要設定的欄位名稱

field_NAME_S 是要設定的欄位規格

然後透過迴圈方式去檢查表單是否存在，不存在則執行表單產生。

Private Sub CommandButton1_Click()

   finance_table = Array("FINANCE_106", "FINANCE_107", "FINANCE_108", "FINANCE_109", "FINANCE_110", "FINANCE_111")

   

   field_NAME = Array("id", "sheet_name", "stockid", "subject", "data")

      

   field_NAME_S = Array("INT NOT NULL AUTO_INCREMENT", "VARCHAR(45)", "VARCHAR(45)", "VARCHAR(45)", "VARCHAR(45)")

   

   PRIMARY_KEY = "id"

   

   DATA_NAME = "securities_company_data"

   

For i = LBound(finance_table) To UBound(finance_table) Step 1 ' SYBOL ` IN DIFFERENT '

 

   a = ODBC_TABLE("securities_company_data", finance_table(i))

   

   If a = 0 Then

   

        Call odbc_new_table_single_table(DATA_NAME, finance_table(i), field_NAME, field_NAME_S, PRIMARY_KEY)

   

   End If

   

Next

   

   

End Sub

以下為副程式模組：

檢查表單：

	Function ODBC_TABLE_Check(DATABASE, TABLE_NAME)
	On Error GoTo line1
	Application.EnableCancelKey = xlErrorHandler
	Dim Conn As New ADODB.Connection
	Dim rs As New Recordset
	User = "root"
	Password = 20037541
	Set Conn = New ADODB.Connection
	Conn.Open "Driver={MySQL ODBC 8.0 UNICODE Driver};Server=127.0.0.1:3306;Database=" & DATABASE & ";User=" & User & ";Password=" & Password & ";Option=3;"
	Dim Catalog As New ADOX.Catalog
	Set Catalog.ActiveConnection = Conn
	Dim Table As ADOX.Table, Column As ADOX.Column
	strSql = "SELECT COUNT(TABLE_NAME) FROM " & _
	" information_schema.TABLES WHERE TABLE_SCHEMA LIKE " & _
	"'" & DATABASE & "'" & "AND" & _
	" TABLE_TYPE LIKE 'BASE TABLE' AND " & _
	" TABLE_NAME = " & "'" & TABLE_NAME & "'"
	Conn.CursorLocation = adUseClient
	rs.Open strSql, Conn, adOpenStatic
	ODBC_TABLE_OUT = rs.GetRows(rs.RecordCount)
	ODBC_TABLE = ODBC_TABLE_OUT(0, 0)
	rs.Close
	Exit Function
	line1:
	MsgBox Err.Description & " /" & Err.Number
	' Resume
	End Function

view raw gistfile1.txt hosted with ❤ by GitHub

產生表單：

	Sub odbc_new_table_single_table(DATABASE, TABLE_NAME, field_NAME, field_NAME_S, PRIMARY_KEY)
	'設定資料庫、工作表、欄位名稱、格式、索引KEY 做自動產生表
	'generate table in mysql
	User = ?
	Password = ?
	On Error GoTo line1
	Set Conn = New ADODB.Connection
	Conn.Open "Driver={MySQL ODBC 8.0 UNICODE Driver};Server=127.0.0.1:3306;Database=" & DATABASE & ";User=" & User & ";Password=" & Password & ";Option=3;"
	strSql = "CREATE TABLE " & "`" & DATABASE & "`" & "." & "`" & TABLE_NAME & "`" & " ("
	For J = LBound(field_NAME_S) To UBound(field_NAME_S) Step 1
	strSql = strSql + " `" & field_NAME(J) & "` " & field_NAME_S(J) & ","
	Next J
	strSql = strSql + " PRIMARY KEY " & "(`" & PRIMARY_KEY & "`))"
	Sql = strSql
	Conn.Execute Sql 'updata
	strSql = ""
	' cn.Close
	Set Conn = Nothing
	Exit Sub
	line1:
	MsgBox Err.Description
	'Resume
	End Sub

view raw gistfile1.txt hosted with ❤ by GitHub