ADO Run Select Into Queries via Excel VBA - Missing Data via VBA but manual query run np

Hi, I have an Access Database that I am using to store analytics data. I am using Excel as a data presentation layer (basically a data dump with pivot tables). I have some queries that require a string of left outer joins and the performance is fine. The problem I am having is this:

1. When I run the queries which create temp data tables via "Select into" commands via VBA, the results yield a table of results data but with a portion of the data missing. It literally makes a table with 6 columns of data but leaves one entire column of data empty.
2. If I run the make queries "Select into" manually from the access database it has no problems making the tables and populating the data with all results.

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
With con
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open "C:\test.accdb"
End With

con.Execute "drop table_names"
con.Execute "Update_Table_list"

Anybody run into this problem? Is there something this ADO execute does not like for a "Select Into" queries?

Thanks,
Khiem

Yes, I was scouring the interweb last night trying to find a solution to this, mostly it just called for me to rewrite it as an excel function and incorporate that into my query call. Seems like a lot of work just to use the ADO instead of just using calling access as an object and making query calls from there. The upshot is that using ADO will help me with some performance issues I am having since the database sits on a network drive and can be extremely quirky.

Is there anything else I can do to use ADO and still keep these functions in place? These queries were already a giant pain in the rear to write in "Access SQL" as it is. Ex below:

SWITCH STATEMENT USED:
FROM (SELECT t.plcode, t.cpty, p.descr AS Parent_Name, Switch(t.isin Like 'CARS*','VEHICLE',t.isin Like 'PLANE*','AIRPLANE',t.isin Like 'BICYLCLE*','BIKE') AS Product, t.startd, t.endd, Sum(t.cash) AS New_Cash FROM (dbo_Trans AS t INNER JOIN dbo_cpty AS c ON t.cpty = c.cpty) INNER JOIN dbo_Parent AS p ON c.parent = p.cpty WHERE (((t.endd)>=DateAdd("d",1,(select * from get_date)) Or (t.endd) Is Null) AND ((t.startd)=Date()) AND ((t.isin) Like 'PLANE%' Or (t.isin) Like 'CARS%' Or (t.isin) Like 'BICYCLE%')) GROUP BY t.plcode, t.cpty, p.descr, t.startd, t.endd, t.isin)

IIF STATEMENT USED:
SELECT t.plcode, t.cpty, p.descr AS Parent_Name, Switch(t.isin Like 'CARS*','VEHICLE',t.isin Like 'PLANE*','AIRPLANE',t.isin Like 'BICYLCLE*','BIKE') AS Product, Sum(t.cash) AS Maturity_Cash, (select * from get_date) AS reportdate, sum(iif(startd = reportdate, t.cash)) AS ON_Mat, sum(iif(startd <> reportdate, t.cash)) AS Term_Mat FROM (dbo_Trans AS t INNER JOIN dbo_cpty AS c ON t.cpty = c.cpty) INNER JOIN dbo_Parent AS p ON c.parent = p.cpty WHERE (((t.endd)=date()) AND ((t.isin) Like 'CARS%' or t.isin like 'PLANES%' or t.isin like 'BICYCLE%')) GROUP BY t.plcode, t.cpty, p.descr, t.isin