I am making a QueryTable in Excel and ran into this error.
I set query command text so
.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?
if your just initally setting it up try something like this
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, _
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...
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
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
'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
Do While Not .eof
For i = 0 To .fields.count - 1
cells(j, i + 1).formula = .fields(i).value
j = j + 1
'close all connections
set rs = Nothing
set cmd = Nothing
Set con = Nothing
Hope this works
Ill edit it when i can if it doesnt