Solved

Access and mysql - login system - DAO

Posted on 2013-11-03
8
508 Views
Last Modified: 2013-11-13
Hello.
i go to explain my problem:
I'm creating a database for a no-profit association.
I have found a really good example, but it is whrite with DAO-JET Engenire, for work with MS Sql_Server.
I want use mysql, because it is free and it is good for me.

the example is here:
http://forum.masterdrive.it/attachments/access-79/852d1267532198-login-sicurezza-form-login_permission.zip

Open in new window



this is the code to change.
Option Compare Database
Option Explicit

' -----------------------------------------------------------
' La CONNECTIONSTRING sarebbe da salvare come KRYPTATA
' magari in un REGISTRY ma per semplificare il DEMO
' la riporto quì e genero la FUNZIONE:
'
'   getConnectionString()
'
' -----------------------------------------------------------
Public Const DB_SERVER      As String = "SERVERXP.mdb"

modificato in:

Public Const DB_SERVER      As String = "db4free.net"
' -----------------------------------------------------------
' Salvo in costanti il nome delle TABELLE BASE
' -----------------------------------------------------------
' [_TL]     ELENCO TABELLE DA LINKARE
' [_FP]     FORM PERMISSION
' [_USERS]  ELENCO UTENTI
' -----------------------------------------------------------
Public Const DB_LINKEDTABLE As String = "_TL"
Public Const DB_PERMESSI    As String = "_FP"
Public Const DB_USERTABLE   As String = "_USERS"

' -----------------------------------------------------------
' DataType personalizzato per le variabili AMBIENTE APPLICATIVO
' -----------------------------------------------------------
Public Type APP_AMB_TYPE
    USER_IDUSER             As Long
    USER_NAME               As String
    USER_LEVEL              As Integer
End Type
' -----------------------------------------------------------
' Variabile ambiente con i dati essenziali del LOGIN SALVATI
' -----------------------------------------------------------
Public APP_DATA             As APP_AMB_TYPE

' -----------------------------------------------------------
' METODI PUBLIC DI APPLICATIVO GESTIONE USERS
' -----------------------------------------------------------
Public Function getConnectionString() As String
    getConnectionString = CurrentProject.Path & "\" & DB_SERVER

modificato in:
getConnectionString = "DNS = mioDNs;Uid = miouser;Pwd = miapass;"

End Function

Public Function getUSER(strUSER As String, strPWD As String) As Boolean

    On Error GoTo ERR_USER
    Dim strSQL              As String
    Dim strUSER_C           As String
    Dim strPWD_C            As String
    Dim rs                  As DAO.Recordset
    Dim APP_DB_CONN         As DAO.Database
    
    strUSER_C = strUSER
    strPWD_C = strPWD
    ' ----------------------------------------------------------
    ' Quì metto l'algoritmo di CODIFICA, perchè
    ' nel DB_SERVER non scriverò MAI la PASSWORD in chiaro quindi
    ' il CHECK verrà fatto sul testo CRYTTOGRAFATO...!!!
    ' ----------------------------------------------------------
    strPWD_C = Transform(strPWD_C)
    ' ----------------------------------------------------------
    
    strUSER_C = "'" & Replace(strUSER_C, "'", "''") & "'"
    strPWD_C = "'" & Replace(strPWD_C, "'", "''") & "'"
    
    strSQL = "SELECT * FROM " & DB_USERTABLE & " "
    strSQL = strSQL & "WHERE USER=" & strUSER_C & " AND "
    strSQL = strSQL & "PWD=" & strPWD_C
    
    Set APP_DB_CONN = DBEngine.OpenDatabase(getConnectionString())

    Set rs = APP_DB_CONN.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
    ' Se il RS è vuoto significa LOGIN FALLITO
    If rs.EOF Then
        MsgBox "USER O PWD ERRATI"
        getUSER = False
    Else
        APP_DATA.USER_NAME = strUSER
        APP_DATA.USER_LEVEL = rs.Fields("LEVEL").Value
        APP_DATA.USER_IDUSER = rs.Fields("ID_USER").Value
        getUSER = True
    End If

