I have the following macro that I created using Access VBA due to the fact that I am very much unfamiliar with Access VBA but I am hoping to find someone here that knows both.

The question is how would I convert the module to do the same thing but from excel without having to open the database?


Private Sub cmbSubmit_Click()
    Dim TheDbPath As String
    Dim QueryName As String
    Dim SQLStr As String
    Dim Crt1 As String
    Dim SqlStrWhere As String
    
    Crt1 = "ABS"
    'First we'll set some variables:
    SqlStrWhere = "Weekof17July2011.Sunday=""ABS"" Or Weekof17July2011.Sunday=""Late"" Or Weekof17July2011.Sunday=""LE"""

    SQLStr = "SELECT * FROM Weekof17July2011 WHERE " & SqlStrWhere
    
  
    TheDbPath = "C:\Documents and Settings\randell.graybill\My Documents\Attendance.mdb"
    QueryName = Forms!CreateQuery.cBoxTableNames.Value & " Query"
    'SQLStr = "SELECT * FROM " & Me.cBoxTableNames.Value
  
      Dim cn As ADODB.Connection
    Dim cat As ADOX.Catalog
    Dim cmd As ADODB.Command
    Set cn = New ADODB.Connection
    
    
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=" & TheDbPath & ";"
     
    If cn.State <> 1 Then
        MsgBox ("Problem with connection")
        Exit Sub
    End If
     
    Set cat = New ADOX.Catalog
    Set cmd = New ADODB.Command
     
    cat.ActiveConnection = cn
     
    cmd.CommandText = SQLStr
    'On Error Resume Next
    
    cat.Views.Append QueryName, cmd
    
    If Err.Number = -2147217816 Then
    Err.Clear
    MsgBox "This Query already exists!"
    End If
     
    Set cat = Nothing
    Set cmd = Nothing
    cn.Close
    Set cn = Nothing

    

DoCmd.Close acForm, Forms!CreateQuery.Name

DoCmd.OpenForm "AttendanceForm"

End Sub