Matheus Faria
New Member
- Joined
- Feb 5, 2017
- Messages
- 1
Hi all,
Below is a code that will run every time a particular excel file is opened in order to block unauthorized access.
For this the idea is to check the motherboard serial of the computer and verify if it is registered in a mysql database. If it is not registered it closes the file and otherwise autorize the access.
The Workbook_Open function is responsible for autorize or block the access.
The MBSerialNumber function takes the serial of the motherboard.
The ConnectDB function accesses and performs a search of the MySQL database verifying if the serial of the computer is registered.
At this last function I'm having error 3709 (line 4 from bottom to top). I would like your help to solve this problem.
Below is a code that will run every time a particular excel file is opened in order to block unauthorized access.
For this the idea is to check the motherboard serial of the computer and verify if it is registered in a mysql database. If it is not registered it closes the file and otherwise autorize the access.
The Workbook_Open function is responsible for autorize or block the access.
The MBSerialNumber function takes the serial of the motherboard.
The ConnectDB function accesses and performs a search of the MySQL database verifying if the serial of the computer is registered.
At this last function I'm having error 3709 (line 4 from bottom to top). I would like your help to solve this problem.
Code:
Public cn As Variant
Private Sub Workbook_Open()
Call MBSerialNumber
Call ConnectDB
If rs < 1 Then
MsgBox ("Data Security failier, This workbook will close")
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
End Sub
Public Function MBSerialNumber() As String
Dim objs As Object
Dim obj As Object
Dim WMI As Object
Dim sAns As String
Set WMI = GetObject("WinMgmts:")
Set objs = WMI.InstancesOf("Win32_BaseBoard")
For Each obj In objs
sAns = sAns & obj.SerialNumber
If sAns < objs.Count Then sAns = sAns & ","
Next
MBSerialNumber = sAns
End Function
Private Sub ConnectDB()
Set cn = CreateObject("ADODB.Connection")
cn.Open "DRIVER={MySQL ODBC 5.3 ANSI Driver};" & _
"SERVER=xxx.xxx.xx;" & _
"DATABASE=xxx_xxx;" & _
"USER=xxxx_xxx;" & _
"PASSWORD=xxxxxxx"
Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT COUNT(serial) FROM users WHERE serial='" & MBSerialNumber & "', cn, adOpenStatic, adLockOptimistic"";"
cn.Close
Set cn = Nothing
End Sub