Connection to MySQL thru DSN

[osvaldo]

I will create an Access application as a Front end application. The matter is that the users will need to update some data from Internet where there is a MySql server.
I thought in creating a local DSN to connect to that MySql server. Is it right ?
I already created it but... I dont know the code to connect it from Access.
Could you please help me with the code to write in Access ? Im a new with it. sorry.
Or you can send me an example of it in an Access database to: osvaldo@ord.com.ar

Thank you very much
Osvaldo

 

[WayneRyan]

Osvaldo,


Hope this simple example helps.

strConnection = "Driver={SQL Server};" & _
                "Server="SomeServer;" & _
                "Database=Master;" & _
                "TrustedConnection=Yes;"
Set DbConnection = New ADODB.Connection
With DbConnection
  .Mode = adModeReadWrite
  .Properties("Prompt") = adUseClient
  Call .Open(strConnection)
  End With

Wayne

 

[osvaldo]

Thanks Waine,

I could solve the connection thru this code:

Public Sub Form_Open(Cancel As Integer)
Dim conexion As ADODB.Connection
Dim rs As ADODB.Recordset
Set conexion = New ADODB.Connection
Set rs = New ADODB.Recordset
conexion.Open "dsn=ord"
rs.Open "select * from cursos", conexion
titulo = rs("titulo")
duracion = rs("duracion")
inicio = rs("inicio")
End Sub

I put some button controls in the form to navigate thru the table but
I do get an ERROR when, for example, I click on the Go to Next record:
"An object is required". The code is as follows:

Private Sub Comando6_Click()
On Error GoTo Err_Comando6_Click
rs.MoveFirst
Exit_Comando6_Click:
Exit Sub

Err_Comando6_Click:
MsgBox Err.Description
Resume Exit_Comando6_Click
End Sub

The "rs" Recordset is not accepted. So, Do I have to create the conexion and recordset somewhere else ??

osvaldo

 

[WayneRyan]

Osvaldo,

Your connection and recordset objects are LOCAL to your form's
On Open event code. They are only valid when the form opens.

You can move their definitions to a public module.

Wayne

 

[osvaldo]

I found an alternative option that works with:
Set conexion = CurrentProject.Connection
but using MY connection I do get an error:
Error "3265"
Item cannot be found in the collection corresponding to the requested name or ordinal:
at: Set Me.Recordset = rs



Public Sub Form_Open(Cancel As Integer)
Dim conexion As ADODB.Connection
Dim rs As ADODB.Recordset
Set conexion = New ADODB.Connection
Set rs = New ADODB.Recordset
conexion.Open "dsn=ord"
' rs.Open "select * from cursos", conexion
With rs
Set .ActiveConnection = conexion
.Source = "SELECT * FROM cursos"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
Set Me.Recordset = rs
End Sub