EXIT_HERE:

    rs.Close
    Set rs = Nothing
    APP_DB_CONN.Close
    Set APP_DB_CONN = Nothing
    
    rs.Close
    Set rs = Nothing

    Exit Function
    
ERR_USER:
    ' ----------------------------------------------------------
    ' Intercetto l'errore derivato da RS/APP_DB_CONN non presenti
    ' ----------------------------------------------------------
    If Err.Number = 91 Then Resume Next
    getUSER = False
    Resume EXIT_HERE
End Function

Public Function getPermissionTable() As Boolean
    On Error Resume Next
    ' ----------------------------------------------------------
    ' Cancello la Tabella PERMESSI nel caso ci fosse
    ' ----------------------------------------------------------
    DoCmd.DeleteObject acTable, DB_PERMESSI
    
    On Error GoTo ERR_PERM
    Dim strSQL                  As String

    ' ----------------------------------------------------------
    ' COPIO IN LOCALE LA TABELLA [_FP]
    ' ----------------------------------------------------------
    strSQL = "SELECT * INTO " & DB_PERMESSI & " "
    strSQL = strSQL + "FROM " & DB_PERMESSI & " IN '" & getConnectionString() & "' "
    strSQL = strSQL + "WHERE ID_USER = " & APP_DATA.USER_IDUSER
    DBEngine(0)(0).Execute strSQL, dbFailOnError
    getPermissionTable = True
EXIT_HERE:
    Exit Function
    
ERR_PERM:
    getPermissionTable = False
End Function

Public Function getLinkedTable() As Boolean
    Dim rs                      As DAO.Recordset
    Dim strConnection           As String
    
    On Error GoTo ERR_LINKED
    ' ----------------------------------------------------------
    ' Cancello la Tabella LINKED nel caso ci fosse prima di ricopiarla
    ' ----------------------------------------------------------
    DoCmd.DeleteObject acTable, DB_LINKEDTABLE

    getLinkedTable = False
    strConnection = getConnectionString()
    
    Dim strSQL                  As String
    ' ----------------------------------------------------------
    ' STRINGA SQL di creazione TABELLA da DB(REMOTO)
    ' Copio il locale la Tabella con l'elenco delle Tabelle
    ' da LINKARE.
    ' ----------------------------------------------------------
    strSQL = "SELECT * INTO " & DB_LINKEDTABLE & " "
    strSQL = strSQL + "FROM " & DB_LINKEDTABLE & " IN '" & strConnection & "'"

    DBEngine(0)(0).Execute strSQL, dbFailOnError
    
    ' ----------------------------------------------------------
    ' APRO UN RS CON L'ELENCO DELLE TABELLE DA LINKARE
    ' CONTENUTO NELLA TABELLA COPIATA [_TL]
    ' ----------------------------------------------------------
    Set rs = DBEngine(0)(0).OpenRecordset(DB_LINKEDTABLE, dbOpenDynaset, dbReadOnly)
    If rs.EOF Then
        Exit Function
    End If
    rs.MoveLast
    rs.MoveFirst
    Do Until rs.EOF
        ' ----------------------------------------------------------
        ' Prima di LINKARLE le cancello per sicurezza
        ' Ho disabilitato la gestione errori proprio per
        ' evitare anomalia in caso la tabella non fosse presente
        ' ----------------------------------------------------------
        DoCmd.DeleteObject acTable, rs.Fields("TABLENAME").Value
        DoEvents
        DoCmd.TransferDatabase acLink, _
                               "Microsoft Access", _
                               strConnection, _
                               acTable, _
                               rs.Fields("TABLENAME").Value, _
                               rs.Fields("TABLENAME").Value
        rs.MoveNext
    Loop
    getLinkedTable = True
    
