Philps:

Troubleshooting an entire sql statement is particularly
difficult. First i would make your sql code "modular"
so that it is easier to troubleshoot. For example
I would introduce the variables SelectClause, FromClause,
& WhereClause and have them correspond to the parts of
your sql statement. For example,

..sql= Array(SelectClause & " " & Fromclause & " " &
whereclause)

Next, you can try running your statement without the
whereclause. If the statement works, you know your
problem is in the whereclause; If not, its time to move
onto the other parts of your statement.

So then i would simplify the statement, reducing it to one
table and one variable in the select statement. Then
progressively add variables and tables, running the
statement until you come up with the error.

Hope this helps.



-----Original Message-----
Hi everyone,

I am trying to retrieve the data from MS Access database
through ODBC query through a function given below:

Sub query_gen(fproject_master, fproject_master1,
fproject_master2, fmptable, _
fmptable1, fpdetail, fpdetail1, fpdetail2, fpdetail3,
fpdetail4, query1, query2, _
query3, query4, query5, fcycle_type)

direc = Worksheets("QUERY_BUILDER").Range
("BH1").Value
datab = Worksheets("QUERY_BUILDER").Range
("BH2").Value

' Sheet2.Columns("A:BA").NumberFormat = "General"


With ActiveSheet.QueryTables.Add(Connection:=Array
(Array( _
"ODBC;DSN=MS Access Database;DBQ=" + CStr
(datab) + _
";DefaultDir=\\phome5\common\AT\AT-
Amendments;DriverId=25;"), _
Array("FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;")), Destination:=
_
Range("A7"))


.Sql = Array("SELECT " & fproject_master & " " &
fproject_master1 & " " _
& fproject_master2 & " " & fmptable & " " &
fmptable1 & " " & fpdetail & " " _
& fpdetail1 & " " & fpdetail2 & " " & fpdetail3
& " " _
& fpdetail4 & " " & fcycle_type & " FROM `" & CStr
(datab) & _
"`.M_P_Table M_P_Table, `" + CStr(datab) & _
"`.Cycle_Type Cycle_Type, `" & CStr(datab)
+ "`.P_Detail P_Detail, `" _
+ CStr(datab) + "`.Project_Master Project_Master"
_
& " WHERE M_P_Table.M_P_No = P_Detail.M_P_No AND
M_P_Table.Project_No = " _
& "Project_Master.Project_No AND
Cycle_Type.cycle_project=P_Detail.Serial_No" _
& "AND ((" & query1 & query2 & query3 & query4 &
query5 & "))")

FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = False 'Will not save the data
End With
End Sub

Whenever I am trying to run this query,
it is giving 2 errors:
First it gives error on line : Sheet2.Columns
("A:BA").NumberFormat = "General"
the error is
Run-Time Error "1004" :unable to set the NumberFormat
property of Range Class.

So I commented this line.

and the second error its giving type mismatch on line:

..Sql = Array("SELECT " & fproject_master & " " &
fproject_master1 & " " _
& fproject_master2 & " " & fmptable & " " &
fmptable1 & " " & fpdetail & " " _
& fpdetail1 & " " & fpdetail2 & " " & fpdetail3
& " " _
& fpdetail4 & " " & fcycle_type & " FROM `" & CStr
(datab) & _
"`.M_P_Table M_P_Table, `" + CStr(datab) & _
"`.Cycle_Type Cycle_Type, `" & CStr(datab)
+ "`.P_Detail P_Detail, `" _
+ CStr(datab) + "`.Project_Master Project_Master"
_
& " WHERE M_P_Table.M_P_No = P_Detail.M_P_No AND
M_P_Table.Project_No = " _
& "Project_Master.Project_No AND
Cycle_Type.cycle_project=P_Detail.Serial_No" _
& "AND ((" & query1 & query2 & query3 & query4 &
query5 & "))")

It was not giving this error earlier. Its all of a
sudden. What could be the possible reason of this sudden
appearance of error?

TIH
Shilps

.