Topic

Messages

ODBC

Hi,

We have an application that we need to develop that will extract data from the C&P Pro 4.02 database and then manipulate it to the required format. We are planning to write this application within Visual FoxPro 6 since this is one of our major development tools here at SRG. We have successfully connected to the Pro database and extracted data using Crystal Reports, however this does not give us the flexibility that we require.

The problem is that while we can connect to the database from VFP6 and see the tables within the database, when we try to see the individual fields all we get is a list with each field labeled as "exp". Has anyone had experience using Visual FoxPro 6 with extracting data from C&P Pro 4 using ODBC? Any ideas what may be causing this?

Thank you in advance

Ian Penney Desktop/Network Manager Simon Richards Group

I would like to see an even easier solution, how about an ODBC driver that you can load into Windows ODBC control panel to creat DSN entries and pull data as recordsets, just like you can with Access, or MySql, or SQl for that matter, why is the data so hard to get out of this "Omnis" database?

Chop chop....lets go people, its 2001, we have intranets and extranets that need our C&P data in them. duh.

Frank Zappa

Here is some sample code how to do an ODBC connection to OMNIS out of Visual Basic.

The ODBC driver is in a subfolder of the C&P folder. Just double click the C:\Clients & Profits\C&P 4.06 Complete PC install\ODBC\Configure ODBC.exe program to install, copy the OMODBC32.dll file to the WINNT\system32 directory, and set up a SystemDSN or UserDSN in control panels. I chose the DSN name < > for my VB example below.

Dim rs As New ADODB.Recordset Dim CnnEC As New Connection Dim CmdEC As New Command Dim sSql As String Dim lvwItem As ListItem

' Connection String for ODBC driver to EC CnnEC.ConnectionString = "Provider=MSDASQL;DSN=EC;UID=XYZ; PWD=userPassword;" CmdEC.CommandType = adCmdText sSql = "Select * from AR_ITEMS ax" CnnEC.Open

rs.Open sSql, CnnEC, adOpenStatic, adLockReadOnly

If rs.EOF And rs.BOF Then 'no records available Else rs.MoveFirst Do Set lvwItem = Me.lvwAR_Items.ListItems.Add lvwItem.Text = Trim(rs(0)) 'lvwItem.ListSubItems.Add , , Trim(ACHIff(rs(1))) 'lvwItem.ListSubItems.Add , , Trim(ACHIff(rs(2))) rs.MoveNext Loop Until rs.EOF End If

rs.Close Set rs = Nothing Set CnnEC = Nothing Set CmdEC = Nothing

End Sub

If you have any questions feel free to e-mail me

Sincerely, Hart Fischer

Hart Fischer