Store external data query information in variables instead of worksheet

[LT]

Greetings all.
I have nearly completed a series of subs and functions that retrieve data from multiple excel sheets, access, and a corporate database.

One of my end spreadsheets will have up to 100 lines of data in it once it is updated. Each line is information from a trouble ticket (the corporate database contains 10s of thousands of trouble tickets). Each line has a hyperlink to the trouble ticket it is related to.

Once done, I still have 3 pieces of data that are not present on each line. I cannot create a SQL query which will efficiently give me just the trouble tickets needed for those three pieces of data. My thinking is to create a SQL query which accepts each trouble ticket one at a time and retrieves the 3 needed pieces of data.

My research indicates I need to specify a destination on a worksheet for this data. I am prepared to create the query in VBA and place the data in a worksheet and retrieve it from there.

Is it possible to bypass using a worksheet, create the query in the VBA code and update specific variables (variable1, variable2, etc) with the data?

SQL = "Select var1, var2, var3 From Corpdatabase where ticketnum = " & ticketnum
......
variable1 = var1
variable2 = var2
variable3 = var3

I look forward to your answers. Thank you.

 

[LT]

Greetings Again
I exhausted all of the research that I could and could not find any references to executing an external query in VBA and storing the information in variables. The conclusion is that I can execute an external query from VBA, but have to create a table on a worksheet. It goes without saying I can use variables to collect the data from the worksheet and continue with my program.

Regards.