EXIT_HERE:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Exit Function
    
ERR_LINKED:
    ' ----------------------------------------------------------
    ' Se non trova la Tabella da ELIMINARE riprende ERR=7874
    ' ----------------------------------------------------------
    If Err.Number = 7874 Then Resume Next
    Resume EXIT_HERE
End Function

Public Function SetPermissionProperties(frm As Access.Form) As Boolean
    ' ----------------------------------------------------------
    ' IMPOSTA LE PROPRIETA' DELLA FORM PASSATA
    ' ----------------------------------------------------------
    
    On Error GoTo ERR_PROP
    
    Dim strSQL                  As String
    Dim rs                      As DAO.Recordset
    Dim blAllowAddition         As Boolean
    Dim blAllowEdits            As Boolean
    Dim blAllowDeletions        As Boolean
    
    strSQL = "SELECT * FROM _FP "
    strSQL = strSQL + "WHERE FORM_NAME='" & frm.Name & "' "
    strSQL = strSQL + "AND ID_USER=" & APP_DATA.USER_IDUSER
    
    Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
    blAllowAddition = rs.Fields("pALLOWADDITIONS").Value
    blAllowEdits = rs.Fields("pALLOWDELETIONS").Value
    blAllowDeletions = rs.Fields("pALLOWEDITS").Value
    rs.Close
    Set rs = Nothing
    
    Call FormPermissionRicorsiva(frm, blAllowAddition, blAllowEdits, blAllowDeletions)
    
    Exit Function
    
ERR_PROP:
    MsgBox "Errore grave...!", vbCritical, "AVVISO"
    DoCmd.Quit acQuitSaveNone

End Function

Public Function FormPermissionRicorsiva(mFrm As Access.Form, _
                                        blAllowAddition As Boolean, _
                                        blAllowEdits As Boolean, _
                                        blAllowDeletions As Boolean)
                                        
    Dim ctl                     As Access.Control
    
    mFrm.ALLOWADDITIONS = blAllowAddition
    mFrm.ALLOWDELETIONS = blAllowEdits
    mFrm.ALLOWEDITS = blAllowDeletions

    For Each ctl In mFrm.Controls
        If ctl.ControlType = acSubform Then
            Call FormPermissionRicorsiva(ctl.Form, blAllowAddition, blAllowEdits, blAllowDeletions)
        End If
    Next

End Function

Public Sub msgBoxPermission(frm As Access.Form)
    ' ----------------------------------------------------------
    ' GENERA UN MSGBOX CON L'INFORMATIVA DEI PRIVILEGI
    ' ----------------------------------------------------------
    Dim strMSG              As String
    Dim rs As DAO.Recordset
    Set rs = DBEngine(0)(0).OpenRecordset("SELECT * FROM _FP WHERE FORM_NAME='" & frm.Name & "' AND ID_USER=" & APP_DATA.USER_IDUSER, dbOpenDynaset, dbReadOnly)
    strMSG = "I Privilegi attivi per l'Utente ---> [" & APP_DATA.USER_NAME & "]"
    strMSG = strMSG + vbCrLf
    strMSG = strMSG + "nella Maschera [" & frm.Name & "] sono:" + vbCrLf + vbCrLf
    strMSG = strMSG + "1 - CONSENTI AGGIUNTE = " & IIf(rs.Fields("pALLOWADDITIONS").Value = True, "VERO", "FALSO") + vbCrLf
    strMSG = strMSG + "2 - CONSENTI MODIFICHE = " & IIf(rs.Fields("pALLOWEDITS").Value = True, "VERO", "FALSO") + vbCrLf
    strMSG = strMSG + "3 - CONSENTI ELIMINAZIONE = " & IIf(rs.Fields("pALLOWDELETIONS").Value = True, "VERO", "FALSO") + vbCrLf + vbCrLf
    strMSG = strMSG + "LIVELLO = " & APP_DATA.USER_LEVEL
    
    rs.Close
    Set rs = Nothing
    MsgBox strMSG, vbInformation, "..:: AVVISO ::.."
