VBA: Replace DSN-required code to ADO

I am working on updating the connection string on an access database. There are a few lines on the form's code:

    Dim MyDB As Database
    Dim QD As QueryDef

    Set MyDB = CurrentDb
    Set QD = New QueryDef

    SQLString = "SELECT list1, list2, list3 FROM Lists WHERE..."

    QD.SQL = SQLString

    QD.Connect = "ODBC;DSN=SQL_Cat;DATABASE=MyCatalog;Trusted_Connection=Yes"

    QD.ODBCTimeout = 0
    MyDB.QueryDefs.Append QD

    Set QD = Nothing
    Set MyDB = Nothing

I need to change this code to use an ADO connection. Whilst some of the parts that need to be change make sense to me, there are other parts like how the QD.SQL, QD.Connect, and QD. MyDB.QueryDefs.Append QD have to change correctly.

What I have so far but have not completed, nor tested:

    Dim ADOCon As ADODB.Connection
    Dim ADORS As ADODB.Recordset
    Set ADOCon = New ADODB.Connection
    ADOCon.ConnectionString = GetConnectionString("MyCatalog")
    ADOCon.CommandTimeout = 0
    ADOCon.ConnectionTimeout = 0
    ADOCon.Open

    Set ADORS = New ADODB.Recordset
    ADORS.ActiveConnection = ADOCon
    ADORS.Open

I would like to ask for some help in making this conversion. I think I am close but I need some explanation.

Thank you.

Solution

You should be able to adapt this to your needs:

Dim rs As ADODB.Recordset
Dim db As ADODB.Connection

Set db = CurrentProject.Connection
Set rs = New ADODB.Recordset

SQL = "Select * from Table"

rs.Open SQL, db, adOpenDynamic, adLockPessimistic