2

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
3
  • Is the problem that you don't know how to access the data retrieved by executing the SQL query?
    – Excellll
    Sep 17, 2014 at 16:24
  • Yes... I have successfully accessed the data, but I don't know where it is stored or how to use it.
    – Toad62
    Sep 17, 2014 at 16:26
  • You need to use a Recordset object to hold the results of the query. Note that you have declared rs but aren't using it anywhere in your code.
    – Excellll
    Sep 17, 2014 at 16:32

1 Answer 1

8

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.

2
  • after 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
    – Toad62
    Sep 17, 2014 at 16:56
  • Yes, I noticed that as well. Try: strSQL = "SELECT NAME, Location WHERE NAME =" & chr(34) & strInput & chr(34) & ";" The chr(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) then chr(39) is the one you want instead.
    – JNevill
    Sep 17, 2014 at 17:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .