Refresh QueryTable throwing "General ODBC error" - VBA Excel 2011 for Mac -

edit: new mistake found?

i may have found out why script wouldn't work anymore. there has been office update , seems have implemented microsoft query gets rid of odbc-manager installed , configured needs. actual problem may microsoft query doesn't have unicode-library , don't know put them microsoft query read/get them. or rather don't know how give microsoft query path unicode-library in manager there no possibility change it, or maybe there , didn't find it? in addition cannot open microsoft query manager unless open directly excel workbook.

original question

this code worked time. exact same code runtime error. here asked in case , got work.

here's code:

sub connectsql()      dim connstring string     dim slogin string     dim qt querytable      slogon = "uid=*;pwd=*;"     sqlstringfirma = "select * gi_kunden.tbl_firma"     sqlstringperson = "select * gi_kunden.tbl_person"     connstring = "odbc;dsn=kundedb;" & slogon      activeworkbook.sheets("firma").select     activesheet.range("a1:t2000").clear      each qt in activesheet.querytables         qt.delete     next qt      activesheet.querytables.add(connection:=connstring, destination:=range("a1"), sql:=sqlstringfirma)         .backgroundquery = false         .refreshstyle = xloverwritecells         .refresh '"general odbc error" hereeee     end      activeworkbook.sheets("person").select     activesheet.range("a1:t2000").clear      each qt in activesheet.querytables         qt.delete     next qt      activesheet.querytables.add(connection:=connstring, destination:=range("a1"), sql:=sqlstringperson)         .backgroundquery = false         .refreshstyle = xloverwritecells         .refresh 'and hereee again     end       call replace     activeworkbook.sheets("firma").select  end sub 

i did use .select twice here , know should change it.

i working in excel 2011 on mac aware not works on windows work.

* edit *

first of all: if have object libraries activated, still doesn't work.

secondly, realized things opened object browser:

1. "odbcconnection" declared class ("class odbcconnection member of excel"). when click on excel in object-browser "odbcconnection" found. if enter code "excel.odbcconnection.refresh" throws error "method or data member not found" highlighting word "odbcconnection". same error shown when use odbcconnection.refresh (refresh being method of odbcconnection according object browser).

2. i have same problem "querytable(1).refresh", throwing error: "variable not defined" though it's listed , declared in object-browser ("class querytable member of excel").

i believe these kind of errors things connected this.

edit

this throws same "general odbc error" on line activeworkbook.refreshall:

dim strconn string dim strlogindata string dim qt querytable  dim strfirmsql string dim strperssql string  strconn = "odbc;dsn=kundedb;" & strlogindata strlogindata = "uid=usr_gi_kunden;pwd=au382k8?x."  strfirmsql = "select * gi_kunden.tbl_firma" strperssql = "select * gi_kunden.tbl_person"  dim wsfirm worksheet set wsfirm = activeworkbook.sheets("firma")  wsfirm.range("a1:t1000").clear  each qt in wsfirm.querytables     qt.delete next qt  wsfirm.querytables.add(strconn, wsfirm.range("a1"), strfirmsql)     .savedata = true     .backgroundquery = true     '.refresh end   dim wspers worksheet set wspers = activeworkbook.sheets("person")  each qt in wspers.querytables     qt.delete next qt  wspers.querytables.add(strconn, wspers.range("a1"), strperssql)     .savedata = true     .backgroundquery = true     '.refresh end  activeworkbook.refreshall 'fails here  call replace  wsfirm.activate 

i can't solve problem, can debug it.

the first question is: error message mean? telling there's error query or command you're running on data, or preventing connecting database?

it possible write informative error messages, , whatever misguided genius @ redmond implemented query table chose not pass through detailed error information emitted database server , connection libraries.

fortunately, of work ourselves.

the querytable object has connection property - it's string, not fully-featured connection object, can examine in more detail , test against adodb connection object. try function testing connection strings, , see if there's useful information:

 public sub connectiontest(connectionstring string)
 ' late-binding: requires less effort, correct aproach ' create reference 'microsoft activex data objects' -
 'dim conado adodb.connection 'set conado = new adodb.connection
