Question re. MYSQL and ADODB

[JumboCactuar]

Hi,
i use the following method to get data from a MYSQL database:

Install MYSQL 5.1 Driver

add a reference to 6.1
not sure which is the best to use here ?

use this code

Sub loadSQLdata()
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    conn.Open "Driver={MySQL ODBC 5.1 Driver};SERVER=192.168.1.29;DATABASE=MYDB;UID=xxx;PWD=xxx;PORT=3306;"
     
    rs.Open "Select * From TABLE1 WHERE MYIDNO=127, conn
    
    Sheet1.Range("A2").CopyFromRecordset rs
    
    rs.Close
    conn.Close
   
    Set rs = Nothing
    Set conn = Nothing
    
Application.ScreenUpdating = True
    
End Sub

Is that the best way? or is there anything else i should do

I see some use Set cnn = New ADODB.Connection rather than creating the object

also is there a way around having to install the driver on each machine to use this ?


Thanks for any help