Reputation: 99
I Have created a ODBC DSN to MySQL called "TEST". The table in the MySQL Database is Called "DETAILS" and the columns are called:
ID (Auto Number) | NAME | SURNAME | DATE|
In Excel, I have a userform with the following controls: Textbox1 = NAME, Textbox2 = SURNAME and DTPicker = DATE + a Command Button Called SAVE.
Is there a way that I can save this data directly to the MySQL Database by pushing the command button? I have the ODBC DSN connection to access the database
UPDATE
This is the code I Have:
Private Sub CommandButton1_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
strCon = "Driver={MySQL ODBC 5.1 Driver};SERVER=SERVERNAME;DATABASE=DATABASE;UID=USERID;PWD=PASSWORD"
Set cn = CreateObject("ADODB.Connection")
cn.Open strCon
Set rs = CreateObject("adodb.recordset")
rs.Open "INSERT INTO MYTABLE (ID, Name, Surname, Date) VALUES (Textbox1.Value, Textbox2.value, DTPicker1.Value)", cn
rs.Close
End Sub
But It doesn't allow me to reference the CONTROLS, Textboxes and DTPicker
Upvotes: 0
Views: 4921
Reputation: 99
Here is the Code to Make this work:
Private Sub CommandButton1_Click()
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Set c = New ADODB.Connection
Dim rw As Integer
c.Open "DSN=DSNName"
sq = "Insert into MYTABLE (NAME, SURNAME, DATE) values ('" & TextBox1.Value & "','" & TextBox2.Value & "','" & DTPicker1.Value & "')" Set r = c.Execute(sq)
c.Close
End Sub
Upvotes: 0
Reputation: 8003
Give this a shot:
Private Sub CommandButton1_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
strCon = "Driver={MySQL ODBC 5.1 Driver};SERVER=SERVERNAME;DATABASE=DATABASE;UID=USERID;PWD=PASSWORD"
Set cn = CreateObject("ADODB.Connection")
cn.Open strCon
Set rs = CreateObject("adodb.recordset")
rs.Open "INSERT INTO MYTABLE (Name, Surname, Date)" _
" VALUES (" & Textbox1.Value & ", " & Textbox2.value _
", " & DTPicker1.Value &")", cn
rs.Close
End Sub
Upvotes: 1