dim conado object set conado = createobject("adodb.connection")
 dim integer
 conado.connectiontimeout = 30 conado.connectionstring = connectionstring
 on error resume next
 conado.open
 if conado.state = 1     debug.print "connection string valid" else     debug.print "connection failed:"

     = 0 conado.errors.count         conado.errors(i)             debug.print "adodb connection returned error " & .number & " (native error '" & .nativeerror & "') '" & .source & "': " & .description         end     next i

 end if
 debug.print "connection string: " debug.print vbtab & replace(.connection, ";", ";" & vbcrlf & vbtab) debug.print

 set conado = nothing

 end sub 

...and insert code:

 dim objquerytable excel.querytable dim strconnect string
 set objquerytable = activesheet.querytables.add(connection:=connstring, destination:=range("a1"), sql:=sqlstringfirma)
      objquerytable          strconnect = .connection         .backgroundquery = false         .refreshstyle = xloverwritecells         .refresh '  "general odbc error" hereeee     end with
  connectiontest strconnect  ' view output in debug window/immediate pane 

if can see errors in that, might implementation of adodb connection doesn't work in mac office environment: it's entirely possible have either:

1. created connection, seen working, , eliminated possibility connection string or dsn source of problem...
2. ...or spotted error in connection parameters can fix.

if connection's working, it's query or command you're running @ database that's source of problem - , error messages i've seen in question point in direction - need delve little deeper.

unfortunately, have no way of knowing whether tools use work you: developers' debugging code, , you'll need tinker work.

the reason it's fiddly office team implemented querytable made interesting decisions: 'connection' , 'recordset' properties exposed querytable aren't fully-featured objects - think interfaces allow querytable make use of objects called 'connection' , 'recordset' variety of different providers, , expose common set of properties , methods. it's decision cross-platform usability, means developer needs interrogate objects can't rely on given method being present @ runtime - so decompiled code in 'debug' mode only.

you'll need register microsoft dao , ado references in ide: late-binding 'createobject' wrong tool when need able view these objects in 'locals' window:

 public sub connectiondetails(objquerytable excel.querytable)
 dim rstado adodb.recordset dim conado adodb.connection
 dim rstdao dao.recordset dim condao dao.connection
 dim integer

 set objquerytable = sheet1.listobjects(1).querytable
 objquerytable

     debug.print "connection string: "     debug.print vbtab & replace(.connection, ";", ";" & vbcrlf & vbtab)     debug.print

     debug.print "query type: " & .querytype  ' documented here: https://msdn.microsoft.com/en-us/library/office/ff835313.aspx     debug.print "query: " & .commandtext     debug.print "database request type: " & .commandtype   ' xlcmdtype documented here: https://msdn.microsoft.com/en-us/library/office/ff197456.aspx

     .maintainconnection = true
     on error resume next     if typeof .recordset dao.recordset then

         on error resume next         set rstdao = .recordset

         rstdao.openrecordset         = 0 dao.errors.count             dao.errors(i)                 debug.print "dao recordset '" & left(rstdao.name, 24) & "' returned error " & .number & " '" & .source & "': " & .description             end         next i

         set conado = dao.dbengine.openconnection(.connection)         = 0 dao.errors.count             dao.errors(i)                 debug.print "dao connection '" & left(condao.name, 24) & "' returned error " & .number & " '" & .source & "': " & .description             end         next i

     elseif typeof .recordset adodb.recordset then

         on error resume next         set rstado = .recordset

         if rstado.state <> 0 rstado.close         rstado.open         set conado = rstado.activeconnection         = 0 conado.errors.count             conado.errors(i)                 debug.print "adodb recordset '" & left(rstado.source, 24) & "' connection returned error " & .number & " (native error '" & .nativeerror & "') '" & .source & "': " & .description             end         next i
     elseif err.number <> 0 then

         debug.print err.source & " error " & err.number & ":" & err.description

     else

         debug.print "recordset type is: '" & typename(.recordset) & "': further information, place breakpoint in code , use 'locals' window."

     end if
 end with
 end sub
 

code - or attempts - quite straightforward: interrogates database , retrieves detailed error messages.

they tell there's syntax error, or missing parameter in sql - can misleading if database ms-access: 'missing parameter' might mean field name or function name unknown. might mean you can't run sql outside ms-access user session.

if fails work, go connectiontest code , run command text against conado connection object: conado.execute strcommandtext ...and interrogate errors collection again.

that's pretty debugging tools can bring bear on problem: 'stacker can suggest other approaches.