End Sub

Public Function getAllowOpen(strFROM2OPEN As String) As Boolean
    On Error GoTo ERR_ALLOWOPEN
    ' ----------------------------------------------------------
    ' Funzione che restituisce un BOOLEAN di permissivo
    ' TRUE se la FORM passata rientra nelle FORM concesse
    ' ----------------------------------------------------------
    Dim rs As DAO.Recordset
    Set rs = DBEngine(0)(0).OpenRecordset("SELECT COUNT(*) FROM _FP WHERE FORM_NAME='" & strFROM2OPEN & "' AND ID_USER=" & APP_DATA.USER_IDUSER, dbOpenDynaset, dbReadOnly)
    getAllowOpen = rs.Fields(0) > 0
EXIT_HERE:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Exit Function
    
ERR_ALLOWOPEN:
    getAllowOpen = False
    Resume EXIT_HERE
End Function

Public Function CLOSE_DB()
    ' ----------------------------------------------------------
    ' FUNZIONE CHE RIMUOVE TUTTE LE CONNESSIONI E LE
    ' TABELLE COPIATE IN LOCALE
    ' ----------------------------------------------------------
    On Error GoTo Err_Close
    Dim rs                      As DAO.Recordset
    
    
    Set rs = DBEngine(0)(0).OpenRecordset(DB_LINKEDTABLE, dbOpenDynaset, dbReadOnly)
    rs.MoveLast
    rs.MoveFirst
    Do Until rs.EOF
        DoCmd.DeleteObject acTable, rs.Fields("TABLENAME").Value
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    
ERR_SECOND_STEP:
    On Error Resume Next
    DoCmd.DeleteObject acTable, DB_PERMESSI
    DoCmd.DeleteObject acTable, DB_LINKEDTABLE
    
    Exit Function
    
Err_Close:
    Resume ERR_SECOND_STEP
End Function

Public Function CloseAllForms(Optional strForm As String = vbNullString) As Boolean
    On Error GoTo Err_Close
    Dim n               As Integer
    Dim x               As Integer
    
    n = Forms.Count
    For x = n - 1 To 0 Step -1
        If Forms(x).Name <> strForm Then DoCmd.Close acForm, Forms(x).Name
    Next
    CloseAllForms = True

EXIT_HERE:
    Exit Function
Err_Close:
    CloseAllForms = False
    Resume EXIT_HERE
End Function

Open in new window


I have Office 2007, but the files are .mdb.
I have install OBDC drivers, 5.52, and 3.x
I have windows 7 64bit.


A second possible solution may be:
use a local file, for login, and make only the link with table on mysql.
the "server" file, will be shared with googledrive, or similar... (after cripted and make password...)


Make it is possible for who have the knowledge..


Thanks for all that help!
0
Comment
Question by:Schakalaka
  • 3
  • 3
  • 2
8 Comments
 
LVL 84
ID: 39621424
MS SQL Server Express editions are also free:

http://www.microsoft.com/en-us/sqlserver/editions/2012-editions/express.aspx

That said, it looks like your example code uses linked tables, so just create the linked tables to your MySQL database, and you should be able to work it out from there.

To create linked tables, you first must be sure that the proper drivers are installed on the workstations. I'm not sure of the version of MySQL you're using, so it's hard to provide further information on that, but the driver downloads for MySQL are here:

http://www.mysql.com/products/connector/

You'd probably use the ODBC drivers for this.

Once you do that, you'd then create links. To do that, click the External Data - Import group, and click the "More" option. Select ODBC. Select the "create linked table" option, and then follow the prompts from there to create your linked tables.

Once you've done that, you can then use those linked tables in your application.

Note there are some gotchas regarding the use of server-based data (for example, if you've used any Access-specific syntax in your queries you'd have to rewrite them), so be aware of this.

