I am trying to create an SQL query in Excel VBA that would create a table in Access based on parameters of a cell in Excel. Usually I could use an execute query to run a pre-built query in the database but since I need to modify it based on the parameters entered into a cell I need to do it this way. The parameter would only be for the Where criteria. For this example we can say that it is also selecting the fixed fields "Sales" and "Product" and the "Where" field is "Model." I also will need to delete the existing table with the same name since I ran into an error that the table already existed. What would the VBA code look like for this?
Step 1: Delete table with the same name
Step 2: Create a new table in Access with the "Where" criteria pointing to a cell.
Sub CreateTable()
On Error GoTo SubError
Const DbLoc As String = "C:\Folder to Excel\Database1.accdb"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim recCount As Long
Dim SQL As String
Set xlBook = ActiveWorkbook
Set xlSheet = xlBook.Worksheets(1)
Set db = OpenDatabase(DbLoc)
SQL = "SELECT Sales, Product " & _
"FROM Table1 " & _
"WHERE Model IN (1, 2);"
If I can feed the parameters to an existing make table query I would happy too!