distribute spreadsheet to several users to write to MySQL

[dingleberry]

So, I would like to distribute a spreadsheet to several users. The spreadsheet (xlsm) has a button which when clicked calls a subroutine to write to a MySQL database that is shared on our local network. All users have access to the MySQL IP Address but not all users have the ODBC driver installed. In fact, many do not. I like the idea of being able to send out a spreadsheet (because most users are familiar with this and it is much easier than writing a web based front end using php) to get information from users but I don't know how to get around this ODBC driver issue. Can anyone provide some direction or help on this? I have attached the VBA code below

  Dim oConn As ADODB.Connection

    Private Sub ConnectDB()

        Set oConn = New ADODB.Connection

        oConn.Open "DRIVER={MySQL ODBC 5.2 Unicode Driver};SERVER=10.144.205.198;DATABASE=store_survey;USER=****;PASSWORD=********;Option=3"

    End Sub

    Function esc(txt As String)

        esc = Trim(Replace(txt, "'", "\'"))

    End Function


    
    'Dim rs As ADODB.Recordset

    Private Sub InsertData()

        Set rs = New ADODB.Recordset

        ConnectDB

        With wsBooks

            For rowCursor = 2 To 11
            
            strSQL = "INSERT INTO `store_survey`.`tutorial` (`title`, `author`, `price`) VALUES ('" & esc(.Cells(rowCursor, 1)) & "', '" & esc(.Cells(rowCursor, 2)) & "', '" & esc(.Cells(rowCursor, 3)) & "');"
                
            'MsgBox strSQL

                rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic

            Next

        End With

    End Sub
    
Sub Button3_Click()
Call InsertData
End Sub

 

[Andrzejek]

I don't know about the ODBC driver, probably you have to install one on every machine, but...
Your Function esc is a Public Function, accepts text, but returns Variant, I hope you are aware of this.

Also, consider:
[tt]
strSQL = "INSERT INTO `store_survey`.`tutorial` (`title`, `author`, `price`) VALUES ('" & esc(.Cells(rowCursor, 1)) & "', '" & esc(.Cells(rowCursor, 2)) & "', '" & esc(.Cells(rowCursor, 3)) & "');"

rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic

oConn.Execute strSQL
[/tt]

And you have to trust your users to have `title`, `author`, and `price` in appropriate columns, so they cannot insert or delete any columns. Pretty ‘dangerous’ approach, IMHO

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dingleberry]

Thanks Andy,
I appreciate the pointers. I really haven't refined the code at all yet. As a matter of fact, that code is from a tutorial I was following to see if writing to MySQL was even feasable. I see it is, but the connection is where the hiccup is. If I can figure out a way to proceed down this path, I will implement your suggestions but that will only be if I can trust that the users are able to write to the database.

 

[SkipVought]

Hi,

In our company there are users that I support that 1) need access to a particular server and 2) must also have an ODBC driver that is properly configured as well.

Unless you are prepared to write an executable to perform the latter, which is not germane to this forum, you must at least give the user a step by step instruction of how to configure the ODBC driver for your application.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]