Problem is exporting data to Excel

[Guest]

Hi,
Well, I have posted this query umpteen no of times on Excel forum but got no answer. Probably because the problem MIGHT be related to Access. Here is a brief description of the problem:
What I am trying to do is to pick data from Access database from MS Excel through an SQL Query. The database has 4 tables , each having a one-many relationship with the subesquent table represented by condition(WHERE M_P_Table.Project_No = Project_Master.Project_No AND P_Detail.M_P_No = M_P_Table.M_P_No AND Cycle_Type.cycle_number = P_Detail.Serial_no)
So far so good. When I execute the query, it picks the data from all the tables EXCEPT from Cycle_type table.
The code of MS-Excel file is here

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=" + datab + ";DefaultDir=" + direc + ";DriverId=25;" _
), Array("FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), Destination:= _
Range("AR1"))

'AR1 is the destination range

.Sql = Array( _
"SELECT DISTINCT " + field + " " & Chr(13) & "" & Chr(10) & "FROM `" + datab + "`." + table_name + " " + table_name + "" _
)
.FieldNames = True 'Field Names appear as Column headings
.RefreshStyle = xlInsertDeleteCells 'Will delete the previous entries
.RowNumbers = False 'Will not enter row numbers
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = False
.TablesOnlyFromHTML = True
.Refresh

End With

It always gives SQL Syntax error on line .Refresh.
Seems that the problem is in the underlying database as it successfully picks the data from all the other 3 tables. Please help, my job is at stake.
TIA
Shilps

 

[John Nurick]

Hi Shilps,

Just a couple of observations:

1) When you run the query (or a query that contains the same SQL) in
Access, does it return the results you expect? If not, there is
something wrong with your SQL.

2) As far as I can make out, substituting values for the variables
field, datab and table_name, the SELECT DISTINCT line in your code below
would produce this:

SELECT DISTINCT TheField
FROM `TheDatabase`.TheTable TheTable

Are you sure that's what you want?

3) I can't understand what you're using the Array() function for. For
instance, according to Help the QueryTable.Connection property wants a
string or variant(string), but you seem to be passing it a variant
containing an array of two variants, each containing an array of one
variant containing a string). What's the logic there?