Here's a link to an article about MS SQL table links, but the process to connect to MysQL is the same:

http://office.microsoft.com/en-us/access-help/import-or-link-to-sql-server-data-HA010200494.aspx?CTT=1
0
 

Author Comment

by:Schakalaka
ID: 39622154
hello.
thanks for reply..
1)  i wan't  use MS_server, because there isn't free host for this database.
so, i'll use db4free.net that use mysql. i'll use Access as front end and Mysql as back end.
so, i'll not the only that use this database. 2 pc in 2 differents offices.

2) i've install all ODBC that i can: 5.52 ANSI, UNiCODE, 32, and 64 bit, and 3.1. All.
infact, this code work perfect:
Public Sub testDsnless()
    Dim dbMySQL As DAO.Database
    Dim cnMySQL As DAO.Connection
    Dim rsMySQL As DAO.Recordset
    Dim sDSN As String, qrMySQL As String
    
sDSN = "DRIVER={MySQL ODBC 5.2 ANSI Driver};PORT=3306;DATABASE=caritasworld;SERVER=db4free.net;UID=caritascdd;PWD=xxx;Option=3;"
    Set dbMySQL = OpenDatabase("", False, False, sDSN)
    
    qrMySQL = "SELECT * FROM _USERS;"
     Set rsMySQL = dbMySQL.OpenRecordset(qrMySQL, dbOpenDynaset, dbSeeChanges)
    rsMySQL.AddNew
    rsMySQL!TABLENAME = NUOVATABELLA
    rsMySQL.Update
End Sub

Open in new window


3) i try to make a specific request:

I need this code:
Public Function getLinkedTable() As Boolean
    Dim rs                      As DAO.Recordset
    Dim strConnection           As String
    
    On Error GoTo ERR_LINKED
    ' ----------------------------------------------------------
    ' Cancello la Tabella LINKED nel caso ci fosse prima di ricopiarla
    ' ----------------------------------------------------------
    DoCmd.DeleteObject acTable, DB_LINKEDTABLE

    getLinkedTable = False
    strConnection = getConnectionString()
    
    Dim strSQL                  As String
    ' ----------------------------------------------------------
    ' STRINGA SQL di creazione TABELLA da DB(REMOTO)
    ' Copio il locale la Tabella con l'elenco delle Tabelle
    ' da LINKARE.
    ' ----------------------------------------------------------
    strSQL = "SELECT * INTO " & DB_LINKEDTABLE & " "
    strSQL = strSQL + "FROM " & DB_LINKEDTABLE & " IN '" & strConnection & "'"

    DBEngine(0)(0).Execute strSQL, dbFailOnError
    
    ' ----------------------------------------------------------
    ' APRO UN RS CON L'ELENCO DELLE TABELLE DA LINKARE
    ' CONTENUTO NELLA TABELLA COPIATA [_TL]
    ' ----------------------------------------------------------
    Set rs = DBEngine(0)(0).OpenRecordset(DB_LINKEDTABLE, dbOpenDynaset, dbReadOnly)
    If rs.EOF Then
        Exit Function
    End If
    rs.MoveLast
    rs.MoveFirst
    Do Until rs.EOF
        ' ----------------------------------------------------------
        ' Prima di LINKARLE le cancello per sicurezza
        ' Ho disabilitato la gestione errori proprio per
        ' evitare anomalia in caso la tabella non fosse presente
        ' ----------------------------------------------------------
        DoCmd.DeleteObject acTable, rs.Fields("TABLENAME").Value
        DoEvents
        DoCmd.TransferDatabase acLink, _
                               "Microsoft Access", _
                               strConnection, _
                               acTable, _
                               rs.Fields("TABLENAME").Value, _
                               rs.Fields("TABLENAME").Value
        rs.MoveNext
    Loop
    getLinkedTable = True
    
