Foxpro Visual Basic Connection 2

[MattSTech]

Hello all,

I have searched up and down several of the Tek-Tips forums and still have a few unanswered questions about my connection to a foxpro database. Hopefully I can hammer this out in the VB6 forum.

Our system (Made2Manage) uses a Foxpro database to store company data. I am looking to write a bit of VB code to connect to this DB and retrieve information.

The connection string seems simple enough, but I have the following reservations.

1) I have searched for all .dbf files on the server. I have found 1126! Are all the .dbf files connected to the .dbc file?

2) Is there a way to connect to the .dbc file and manipulate all associated .dbf's or do I have to connect to these individually?

Thanks for the guidance.
Matt

 

[hmckillop]

have a look at this useful site it has connection strings for most DB application types

http://www.connectionstrings.com/

or more specifically

http://www.connectionstrings.com/visual-foxpro

"I'm living so far beyond my income that we may almost be said to be living apart

 

[MattSTech]

Thank you. Yes I have used them in the past for conn strings. In the OP I commented on this, perhaps not explaining that I had no problem connecting to the .dbf files independantly.

Recasting Q#1- How do I know what tables are associated to the .dbc?

Matt

 

[PeterWallace]

You Could use a variation of theis bit of code to find associated names

Function TableExist(Tablename As String) As Boolean
Dim cat As ADOX.Catalog
Dim Table As ADOX.Table
Dim mstr As String

Set cat = New ADOX.Catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & TheDatabase & ";"

TableExist = False
For Each Table In cat.Tables
Debug.print " table Name ";Table.name
Next

Set cat = Nothing
End Function

 

[dilettante]

You might want to download Microsoft OLE DB Provider for Visual FoxPro 9.0 and install it.

I believe that a clean FoxPro connection string looks more like:

oConn.Open "Provider=vfpoledb;" & _ 
           "Data Source=C:\vfp9\Samples\Data\myVFPDB.dbc;" & _ 
           "Mode=ReadWrite|Share Deny None;" & _ 
           "Collating Sequence=MACHINE;" & _ 
           "Password=''"

The important parts being the Provider and Data Source entries, since the rest probably defaults. This form of connection string might work better with an ADOX.Catalog to let you enumerate Tables, Views, Procedures, and other collections.

While Jet can handle many xBase files (including FoxPro's) as attached tables using an IISAM this is probably far from optimal. I would avoid using a FoxPro ODBC driver as well, which may impose limitations on the features you can use from ADO.

You might also want to take a look at OLE DB Provider for Visual FoxPro.

Of course FoxPro users can probably tell you a lot more. Have you tried their forum here?

 

[vb5prgrmr]

While my advise is in oppisition to some above I will tell from first hand experience...

Create two ODBC DSN's, one a free table the other to your dbc.

Use DAO ODBC Direct.

Now you will have no problems switching between the two as needed especially if you have to read the Fox Pro equilivalent of an Access memo field. None of that nasty ADO fetching/getting chunks of more information from that type of field as DAO will return it all in one go.

Good Luck

 

[dilettante]

As far as I know you never have to use AppendChunk/GetChunk in ADO, they exist as a convenience to avoid excessive memory consumption with "long" Fields, Parameters, etc. This can be critical to server-side development.

I'm not sure what any of this has to do with the question though.

 

[vb5prgrmr]

What it has to do with the question is the question of access. Using ADO on note fields with Fox Pro vs. DAO ODBC Direct you will find ADO trundicates the field and thus the programmer will need to use the getchunck method of ADO, while DAO does not have such a limitation, unless you force it to.

As for another part of the question of the OP. Yes, all those tables should be linked via that .dbc file and if it is any thing like the accounting system I had to work on there should be sub directories with tables contained. These sub directores logically divided the accounting database into accounts recievable, payable, employess, sales, etc.

Oh, yeah! Just remembered, when you create the free table ODBC DSN, do not provide a path in it as you will provide that as part of your connection string or query statement.

Good Luck

 

[MattSTech]

Thank you so much for the help! I appreciate the different approach. A star for you both!
As I dig into this project I am finding more and more issues. I now have a SQL issue, but I will create a new thread.

 

[dilettante]

I still don't believe that ADO will inherently truncate long values like Memo type field values. It is true the using Jet aggregation or Jet Expression Service operations will convert a Memo to a 255-max Text value, but that's another matter.

The simple example below illustrates this:

Option Explicit
'Form with a multiline TextBox txtMemo and two CommandButtons
'cmdRetrieve and cmdStore.
'
'Project reference to ADODB 2.5 or later.  ADOX is late-bound here
'for compatibility between 2000/XP and Vista/Win7.

Private Const DBNAME As String = "memo.mdb"
Private Const CONNSTRING As String = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" _
  & "Jet OLEDB:Engine Type=5;Data Source='$DBNAME$'"
Private cnDB As ADODB.Connection
Private strCaption As String

Private Sub cmdRetrieve_Click()
    Dim rsMemo As ADODB.Recordset
    
    Set rsMemo = cnDB.Execute("SomeTable", , adCmdTable)
    With rsMemo
        If IsNull(!MemoField.Value) Then
            txtMemo.Text = "*null*"
        Else
            txtMemo.Text = !MemoField.Value
        End If
        .Close
    End With
End Sub

Private Sub cmdStore_Click()
    cnDB.UpdateMemo txtMemo.Text
    txtMemo.Text = ""
End Sub

Private Sub Form_Load()
    Dim strConn As String
    
    strConn = Replace$(CONNSTRING, "$DBNAME$", DBNAME)
    On Error Resume Next
    GetAttr DBNAME
    If Err Then
        'Create empty MDB.
        On Error GoTo 0
        Dim catDB As Object 'As ADOX.Catalog.
        
        Set catDB = CreateObject("ADOX.Catalog")
        With catDB
            .Create strConn
            Set cnDB = .ActiveConnection
        End With
        With cnDB
            .Execute _
                "CREATE TABLE SomeTable (" _
                  & "MemoKey INTEGER, " _
                  & "MemoField MEMO WITH COMP)", , _
                adCmdText Or adExecuteNoRecords
            .Execute _
                "INSERT INTO SomeTable VALUES (1, NULL)", , _
                adCmdText Or adExecuteNoRecords
            .Execute _
                "CREATE PROC UpdateMemo (MemoVal MEMO) AS " _
                  & "UPDATE SomeTable SET MemoField = MemoVal " _
                  & "WHERE MemoKey = 1", , _
                adCmdText Or adExecuteNoRecords
        End With
    Else
        'Open existing MDB.
        On Error GoTo 0
        Set cnDB = New ADODB.Connection
        cnDB.Open strConn
    End If
    strCaption = Caption
End Sub

Private Sub Form_Unload(Cancel As Integer)
    cnDB.Close
End Sub

Private Sub txtMemo_Change()
    Caption = strCaption & " (" & CStr(Len(txtMemo.Text)) & ")"
End Sub

Just paste that into a Form set up with the indicated controls and set the ADODB reference. Then run it and enter any text into the TextBox, which you can store and retrieve by clicking on the buttons.

I've seen no truncation whatsoever, with no need for AppendChunk/GetChunk calls. Admittedly I haven't tested it with much above 10,000 characters though. ;-)

Thunking through Jet to use a FoxPro ODBC driver might add another dimension, I'll admit. I have not tested that myself. At that point it isn't an ADO issue at all though.