ODBC Access to Sage Line 50 from VB.Net

• Posted by miketeye on April 29, 2010
• 1 comments

So I had a few challenges accessing Sage Data via the Sage supplied ODBC Driver for Sage Line 50 vX from Vb.Net code.

The specific error message is:

System.Data.Odbc.OdbcException: ERROR [08001] Cannot find all files in data path ERROR [01000] The driver returned invalid (or failed to return) SQL_DRIVER_ODBC_VER: 2.00 ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

There were a few suggestions from forums that this had to do with Code Access Security permissions on the .Net platform.Some people suggested elevating trust level from medium to Full ie. <trust level="Full" />, others suggested impersonation with a user who has access to the ODBC data store <identity impersonate="true" userName="myname" password="mypassword" />

Since I was on a development box and running the app in Visual studio, the appDomain already runs in full trust, so the full trust suggestion did not work. Secondly, I tried the identity impersonation option but it did not fix the problem either. I found out the reason being, my ODBC DSN had a path mapped to a network share, and I had no permission as per the impersonating identity to that shared drive.

So what is the simple solution?, for dev purposes, I changed the ODBC store path in Control panel\Administrative Tools\Data Sources\System DSN\SageLine50v11 to point to a local drive, (By default this would be: C:\Progra~1\Sage\Accounts\ACCDATA) and it took care of the error completely.

When I deploy to the server, the ODBC store on the server will have a path pointing to a local drive. This way I don't have to mess up my permission sets on any system.

So, the ODBC driver supplied by sage works with .Net, remember  though that this driver is readonly and does not write back data to the Sage proprietory database. If you need sample connection strings, try the following:

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;DSN=SageLine50v11;Driver={Sage Line 50v11};uid=USERNAME;pwd=PASSWORD;"

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;DSN=SageLine50v11;uid=USERNAME;pwd=PASSWORD;"

connectionString = "DSN=SageLine50v11;uid=USERNAME;pwd=PASSWORD;"

All three connection strings above worked for me. REMEMBER to replace USERNAME with your user name and PASSWORD with your password.

HTH

E-mail | Kick it! | DZone it! | del.icio.us | Permalink | Post RSS