Execute SQL Query with VBA and fill sheet with data
Solution 1:
You need to fill your recordset object (That's the rs variable you declared). I have changed the code below to do just that. You can then copy the results from the recordset directly into a range with RANGE.copyfromrecordset <yourrecordset>
below I have added that as well where I dump the results into SHeet1.Range("A1"). You'll probably want to change that. EDITED TO ADD: Just realized you are using late binding for ADODB. I have adjusted the code so it will actually work.
Sub Get_Data()
Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL, strInput As String
strFile = "S:\Location.Database.accdb"
strCon = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strFile
Set cn = CreateObject("ADODB.Connection")
cn.Open strCon
strInput = InputBox("Input Desired Name")
strSQL = "SELECT NAME, Location WHERE NAME =""'strInput'"";"
'Added the following four lines
Set rs = CreateObject("ADODB.RECORDSET")
rs.activeconnection = cn
rs.open strSQL
Sheet1.Range("A1").CopyFromRecordSet rs
'removed
'cn.Execute strSQL
rs.close
cn.Close
Set cn = Nothing
End Sub
You can think of the RecordSet (rs in this case) as a virtual table that holds the results of the SQL. You can interact with the RecordSet on a record by record (with rs.MoveFirst, MoveNext, MoveLast ) and field by field basis (by iterating through the rs.fields collection), or you can just dump the results in a range.