How do I create a VBA sub that accesses a database, and then use it inside another sub?

I have a piece of code that accesses a database, and repeats itself as the database 'table' where I get info changes.

My idea is to create another sub with this piece of code and each time I need it I would call it inside the 'big' sub.

Variables Task_Type and X_Loop are the ones that change

Dim strSQL As String
'Define SQL Query
strSQL = "SELECT * FROM X_Loop  WHERE Sample = '" & Sample & "' AND Task_Type = '" & TaskType & "'"
'Create recordset with defined SQL Query
Set rst = New ADODB.Recordset
rst.Open strSQL, conn, adOpenForwardOnly
            
While rst.EOF <> True And rst.BOF <> True
    SetTaskField Field:="Name", Value:=rst.Fields("Task_Name")
    SetTaskField Field:="Task Type", Value:=rst.Fields("Task_Type")
    SelectTaskField Row:=0, Column:="duration", Width:=2
    SetTaskMode Manual:=True
    EditClear Contents:=True
    SelectRow Row:=1
    InsertTask
    rst.MoveNext
Wend

Solution

Whenever you encounter a problem such as this, and you know that the code you want to encapsulate in a sub has a number of variables that are going to change, then those variables become parameters to your new sub:

Option Explicit

Private Sub Command1_Click()
   'call the sub wherever you need
   AccessTheDatabase "X_Loop", "My Sample", "My Task"
End Sub

Private Sub AccessTheDatabase(ByVal TableName As String, ByVal Sample As String, ByVal TaskType As String)
   Dim strSQL As String
   Dim rst As ADODB.Recordset
   
   strSQL = "SELECT * FROM " & TableName & " WHERE Sample = '" & Sample & "' AND Task_Type = '" & TaskType & "'"
   
   Set rst = New ADODB.Recordset
   rst.Open strSQL, conn, adOpenForwardOnly
   
   While rst.EOF <> True And rst.BOF <> True
      'do whatever you need
      rst.MoveNext
   Wend
End Sub

You should, of course, name the sub something that makes sense in the context of your app.