Excel and mySQL for security?

[johnmidcap]

Good evening. I am totally new to connecting to mySQL and have been trying to connect for a day now. I appreciate any guidance.

I have 5.1.6 and set this driver up on my computer. No problem connecting to the mySQL database.

I am trying to write VBA in an Excel spreadsheet that requires a user to enter a password, which is then verified against a mySQL db, before opening the spreadsheet. I am trying to connect using the following code:


Private Sub Worksheet_Activate ()
Dim sh1 As Sheet1
Dim SQL As String

Set conn = New ADODB.Connection ‘global variable
conn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=IP; DATABASE=test;UID=test;PWD=test; OPTION=3" ‘IP and test is substituted for actual information
conn.open
Set sh1 = Excel.Worksheets(1)

Set rsCard = New ADODB.Recordset ‘global variable
SQL="select * from tblusers WHERE chrUser = ‘test’"
rsCard.CursorLocation = adUseServer
rsCard.Open SQL, conn
If Not rsCard.BOF
Sh1.Cells(0, 1).Value = rsCard. Fields(0)
End If
End Sub

I can access the mySQL db when setting up the link in User DSN and System DSB, but, when I run it I am getting an error that it cannot find the database. Any reason why this is happening?

Also, has anyone else used the above methodology to protect access to an Excel spreadsheet. The reason why I am trying this is because I do not want people that leave the organization to be able to use the spreadsheet. It is part of our IP.

Thanks for your assistance,

John

 

[ingresman]

when I run it I am getting an error that it cannot find the database

Can you say exactly what error you get ?

 

[gtaborda]

Hi, I am not sure if this helps, but this is how I connect to mySQL database from Excel and works great

dsn_file=fullfilepath_with_variables
(hidden file so they can't copy it and take it, although anyone with enough knowledge would be able to change the variables and use it anyway....)
Also I am able to have different connection DNS this way and change servers...

Open dsn_file For Input As #1
Line Input #1, tmpODBC
Line Input #1, tmpDRIVER
Line Input #1, tmpUID
Line Input #1, tmpPWD
Line Input #1, tmpOPTION
Line Input #1, tmpPORT
Line Input #1, tmpDATABASE
Line Input #1, tmpSERVER
Close #1

connection_string = tmpDRIVER & ";" & tmpSERVER & ";" & tmpDATABASE & ";" & tmpUID & ";" & tmpPWD & ";" & tmpOPTION

Set oConn = New ADODB.Connection
oConn.Open connection_string

The only string difference I see between your code and mine is that I use "DRIVER=MySQL ODBC 5.1 Driver" instead of "DRIVER={MySQL ODBC 5.1 Driver}"
Could that be the reason ?

Anyway, hope that helps somehow...