CODEWITHSUNDEEP
mysqlsqlexcelexport
Ruan du Preez
Ruan du Preez

Reputation: 99

Add or Update data from Excel to MYSQL

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

Answers (2)

Ruan du Preez
Ruan du Preez

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

user2140261
user2140261

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

Related Questions