Execute SQL Query with VBA and fill sheet with data
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.
Related videos on Youtube
Toad62
Updated on September 18, 2022Comments
-
Toad62 almost 2 years
Very new to any form of code. Attempting to run a script in VBA that will pull data from an access database using SQL and put it into an excel spreadsheet. Code is similar to below. Runs clean but I don't know how to use the data it is pulling. Thanks in advance.
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'"";" cn.Execute strSQL cn.Close Set cn = Nothing End Sub
-
Excellll almost 10 yearsIs the problem that you don't know how to access the data retrieved by executing the SQL query?
-
Toad62 almost 10 yearsYes... I have successfully accessed the data, but I don't know where it is stored or how to use it.
-
Excellll almost 10 yearsYou need to use a
Recordset
object to hold the results of the query. Note that you have declaredrs
but aren't using it anywhere in your code.
-
-
Toad62 almost 10 yearsafter adding the line "Set rs = CreateObject("ADODB.Recordset")" the above worked. Thank you. I appear to have some kind of issue with how I am using my user input variable in my SQL string... but that is another issue altogether. Thank you
-
JNevill almost 10 yearsYes, I noticed that as well. Try:
strSQL = "SELECT NAME, Location WHERE NAME =" & chr(34) & strInput & chr(34) & ";"
Thechr(34)
is a double quote character. The&
concatenates all of it together. If Access requires a single quote around text (I can't remember off the top of my head) thenchr(39)
is the one you want instead.