Excel2000: Is it possible to run a pass-through query from Excel

[Arvi Laanemets]

Hi


I need to read some data from Sybase database and create an Excel chart
based on this data.

I can create a pass-through query in Access, but I don't like Access chart
designer, and I prefer Excel to create charts, when possible. Having the
query in Access and chart in Excel will be somewhat cumbersome. Is there a
way to run a Sybase pass-through query from Excel (probably from some
procedure), and how to do it? (I have a Data Source for connection to Sybase
database - let it be MySource - defined on my computer, and I can get a
working SQL-string from my Access test application)


Thanks in advance

 

[Bob Phillips]

Simple using ADO.

Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Sybase.ASEOLEDBProvider;" & _
"Srvr=myASEServer,5000;" & _
"Catalog=myDBName;" & _
"User Id=myUserName;" & _
"Password=myUserPassword"

sSQL = "SELECT * From TableName"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
Worksheets("Sheet1").Range("A1").CopyFromRecordset oRS

oRS.Close
Set oRS = Nothing
End Sub

But see
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSybaseAdaptiveServerEnterprise
for some notes.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Arvi Laanemets]

Hi

Thanks!

I think I have to try to modify your code somehow, so I can use a
pass-through there.

1. I have checked before, and found, that average simple Access/Excel query
(Select * from Table) from most tables in our Sybase database takes about 20
minutes, and only seconds from Sybase Anywhere.
2. It isn't very good idea to meddle with MS query in any critical database,
when there are 100 - 200 users connected to it.
3. The original (Sybase) query uses some functions not available in Access
or Excel. Replacing them is probably possible, but may cause additional
problems.
4. The query uses 4 Left Outer Join's (1 join + 3 join's sequently) + 4 Left
Outer Join's in second part (it is an Union query). Unless I missed
something, in Excel2000 only 2 tables can be connected using outer join's.

 

[Bob Phillips]

None of that seems relevant to me. You are using ADO to pass a command to
the database, not MSQuery, so it is what the database can do that is
relevant. The joins etc. are all handled by the DB engine. Excel doesn't
come into it, Access is totally irrelevant.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)