UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dsn-less Connection String, Access 2016 and MySQL 5.5.57    
 
   
JeffAspiringProg...
post Oct 24 2017, 06:06 AM
Post#1



Posts: 3
Joined: 24-October 17



Good Day all
I know that the DSN Less topic is talked about alot, but I am just not finding the Google answers so I'm hoping someone here has dealt with this.

I have just two questions?
I have converted the Access Backend to MYSQL for my job and everything is working very well. I do know that if you create a new access database and import the tables, anyone can just go into the tables because it remembers the password from when I linked it via the ODBC.
So the question I'm asking is, if I create the DSN Less connection instead and call it via the form only when the data capturer saves the record. That means if anyone imports the table now, they won't be able to go into the tables because the password is called from the form instead? Am I on the right track here?

If I am on the right track, then I would really need guidance on what the MYSQL VBA code is? I have tested the mysql connection string in the vb.net in visual studio and it works without a problem.
Trying to amend the code to be used in Access VBA is trickier than I thought and I get errors like you won't believe?
This is the vb.net code that works (Just an example that opens the connection to the database and then closes it once it is accepted)[Instead of the checking of employee name, the code would actually be to save record]:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim cn As New MySqlConnection
Dim cmd As New MySqlCommand
Dim dr As MySqlDataReader
cn.ConnectionString = "server = SERVERNAME; user id = USERNAME; password = PASSWORD; database = DATABASE"
cmd.Connection = cn
cn.Open()
cmd.CommandText = "select EmployeeName,EmployeePassword from tblemployeeslogin where EmployeeName = '" & txtusername.Text & "' and EmployeePassword = '" & txtpassword.Text & "'"
dr = cmd.ExecuteReader
If dr.HasRows Then
ClientForm.Show()
Else
MsgBox("Invalid Username or Password")
End If
cn.Close()
End Sub

If anyone here knows what I'm talking about here, it would be much, much appreciated.

Jeff
Go to the top of the page
 
GroverParkGeorge
post Oct 24 2017, 06:27 AM
Post#2


UA Admin
Posts: 31,240
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

There is a lot of very good information in this blog article.

See if it can address some of your concerns regarding security. Ben specifically mentions MySQL in his discussion, so that's helpful to you.

--------------------
Go to the top of the page
 
cheekybuddha
post Oct 24 2017, 11:48 AM
Post#3


UtterAccess VIP
Posts: 9,278
Joined: 6-December 03
From: Telegraph Hill


Here's a function you can use to build the connection string for MySQL:
CODE
Public Function CnString(Optional myServer As String = "localhost", _
                         Optional myPort As String = "3306", _
                         Optional myDB As String = "test", _
                         Optional myUser As String = "jeff", _
                         Optional myPW As String, _
                         Optional myDriver As String = "{MySQL ODBC 5.3 Unicode Driver}", _
                         Optional myOption As Long = 16386)
On Error GoTo Err_CnString

  Dim myCnStr As String

  If Len(myPW & vbNullString) = 0 Then
    myPW = InputBox("Enter the password for '" & myDB & "' on '" & myServer & "'", _
                    "Database password...")
    If Len(myPW & vbNullString) = 0 Then
      GoTo Exit_CnString
    End If
  End If
  
  myCnStr = "ODBC;" & _
            "DRIVER=" & myDriver & ";" & _
            "SERVER=" & myServer & ";" & _
            IIf(myPort > 0, "PORT=" & myPort & ";", vbNullString) & _
            IIf(myOption > 0, "OPTION=" & myOption & ";", vbNullString) & _
            "DATABASE=" & myDB & ";" & _
            "UID=" & myUser & ";" & _
            "PWD=" & myPW

Exit_CnString:
  CnString = myCnStr
  Exit Function

Err_CnString:
  Select Case Err.Number
  Case Else
      MsgBox Err.Description & vbNewLine & vbNewLine & _
      "Procedure: CnString" & vbNewLine & _
      "Module: basMyConnect", , "Error " & Err.Number
  End Select
  Resume Exit_CnString

End Function


I assume you have the correct MyODBC driver installed (make sure you match the bitness with your version of Access).

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
JeffAspiringProg...
post Oct 25 2017, 05:46 AM
Post#4



Posts: 3
Joined: 24-October 17



@GroverParkGeorge: Thank you for the excellent article, just what I was looking for!

@cheekybuddha: Thank you for the function, appreciated, now I've got something to work with.
Go to the top of the page
 
GroverParkGeorge
post Oct 25 2017, 06:22 AM
Post#5


UA Admin
Posts: 31,240
Joined: 20-June 02
From: Newcastle, WA


We're happy to be able to help.

Continued success with your project.

--------------------
Go to the top of the page
 
JeffAspiringProg...
post Oct 25 2017, 08:54 AM
Post#6



Posts: 3
Joined: 24-October 17



@GroverParkGeorge: Sorry. I'm a little stuck, been on this the whole day. The initconnect procedure in that blog you mentioned is a suitable solution for what I need it to do. When I try to call it in the application startup (I'm assuming in the autoexec macro), I keep getting the error "object required(424) encountered. Is it to do with referencing because I'm sure it's already referenced in the object library?

@cheekybuddha: I did try your code and it seems to work, but it doesn't save the connection temporarily. In that I mean, the idea that was proposed in the blog that GroverParkGeorge suggested, is that the connection is cached temporarily while using the database and then discarded after database closes?
Go to the top of the page
 
cheekybuddha
post Oct 25 2017, 10:58 AM
Post#7


UtterAccess VIP
Posts: 9,278
Joined: 6-December 03
From: Telegraph Hill


My code was only to build the connection string - it doesn't create any connections.

You have to assign the string to the appropriate object - pass-through query, linked table, connection object etc ...

Perhaps show the code you have tried that doesn't work.

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 10:48 AM