Share
ReportJust installed Nav 13. How do I get a connection via ODBC. Used it on Version 3.60
*This post is locked for comments
You’re offline. This is a read only version of the page.
Just installed Nav 13. How do I get a connection via ODBC. Used it on Version 3.60
*This post is locked for comments
I am able to get individual transactions imported into excel, but not the TOTAL Balances of Jobs and General Ledger
by doing that you do not have the business logic of the accessed nav objects.
for balances follow msdn.microsoft.com/.../hh168377(v=nav.70).aspx
and analyse the calc. logic of the according objects (pages/reports).
Finally got a link via- Data, Connections, Microsoft Query, using SQL Server. I now just need to find cost totals!
Finally got a link via- Data, Connections, Micrososft Query, using SQL Server. I now just need to find cost totals!
you can connect via odbc using the according .net classes.
following sample code inserts 2 text values into an excel sheet.
- preparation of excel file
create a new excel file, for that sample with format excel 97-2003 xls.
save it into folder c:\temp, call it book1.xls.
the first sheet must have the name 'Sheet1'.
write FName into cell A1, LName into cell B1.
save the excel file, close excel.
the prepared excel sheet:
- variables: set property runonclient of dotnet variables to yes
OdbcConnection : DotNet : System.Data.Odbc.OdbcConnection.'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' Command : DotNet : System.Data.Odbc.OdbcCommand.'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' Query : Text CmdResult : Integer
- the sample code:
All responses (
Answers (
OdbcConnection := OdbcConnection.OdbcConnection;
OdbcConnection.ConnectionString := 'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\temp\book1.xls;ReadOnly=0';
OdbcConnection.Open;
MESSAGE(FORMAT(OdbcConnection.State.ToString));
Query := 'insert into [Sheet1$] (Fname,Lname) values (?,?)';
Command := Command.OdbcCommand(Query, OdbcConnection);
Command.Parameters.AddWithValue('?', 'value 1');
Command.Parameters.AddWithValue('?', 'value 2');
CmdResult := Command.ExecuteNonQuery;
MESSAGE('Odbc Command Result: ' + FORMAT(CmdResult));
OdbcConnection.Close;
MESSAGE(FORMAT(OdbcConnection.State.ToString));
CLEAR(OdbcConnection);
result:
i suggest to replace this data export using standard nav functionality.
in many pages you will find something like an "export to excel" action.
for special exports you could create your own report using table "excel buffer". this special table is intended to import/export data from/to excel.
also have a look at the tools powerpivot and jetreports.
Imported from Nav 3.60 to Excel.
Connection was used to download Balances from General Ledger, costs, payroll info etc
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156