inserting a row into mysql database with ms access

[bchaney]

i'm having trouble forming the syntax to add a new row to a mysql database from ms access. i want to be able to check for duplicates then if no duplicates are there then add the new entry. this will be adding two text fields (full name and password). That's it.

any help would be appreciated.

Here is my code i have within a click event to add record:

Private Sub Add_Button_Click()
' check if needed fields are blank
If (IsNull(Full_Name_Text)) Then
msgbox ("Please enter a Full Name"), vbOKOnly, "Data Missing"
Else
If (IsNull(Password_Text)) Then
msgbox ("Please enter a Password"), vbOKOnly, "Data Missing"
Else

' connect via the mysql-connector-odbc-5.1.5-win32.msi file/driver...
Dim MySQLConn, sqlstr, MyRS, c
Set MySQLConn = CreateObject("ADODB.Connection")
MySQLConn.Open = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;DATABASE=mechanicdatabase;USER=****;PASSWORD=****;OPTION=3;"
sqlstr = "select * from mechanicdatabase;"
Set MyRS = MySQLConn.Execute(sqlstr)

Dim strPassword As String
Set NameCheck = CurrentDb.OpenRecordset("SELECT mechanicdatabase.* FROM mechanicdatabase WHERE mechanicdatabase.[Full Name]='" & Me.Password_Text.value & "';", 2)
If NameCheck.EOF Then
'send to md5 code for digesting
strPassword = DigestStrToHexStr(Me.Password_Text.value)
'verification of digesting, delete after testing of code is done
msgbox strPassword, vbOKOnly, "test"

CurrentDb.OpenRecordset ("INSERT INTO mechanicdatabase([Full Name], Password) Values(@[Full Name],'" & strPassword & "'")
'I don't think the above is correct. i did a little programming in vb.net and
'this insert statement just doesn't look right. it's probably something really
'simple.

On Error GoTo Err_Add_Button_Click
DoCmd.GoToRecord , , acNewRec
Exit_Add_Button_Click:
Exit Sub
Err_Add_Button_Click:
msgbox Err.Description
Resume Exit_Add_Button_Click

End Sub

 

[MarkK]

Hey BCHaney, welcome to the forum. BCChilliwack here.
In Access, CurrentDB returns the default DAO.Database. If your tables are in a separate MySQL database then you'll simply never find your tables at CurrentDB.
I think you're going to have to execute all your data access against a ADODB.Connection object like the MySQLConn object you created earlier.
Cheers,

 

[bchaney]

Thanks for your help! i did some more research after you commented on my post and i rewrote some of my insert string and associated code. how does this look?

Dim connstr As String
Dim insertstr As String
Dim rowsaffected As Long
'Dim CheckName As String
'Dim CheckNamestr As String
Set Conn = CreateObject("ADODB.Connection")
Conn = New ADODB.Connection
connstr = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;DATABASE=mechanicdatabase;USER=****;PASSWORD=****;OPTION=3;"
Conn.ConnectionString = connstr
Conn.Open
insertstr = "INSERT INTO mechanicdatabase([Full Name], Password)VALUES('" & Full_Name_Text & "','" & Password_Text & "')"
Conn.Execute insertstr
rowsaffected = Conn.recordsaffected

This last part i'm trying to get working so i can start testing it. i'm trying to return if the rows were actually added to the database. i seen somewhere that this is normally done within ms access and i'm just trying to use that to my advantage. do you know if this is how you properly call that?

Thanks

 

[bchaney]

okay after looking through the internet for a while again, i redid some more of my code and here is what i currently have:

Private Sub Add_Button_Click()

' check if needed fields are blank
If (IsNull(Full_Name_Text)) Then
msgbox ("Please enter a Full Name"), vbOKOnly, "Data Missing"
Else
If (IsNull(Password_Text)) Then
msgbox ("Please enter a Password"), vbOKOnly, "Data Missing"
Else

' connect via the mysql-connector-odbc-5.1.5-win32.msi file/driver...
Dim connstr As String
Dim insertstr As String
Dim rowsaffected As Long
Dim strPassword As String
Dim Conn As New ADODB.Connection

connstr = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=127.0.0.1;DATABASE=mechanicdatabase;USER=****;PASSWORD=****;OPTION=3;"
Conn.ConnectionString = connstr
Conn.Open

insertstr = "INSERT INTO mechanidatatable([Full Name], Password) VALUES('" + Full_Name_Text + "','" + strPassword + "')"
msgbox insertstr, vbOKOnly, "test"
Conn.Execute insertstr

rowsaffected = Conn.recordsaffected

The test run errors out at "Conn.Execute insertstr". Does anybody know why?? it says my syntax is wrong but i've checked over it over and over. i tried this syntax in the actual command line with values instead of variables and it gives me the same error. yet i look on mysql website and they have the same string. am i forgetting something??

Please let me know.

Thanks,

 

[MarkK]

A few observations ...
In the various versions of ADODB I checked I can't find a Connection object that has a RecordsAffected property. How does your code even compile with this line ...

rowsaffected = Conn.recordsaffected

Also, you check for IsNull() on Password_Text, but you never assign that value to your variable strPassword. When you construct you SQL here ...

insertstr = "INSERT INTO mechanidatatable([Full Name], Password) VALUES('" + Full_Name_Text + "','" + strPassword + "')"

... strPassword will always equal vbNullString.
Also, you check for nulls on both Password_Text and Full_Name_Text but you don't abandon the procedure if you find nulls. In all cases the connection will be opened and an insert attempted.
HTH

 

[bchaney]

Thanks for the reply!! While i was waiting for a response i fixed a lot of the problem areas of my code. I also figured out the correct connection string and syntax associated with the "rowsaffected" term. I was going off of someone else's code and it wasn't right for my application which is why it wouldn't work. It took a lot of work but i'm finally getting the hang of linking ms access front end to mysql database. Here is the final addbutton code in case anyone needs any help for it.

Private Sub Add_Button_Click()

On Error GoTo Err_Add_Button_Click

' check if needed fields are blank
If (IsNull(Full_Name_Text)) Then
msgbox ("Please enter a Full Name"), vbOKOnly, "Data Missing"
Full_Name_Text.SetFocus
Exit Sub

Else
If (IsNull(Password_Text)) Then
msgbox ("Please enter a Password"), vbOKOnly, "Data Missing"
Password_Text.SetFocus
Exit Sub

Else
' connect via the mysql-connector-odbc-5.1.5-win32.msi file/driver...
Dim connstr As String
Dim rowsaffected As Long
Dim strPassword As String
Dim Conn As New ADODB.Connection

'send to md5 code for digesting
strPassword = DigestStrToHexStr(Me.Password_Text.value)

connstr = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=127.0.0.1;DATABASE=mechanicdatabase;USER=****;PASSWORD=****;OPTION=3;"
Conn.ConnectionString = connstr
Conn.Open

Conn.Execute "USE mechanicdatabase;"
Conn.Execute "INSERT INTO mechanicdatatable(`Full Name`, Password) VALUES('" & Full_Name_Text & "','" & strPassword & "');", rowsaffected

If rowsaffected = 0 Then
msgbox "Added data '" & Full_Name_Text & "' failed. Try again.", vbOKOnly, "Error"
Exit Sub
Else
msgbox "Data added successfully!", vbOKOnly, "Message"
Full_Name_Text = Nothing
Password_Text = Nothing
If msgbox("Would you like to add another Mechanic?", vbYesNo, "Message") = vbNo Then
DoCmd.Close
Else
User_Entry_Subform.Requery
End If
End If
End If
End If
Exit_Add_Button_Click:
Exit Sub
Err_Add_Button_Click:
msgbox Err.Description
Resume Exit_Add_Button_Click

End Sub

This code works pretty good as it is right now.