Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > access selective data...using query
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 107
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 75
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 107
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 75
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 107
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Getting query data from Access Bud Cookson Excel Discussion (Misc queries) 1 August 24th 09 06:59 PM
Import Data from Access query Andy Excel Discussion (Misc queries) 8 April 23rd 09 09:15 AM
Import data from query in Access oscar.c.marin[_2_] Excel Programming 4 November 13th 08 11:01 PM
Excel Data Query from Access Luke Bailey Excel Discussion (Misc queries) 0 February 13th 07 11:24 PM
Data too large for Excel, need to query Access data for results Susan[_4_] Excel Programming 8 March 9th 06 03:02 PM


All times are GMT +1. The time now is 12:05 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement