Problem in SQL Query

[Shilps]

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").Valu
datab = Worksheets("QUERY_BUILDER").Range("BH2").Valu

' 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 = Tru
.RefreshStyle = xlInsertDeleteCell
.RowNumbers = Fals
.FillAdjacentFormulas = Fals
.RefreshOnFileOpen = Fals
.HasAutoFormat = Tru
.BackgroundQuery = Tru
.TablesOnlyFromHTML = Tru
.Refresh BackgroundQuery:=Fals
.SavePassword = Tru
.SaveData = False 'Will not save the dat
End Wit
End Su

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

TI
Shilp

 

[Al]

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?

 

[onedaywhen]

Note the string concatenation character in VBA code is '&' not '+'
(the latter being the C equivalent). You have a mixture of both in
your code. Using '+' with strings can have unexpected results.

--