ODBC in Navison 3.56a

I use the ODBC interface to get Navison dat in Excel 97. I can access data with MS Querry and also with VBA if I try it the first time. The second call I get the message [n/ODBC]error or the VBA program display a windows meesage fault page in nodbc.dll. If I restart Excel it is working again one times. Can anybody help me? I use the statements like SQLopen, SQLretrieve and so on like the examples in the Navison manual. In the Microsoft help I get the in formation that these statements should not be used. Is there any short example how to do it in the correct way? Thanks in advance Norbert

I have had the same problem. The problem is that the ODBC driver doesn’t clean up the connection after retrieving the data. The solution I used was to record a macro and use the microsoft VB-code to retrieve data - by using this (and not the XLAodbc as suggested in the navison manual) the macro will run more than once - and with out problems. Exampel from exel 2000: Sub HentNaviData() Msg = “Opdater data fra Navision Database” Style = vbOKCancel + vbQuestion Title = “ODBC” response = MsgBox(Msg, Style, Title) If response = vbOK Then Dim opdatJN As Boolean opdatJN = False filter1 = Range(“B1”) filter2 = (Range(“B2”) - 1) filter3 = (Range(“B3”) + 1) Range(“G1:m25000”).Select Selection.ClearContents With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ “ODBC;DSN=Sample Navision ODBC 32 bit;”)), _ Destination:=Range(“Kontoplan!$g$1”)) .CommandText = Array( _ “SELECT FinansKonto_0.Nummer, FinansKonto_0.Navn, FinansKonto_0.Bevægelse, FinansKonto_0.”“Saldo til dato”"" & Chr(13) & “” & Chr(10) & “FROM FinansKonto FinansKonto_0” & Chr(13) & “” & Chr(10) & _ “WHERE(FinansKonto_0.” _ , _ “KontoArt = ‘Konto’) And (FinansKonto_0.DatoAfgrænsning = '” & filter1 & “’) And (FinansKonto_0.Nummer > '” & filter2 & “’) And (FinansKonto_0.Nummer < '” & filter3 & “’)” & Chr(13) & “” & Chr(10) & “ORDER BY FinansKonto_0.Nummer”) .Name = “finkonto” .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery = False End With ActiveWindow.SmallScroll Down:=-12 opdatJN = True Else Msg = “Data blev ikke opdateret” Style = vbOKOnly + vbInformation response = MsgBox(Msg, Style, Title) End If If opdatJN Then Msg = “Data er opdateret” Style = vbOKOnly + vbInformation response = MsgBox(Msg, Style, Title) End If Sheets(“KONTOPLAN”).Activate End Sub Richard Lindberg