access selective data...using query
 

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

--
hemu

access selective data...using query
 

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

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Hemant_india" wrote:

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

--
hemu

access selective data...using query
 

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


"Alan Moseley" wrote:

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

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Hemant_india" wrote:

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

--
hemu

access selective data...using query
 

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.
--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Hemant_india" wrote:

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


"Alan Moseley" wrote:

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

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Hemant_india" wrote:

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

--
hemu

access selective data...using query
 

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
--
hemu


"Alan Moseley" wrote:

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.
--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Hemant_india" wrote:

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


"Alan Moseley" wrote:

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

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Hemant_india" wrote:

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

--
hemu