Using the ODBC feature available in Tally.ERP 9 the user can extract the necessary data in Excel and generate the required reports.

1. Odbc Driver Manager Not Found
2. Odbc Driver Not Found
3. Odbc Driver Not Showing Up

To extract data from Tally.ERP 9 in Excel execute the following steps:

Tally ODBC with Microsoft Excel 2007/2010 Tally ODBC with Microsoft Excel 2007 Tally OBDC helps you to extract the Data from Tally.ERP 9 and design the reports in MS Excel 2007. This can be done by following the steps listed below. § In case, the ODBC Server is not running, you can enable the ODBC Server by following the steps shown. Due to this, Tally.ERP 9 (which acts as the ODBC Server) will not be able to communicate with MS Excel and hence data will not be exported. Now, to enable data export, a driver is to be installed. In the first scenario, to export data via ODBC. Step by step tutorial on how to install an odbc driver for Microsoft access/excel in windows. ODBC driver not found. ODBC to TALLY,Tally ODBC To.

• StartTally.ERP 9. It should be open till the Mail Merge process is complete.

• Ensure that the wordsODBC Serveris visible in theInformation PanelunderConfigurationsection of Tally.ERP 9 screen.

• OpenExcelsheet

• Go toData > From Other Sources > From Microsoft Query(Import data for an unlisted format by using the Microsoft Query Wizard)

[Img-52]

• Choose theTally.ERP 9 Source(Example: TallyODBC 9000*)

• ClickOK

[Img-53]

The screen displayed above appears whenTally.ERP 9 exeis32 Bit.

Note:IfTally.ERP 9 - 64 Bit, the screen appears as shown below: Observe that theDSN(Data Source Name) selected isTallyODBC64_9000, where 9000 is the ODBC Server port number.

[Img-54]

• TheQuery Wizardwindow opens

• SelectLedger

[Img-55]

• Expandthe list under Ledger and select the required fields andmovethe required fields to the right side

[Img-56]

• ClickNext

[Img-57]

• Map the Parent asSundry Debtors(if only Sundry debtors are required) orSundry Creditor(if only Sundry Creditors are required) or selectbothone by one, based on the requirement.

[Img-58]

• ClickNext

• If any sorting is required select thesorting

• ClickNext

• Click onSave Query

• Save the Query in a particular path (Eg. C:Documents and SettingsUserApplication DataMicrosoftQueriesQuery from TallyODBC_9000.dqy)

• Click on Finish

[Img-59]

• The customer data can also be seen in the Excel sheet.

• ClickOKin theImport Datawindow to import the data to Excel

[Img-60]

• PressOK

[Img-61]

Exceptional cases for 64 Bit compatible scenarios

There are three important elements whose bitness determine the Export via ODBC process:

• Tally.ERP 9,

• Operating System, and

• MS Office

The ODBC feature works seamlessly when, the Operating System, Tally.ERP 9 and MS Office are all 64-bit. The method for this is as shown above.

However, there is a driver requirement if, the Operating System is 64 Bit, while

• Tally.ERP 9 is 32 bit and MS Office is 64-bit.

• Tally.ERP 9 is 64 bit and MS Office is 32-bit.

As can be seen, in both cases there is a bitness mismatch between Tally.ERP 9 and MS Office. Due to this, Tally.ERP 9 (which acts as the ODBC Server) will not be able to communicate with MS Excel and hence data will not be exported.

Now, to enable data export, a driver is to be installed.

In the first scenario, to export data via ODBC,

• Open the Tally Application folder.

• Select the toolregodbc64.exeandRun as Administrator. This will install the drivertallywin64.dat

In the second scenario,

• Select the toolregodbc32.exeandRun as Administrator. This will install the drivertallywin32.dat

To summarise,

Once the driver has been installed, you can continue with the export process in MS Excel.

One of the .NET Framework features often highlighted is its new data access model, ADO.NET. Some of its improvements over its predecessors include scalability, speed, and its disconnected nature features. A .NET data provider is used to access a database system; a good example is the Oracle Data Provider for .NET. One problem is some systems don't have a data provider available, so, thankfully, you can easily add support for the older ODBC (Open DataBase Connectivity) technology. ODBC is an established industry standard with ODBC drivers available for most systems in use.

Working with ODBC

ODBC is a uniform interface standard that you may use to access database systems. It's a database access library that enables applications to work with data contained in a database. One aspect of ODBC is that you may use it to access almost any type of database, albeit Oracle, Access, Sybase, mySQL, spreadsheets, text files, and so forth. It's a mature technology, so locating an ODBC driver for a particular database system is usually not a problem.

ODBC .NET Data Provider

The ODBC .NET Data Provider is an add-on component to the Microsoft .NET Framework Software Development Kit (SDK). It provides access to native ODBC drivers the same way that the OLE DB .NET Data Provider provides access to native OLE DB Providers. The ODBC .NET Data Provider is intended to work with all compliant ODBC drivers, but the Microsoft site states that it has only been tested with the Microsoft SQL ODBC Driver, Microsoft ODBC driver for Oracle, and the Microsoft Jet ODBC driver.

ODBC setup

ODBC consists of a driver and driver manager. The driver is specific to a database vendor's product. For instance, Oracle provides a driver for working with an Oracle system. The driver manager is used to install the necessary driver files and configure data sources (that take advantage of the driver) to be used in applications. On Windows-based systems, the ODBC Data Source Administrator is used to create and maintain ODBC connections. You may utilize an ODBC driver in a .NET application once it is property installed and set up.

ODBC classes

Once you install the ODBC .NET Data Provider, you can utilize it in an application. If you're using Visual Studio .NET, you may add a reference to its dll file, Microsoft.Data.Odbc.dll. If you're developing from the command line, you can add a reference during compilation or copy the dll file into the application's bin directory.

The ODBC classes are contained in the Microsoft.Data.Odbc namespace. It includes the following classes:

• OdbcConnection: Used to connect to an ODBC data source. The name assigned to the ODBC data source, during its setup, is used to access it.
• OdbcCommand: Used to execute a command against a connection.
• OdbcDataReader: Allows you to loop through the results of a query against a data source.
• OdbcParameter: Used to bind a parameter to a command.
• OdbcDataAdapter: Used to fill a DataSet object from an ODBC data source.
• OdbcCommandBuilder: Creates default Insert, Update, and Delete statements for an ODBC data adapter.

The next example takes advantage of a few of these classes. It utilizes a previously established ODBC data source (aptly called Test). The DSN name is used in the connection string, along with the user id and password to access the database. A basic SQL statement is used to retrieve all rows from the Customers table with the column values displayed. Finally, the connection is closed and all other objects are disposed. The C# code follows:

Odbc Driver Manager Not Found

using System;
using Microsoft.Data.Odbc;
namespace BuilderODBC {
class TestClass {
static void Main(string[] args) {
string connectionString = 'DSN=Test;UID=Chester;Pwd=Tester;';
string sql = 'SELECT CustomerID, ContactName, ContactTitle FROM Customers';
OdbcConnection conn= new OdbcConnection(connectionString);
conn.Open();
OdbcCommand comm = new OdbcCommand(sql, conn);
OdbcDataReader dr = comm.ExecuteReader();
while (dr.Read()) {
Console.WriteLine(dr.GetValue(0).ToString());
Console.WriteLine(dr.GetValue(1).ToString());
Console.WriteLine(dr.GetValue(2).ToString());
}
conn.Close();
dr.Close();
comm.Dispose();
conn.Dispose();
} } }

The equivalent VB .NET code follows:

Imports Microsoft.Data.Odbc
Module Module1
Sub Main()
Dim conn As OdbcConnection
Dim comm As OdbcCommand
Dim dr As OdbcDataReader
Dim connectionString As String
Dim sql As String
connectionString = 'DSN=PracticalLotusScriptTest;UID=Chester;Pwd=Tester;'
sql = 'SELECT CustomerID, ContactName, ContactTitle FROM Customers'
conn = New OdbcConnection(connectionString)
conn.Open()
comm = New OdbcCommand(sql, conn)
dr = comm.ExecuteReader()
While (dr.Read())
Console.WriteLine(dr.GetValue(0).ToString())
Console.WriteLine(dr.GetValue(1).ToString())
Console.WriteLine(dr.GetValue(2).ToString())
End While
conn.Close()
dr.Close()
comm.Dispose()
conn.Dispose()
End Sub
End Module

Dealing with ODBC errors

The Microsoft.Data.Odbc namespace includes the OdbcException class for handling any errors that may occur when interacting with ODBC data sources. We can alter our example to utilize this class to handle any runtime exceptions that may occur. The altered C# code follows:

Odbc Driver Not Found

using System;
using Microsoft.Data.Odbc;
namespace BuilderODBC {
class TestClass {
static void Main(string[] args) {
string connectionString =
'DSN=PracticalLotusScriptTest;UID=Chester;Pwd=Tester;';
string sql = 'SELECT CustomerID, ContactName, ContactTitle FROM Customers';
OdbcConnection conn = null;
OdbcCommand comm = null;
OdbcDataReader dr = null;
try {
conn= new OdbcConnection(connectionString);
conn.Open();
comm = new OdbcCommand(sql, conn);
dr = comm.ExecuteReader();
while (dr.Read()) {
Console.WriteLine(dr.GetValue(0).ToString());
Console.WriteLine(dr.GetValue(1).ToString());
Console.WriteLine(dr.GetValue(2).ToString());
}
dr.Close();
}
catch (OdbcException oe){
Console.WriteLine('An ODBC exception occurred: ' + oe.Message.ToString());
} catch (Exception e) {
Console.WriteLine('An exception occurred: ' + e.Message.ToString());
} finally {
if (conn.State System.Data.ConnectionState.Open) {
conn.Close();
}
comm.Dispose();
conn.Dispose();
} } } }

You'll notice the ODBC objects are declared outside the try/catch block, so they may be utilized in the finally block. ODBC exceptions are handled separately from generic errors. The connection is checked to see if it is open before closing it.

Odbc Driver Not Showing Up

Every data source available

ODBC has been around for numerous years. Consequently, ODBC drivers exist for almost any data source imaginable. The ODBC .NET Data Provider provides ODBC access within .NET applications through the Microsoft.Data.Odbc namespace. Use this data provider when you cannot find a native data provider for the data system used in your application.

TechRepublic's free .NET newsletter, delivered each Wednesday, contains useful tips and coding examples on topics such as Web services, ASP.NET, ADO.NET, and Visual Studio .NET. Automatically sign up today!