EXIT_HERE:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Exit Function
    
ERR_LINKED:
    ' ----------------------------------------------------------
    ' Se non trova la Tabella da ELIMINARE riprende ERR=7874
    ' ----------------------------------------------------------
    If Err.Number = 7874 Then Resume Next
    Resume EXIT_HERE
End Function

Open in new window


writed with DAO, working in MS2007..
in this code, i  have a local table with the list of remote table that i want linking...
All this, will be after a login (if you have see the full example), automatic.
thanks if somebody can do something.
0
 
LVL 84
ID: 39627030
I'm not really sure what your question is at this point. Could you please restate it?

I think you want some way to log into the MySQL server? But I'm not sure ...

Note that you can store the credentials when you create the links, if that would be better. To do that, be sure to check the "save password" box when you initially create the links.

If you want to use DSNless table linking, see this article:

http://www.accessmvp.com/DJSteele/DSNLessLinks.html
0
 

Author Comment

by:Schakalaka
ID: 39631042
ok, i try..
yes.. i need the code with DAO, for link table.
In my FE file, i have a table that contain the name of remote table to link.
The remote table, are on mysql, so i need use DAO-> ODBC tecnology..
So, i need open a connection to the remote database, then link the listed tables.
i don't know how do this...
thanks
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 84
ID: 39631103
The link I provided above shows a very complete example of linking to a database (and in fact it's a MySQL database in the example). Have a look at the link and then apply that to your project.
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 39631810
This is the way I do it. There is a hidden table called MSysObjects that can show you what the link looks like.

Public Function Link_ODBC_Tables() As Boolean

Link_ODBC_Tables = False

Dim Cmd As String

Dim Msg As String
Dim Title As String

Dim ConnStr As Variant
Dim MachIP As String
Dim Path As String

Dim OpSys As String


Dim SQL As String
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim TblAttach As DAO.TableDef
Dim I As Integer

'build what the connection string looks like. You can see it in the MSysObjects hidden table.
ConnStr = "ODBC;DSN=RemoteDSN;DB=localhost:" & Path & ";UID=UserName;PWD=P@ssword"

'I have a table which lists off the tables to link.
SQL = "Select ForeignName " & _
    "From List_Of_Tables"

Set DB = CurrentDb()
Set RS = DB.OpenRecordset(SQL)

If RS.EOF = False Then
    RS.MoveFirst
Else
    MsgBox "No Data", vbExclamation, "Exiting Function"
    Set RS = Nothing
    Set DB = Nothing
    Exit Function
End If

I = 1
On Error Resume Next
Do While RS.EOF = False
    
    Set TblAttach = DB.CreateTableDef(RS!ForeignName, dbAttachSavePWD + dbReadOnly)
    With TblAttach
        .Connect = ConnStr
        .SourceTableName = RS!ForeignName
        .Name = RS!ForeignName '& "_Blank"
        
    End With
    DB.TableDefs.Append TblAttach
    Set TblAttach = Nothing

    I = I + 1
    
    If I Mod 10 = 0 Then Debug.Print RS!ForeignName
                                      
    RS.MoveNext
    
Loop

RS.Close

Set RS = Nothing
Set DB = Nothing

'This is a test to see if the linking worked.
If DCount("*", "MSysObjects", "ForeignName = 'First_Table_Name'") > 0 Then
    Link_ODBC_Tables = True
Else
    Link_ODBC_Tables = False
    Msg = "There was an issue linking to  " & vbCrLf & _
        "the tables.  " & vbCrLf & _
        "Please try again."
    Title = "Linking Error!"
    MsgBox Msg, vbExclamation + vbOKOnly + vbSystemModal, Title
    Link_ODBC_Tables = False
    Exit Function
End If

End Function

Open in new window

0
 

Author Closing Comment

by:Schakalaka
ID: 39645667
OK!! it work good! thanks!!
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39646054
Can I ask why you gave me a B grade?
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now