Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    16

    Unanswered: Run-time error 13 type mismatch

    I am making a QueryTable in Excel and ran into this error.
    I set query command text so
    Code:
    .CommandText = Array("SELECT TOP 50 regn1,name, count (*) AS Counter_agents, sum(obk_sum) AS OBK, sum(obk_sum)*count(*) FROM (SELECT regn1, regn2, sum(obk) as obk_sum FROM kp0407 WHERE (BAL =31302 OR BAL=31303 or bal=31301) GROUP BY regn1, regn2 having sum(obk)>0), banc GROUP BY regn1,name;")
    and get Run-time error 13 type mismatch. However when I run same query directly in access it doesnt cause any problems. What am I doing wrong and what is the remedy?

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    if your just initally setting it up try something like this

    Code:
        Dim qt        As QueryTable
        Dim sConn   As String
        Dim sSQL    As String
        
        
        sConn = "ODBC;DSN=odbcName;Database=dbName"
        sSQL = "SELECT  fields FROM table"
        With Worksheets("Sheet1").QueryTables.Add(Connection:=sConn, _
                Destination:=Range("F1"), Sql:=sSQL)
                .Refresh
        End With

  3. #3
    Join Date
    Sep 2004
    Posts
    16
    it works... in a way. It does what it is supposed to but for some reason it does so only after the whole script stops execution.i.e. I want to do something with the fetched data, then fetch some more, work with it and so on. But instead it works on empty cells and only afterwards does the requested data actually arrive...

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Would ADO be a better soloution?, i.e. fill a recordeset and then write that recordset to the spreadsheet,

    you wont be able to refresh the data unless you run the whole process again though

  5. #5
    Join Date
    Sep 2004
    Posts
    16
    err how do I do that?

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    this is from memory so forgive me if it doesnt work first time as i don have excel or access at home etc.

    youll need to add a reference to Microsoft ActiveX Data Object ?.?

    where ?.? is the highest number you have

    Code:
    Sub testing()
            Dim con As adodb.connection
            Dim cmd As adodb.command
            Dim rs As adodb.recordset
    
            Dim sSQL As String
            Dim sCon As String
            Dim i As Integer
            Dim j As Integer
    
            'set your connection String update as appropriate
            sCon = "Connection String"
            sSQL = "Your SQL String"
    
            'open your connection
            set con = New adodb.connection()
            con.connectionstring = sCon
            con.open
    
            'query the database and fill a recordset
            set cmd = New adodb.command
            cmd.activeconnection = con
            cmd.commandstring = sSQL
            set rs = cmd.execute
            'write to spreadsheet
            j = 1
            With rs
                .movefirst
                Do While Not .eof
                    For i = 0 To .fields.count - 1
                        cells(j, i + 1).formula = .fields(i).value
                    Next i
                    j = j + 1
                    .movenext
                Loop
            End With
            'close all connections
            set rs = Nothing
            set cmd = Nothing
            con.close
            Set con = Nothing
    
        End Sub
    Hope this works
    Ill edit it when i can if it doesnt

    Dave

  7. #7
    Join Date
    Sep 2004
    Posts
    16
    What I wound up was to use my original code except that instead of seting sql in CommandText I have sat it up in Add like you shown first time.

    Thanks for help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •