access selective data...using query

[Hemant_india]

hi
below is macro i have recorded
when i put .commandtext=qrystring i get error
can somebody help me?
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 07/11/2008 by kjsb
'
Dim qrystring
qrystring = "select * from kjsb.d010009 where lbrcode=5"
'
With ActiveSheet.QueryTables.Add(Connection:= _
"OLEDB;Provider=MSDAORA.1;User ID=test;Data Source=kjsb",
Destination:=Range( _
"A1"))
.CommandType = xlCmdTable
.CommandText = Array("""KJSB"".""D010009""")
'.CommandText = qrystring
.Name = "kjsb (Default) D010009"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\Documents and Settings\kjsb1\My Documents\My Data Sources\kjsb
(Default) D010009.odc"
.Refresh BackgroundQuery:=False
End With
End Sub

 

[paulgu]

Hi,
Your problem is that with a query of command type Table (xlCmdTable), you need to supply the table name only in the command text, so qrystring = "kjsb.d010009" would work fine. If you want to use SQL then you need to change the command type to xlCmdSql.
HTH,
Paul

 

[Alan Moseley]

Without knowing the error message I am struggling a little, but I would think
that your CommandType should be set to xlCmdSQL and NOT xlCmdTable

 

[Hemant_india]

eroor is table not found
however if run the code as it is it works

 

[Alan Moseley]

Your command text is 'SELECT * FROM .....etc', and your CommandType is
xlCmdTable. You do not have a table called 'SELECT * FROM ....etc'. Change
your commandtype to xlCmdSQL.

 

[Hemant_india]

ok
but if i keep commandtype as xlCmdTable and use syntax as
..commandtext=array(my sqlstring) ... then it should run ... i guess
i think i am missing something while writing the query
here i am accessing the table from oracle