Importing specific Access table data into Excel using Excel as the front end

Question

I am trying to store and retrieve data that is entered into Excel into Access. I am an Access newbie and already have an Excel program on the front end, leading me to drive the import from Excel. I have successfully figured out how to save my Excel Named Range into the desired Access table, but now I need to figure out how to pull specific data from that Access table back into Excel. I know I can simply use the Get External Data feature from Excel to import the entire Access table into Excel, but I need to be able to only import a specific portion of the table into Excel based upon a predetermined set of parameters. Is this possible to do?

As a background to the program, basically it stores data from part number runs. Not only do I need to save new part runs into an Access database (which I figured out), but I also need to be able to pull previous part number runs from Access back into Excel to perform further analysis. That is why I need to figure out how to import only a specific portion of the table. I'm not sure what code, if any, I can post since I'm basically looking for code from scratch; but if there's any code you think you need from my Excel program I'm happy to provide it. Thanks for your help. Below is the code:

Sub GetSpecData()
Application.ScreenUpdating = False
'*******************************************************************************
'Open the connection to the data source.
Set Connection = New ADODB.Connection
Connection.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=\\Server2013\network_shared\QC SHARED\Databases\P&Q_Tracking_Data_Storage.mdb;"
'*******************************************************************************
'Create the new RecordSet.
Set Recordset = New ADODB.Recordset
With Recordset
'Define the appropriate Filter(s) and notify the user of the selection criteria.
    Src = "Select * from Raw_Data where Tag = 'GHI' "
    Src = Src & "or Tag = 'DEF' "
    Src = Src & "or Tag = 'LMN'"
    .Open Source:=Src, ActiveConnection:=Connection
    'Write the field names.
    For ODCol = 0 To .Fields.Count - 1
        Tracking.Sheets("Selected Past Data").Range("B7").Offset(0, ODCol).Value = .Fields(ODCol).Name
    Next
    'Write the recordset.
    Tracking.Sheets("Selected Past Data").Range("B7").Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
'*******************************************************************************
'Create and format the table from the Recordset.
With Tracking.Sheets("Selected Past Data")
    DataLastRow = .Range("A" & Rows.Count).End(xlUp).row
    .ListObjects.Add(xlSrcRange, Range("B7:M" & DataLastRow), , xlYes).Name = "INC2tbl"
    .ListObjects("INC2tbl").ShowTotals = True
End With
Application.ScreenUpdating = True
'*******************************************************************************

End Sub

After googling the suggestion in the comment below, I have a couple questions. First, the code above seems to filter access data by three keys: GHI, DEF, and LMN. Am I interpreting that correctly? Second, where it says "Select * from Raw_Data where Tag = 'GHI' ", since that's in quotes, that's not actual code that will be executed, correct? That's simply a prompt or something like it, correct?

Answer 1

SELECT * FROM TABLE; OUTPUT TO TABLEName.EXL FORMAT sql;

Answer 2

First, the code above seems to filter access data by three keys: GHI, DEF, and LMN. Am I interpreting that correctly? Second, where it says "Select * from Raw_Data where Tag = 'GHI' ", since that's in quotes, that's not actual code that will be executed, correct? That's simply a prompt or something like it, correct?

First and second questions relate closely, so I'll answer as one. The basic idea here is that there is a connection to the database you defined (in the Connection.Open statement). The connection itself does nothing then that; establish a connection. The actual communication with the database is done in a specific language, named SQL (there are many dialects, but for simplicity sake, call it SQL for now). So your code in in the VBA language, but the communication with the database is done in SQL. The VBA code has to produce SQL statements (and those are strings, so you need the quotes). That's what the Src variable holds; and SQL statement. In this case:

"Select * from Raw_Data where Tag = 'GHI' or Tag = 'DEF' or Tag = 'LMN'"

I think this is not the place to teach you SQL, but there is plenty information on the net about it. What this statement does is:

select all columns from table "Raw_Data", but only those rows of data that has 'GHI', 'DEF' or 'LMN' in the Tag column.

So to receive all the data from table "part_numbers", you would have to use:

"SELECT * FROM part_numbers;"

And if you need to import only columns "col_1" and "col_2", you would use:

"SELECT col_1,col_2 FROM part_numbers;"

And if you need to import only columns "col_1" and "col_2", and only rows with "part_id" lower than 1000, you would use:

"SELECT col_1,col_2 FROM part_numbers WHERE part_id < 1000;"

That's it really. Now if your recordset (think of it as an array that holds the data you asked for) has queried the database, you can loop it to parse the data. Something like:

Set Recordset = Connection.Execute(Src)
Do until Recordset.EOF
    debug.print Recordset!col_1
    Recordset.movenext
loop

Again, google something like: "vba excel adodb access", and you'll hit plenty examples.

Answer 3

After researching SQL code formatting more I have made more sense of the subject. Thanks for the help.