Accessing 3rd party data via their ODBC driver

[Susan Smith]

Hi all,

I know that we (or rather Luis) has talked at past conferences about the BR ODBC driver and how to set up the data definitions, etc. But what if I want to access the data of a 3rd party application with THEIR ODBC driver from within BR? Can someone give me the 2nd grade overview of this?

My BR client, in addition to using the system I've written for them in BR, also uses an accounting system from Sage Software (MAS 200). Their system is written in Providex (an offshoot of Business Basic) and their data is in native Providex format. They told me that they have an ODBC driver available so I can get at it, but the person that I have access to at their MAS 200 "partner" doesn't know much about it.

I want to do some data validation. When I enter a G/L Account in my BR program, I want to see if that account exists in the MAS 200 chart of accounts. Likewise, before I blindly send a batch of checks to their A/P system (which is being done with a .CSV file import), I'd like to verify that their check history doesn't already contain any of the check numbers in my batch before I start the posting process.

Originally, I thought I could use BR 4.3 and it's SQL features, but I came to find out today that my client isn't running the SQL version of MAS 200. That's when I came up with the ODBC idea. If I can't connect directly for this validation, I'm going to have to export their check history to a CSV file every time I post a batch so I can examine it for duplication check numbers. Ugh. Same with the chart of accounts. And there are 4 companies involved, so there is a lot of extra steps this way.

Can anyone point me in the right direction? This scenario doesn't seem to fit the examples that we did with Excel at the conference with Luis because I want to access THEIR data from my programs rather than access BR data.

-- Susan

[GomezL]

On the CD, there is a sample program in my section.

CLSINC\CSV\CSV_EXPORT.WB

My goal was to make this both an example of creating a CSV file using the "Mat2Str" command, as well as a usable example of using BR 4.3 to connect to an ODBC source.

In my Example, I used an MS-Access Table, but you can really use any data source you want.

** Note: In my example I had a "Failed Attempt" to use Excel as an ODBC source (My bad syntax caused it to fail).

Connecting to an Access DB is as easy as:

Code: Select all

08000 CONFIG_ACCESS: ! 
08010   DIM Database$*256,Dbq$*256,Defaultdir$*256,Connection$*256
08020   LET Defaultdir$=Os_Filename$("43")
08030   LET Connection$="DRIVER=Microsoft Access Driver (*.mdb, *.accdb);"
08040   LET Database$="mydb"
08050   LET Dbq$=Os_Filename$("43\ATTYFILE.MDB")
08060   EXECUTE 'CONFIG database '&Database$&' connectstring="'&Connection$&';DBQ='&Dbq$&';DefaultDir='&Defaultdir$&';UserCommitSync=Yes;Threads=3.SafeTransactions=0 ;PageTimeout=5;MaxScanRows=8 ;MaxBufferSize=2048 ;FIL=MS Access;DriverId=2"'
08100   RETURN 

The trick is to come up with the "Correct Connection String" for your ODBC driver.