Excel and ODBC

[comconrk]

I am connecting to several databases in my Excel Spreadsheet (AS400, Access and Solomon SQL Server).

For some reason the Solomon SQL is very slow. When I
use MSQuery it is quite quick, but when I use the
SQL REquest function in Excel VBA it slows down
considerably.
What can I do to speed this up?

Thanks very much.

 

[xlbo]

Record yourself using MSQuery and run that thru code - here is a sample that I use:

Sub GetBOData()
Dim qtName As String, mSQL As String
qtName = BOSht.QueryTables(1).Name

mSQL = "SELECT Some Stuff FROM Some tables WHERE Some Stuff Matches Some Other Stuff"
 
    With BOSht.QueryTables(qtName)
    .Connection = Array(Array( _
        "ODBC;DSN=KnowledgeQuery;Description=Punch Pub KMS Database;UID=myUserID;APP=Microsoft Office XP;WSID=PPCP0026;LANGUAGE=British;Networ" _
        ), Array("k=DBMSSOCN;Trusted_Connection=Yes"))
        .Destination = Range("BOStart")
        .CommandText = mSQL
        .Name = "AllAGMSTW"
        .FillAdjacentFormulas = True
        .FieldNames = True
        .SavePassword = True
        .SaveData = True
        .Refresh (false)
    End With
End Sub

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244