Every month, I receive a file from a vendor and perform a simple select query on it. I would like to automate this process in VBA, but seem to have trouble running the SELECT query through VBA. In essence, I would like the Select Query to run and results saved as a Select Query. Below is my code. Any help would be appreciated.
Dim mySQL As String
Dim Cnn1 As ADODB.Connection
Set Cnn1 = CurrentProject.Connection
Dim myrecordset As New ADODB.Recordset
myrecordset.ActiveConnection = Cnn1
'build mySQL string from query's SQL statement
mySQL = "SELECT [Jan Covered Lives].DIVISION, [Jan Covered Lives].[NUMBER OF SUBSCRIBERS], [divisionLookup2 Qry].Division"
mySQL = mySQL + " FROM [divisionLookup2 Qry]"
mySQL = mySQL + " INNER JOIN [Jan Covered Lives]"
mySQL = mySQL + " ON [divisionLookup2 Qry].DIVTXTID = [Jan Covered Lives].DIVISION"
' Code goes wrong here
Set Cnn1 = Nothing