[AccessD] Relinking Foxpro Tables

Gustav,

At 24/02/2005, Gustav Brock wrote:
>Hi David
>
>First, I think TABLE=""; should be omitted from the connect string.
>Second, where/why do you close the mdb - cannot see that.
>Third, try to refresh the TableDefs collection:
>
>DBEngine(0)(0).TableDefs.Refresh

First - Thanks - deleted table=.

Second - I was manually closing and opening the database.

Third - Have added this to my code.

Here is more information.

1) Initially I link the tables using File .. Get External Data .. Link 
Tables.  Files of Type = ODCB Databases.  Machine Data Source = Visual 
FoxPro Tables.  Configuration Connection = Free Table Directory.  I then 
select the directory which holds the tables.  Next pops up a list of tables 
available to be linked.  I select all.  Then for each table I need to 
select a unique record identifier.

In the table properties the description field holds the following:
ODBC;DSN=Visual FoxPro Tables;SourceDB=d:\CLIENTS\PM 
SERVICES\DATA\20050216;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;;TABLE=rs2aaaa1m

The connect field in MSysObjects holds the following:
DSN=Visual FoxPro Tables;SourceDB=d:\CLIENTS\PM 
SERVICES\DATA\20050216;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;

The name field in MSysObjects holds the following:
rs2aaaa1m

At this point I can edit data in the tables.

2) Then I run my code the table properties the description field changes to:
ODBC;DSN=Visual FoxPro Tables;SourceDB=D:\Clients\PM 
Services\Data\20050217;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;;TABLE=rs2aaaa1m

The connect field in MSysObjects holds the following:
DSN=Visual FoxPro Tables;SourceDB=D:\Clients\PM 
Services\Data\20050217;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;

At this point when I open the table I still have the data from the old 
table showing.  I am unable to delete it.  Closing the database and opening 
it in Access still doesn't help.  It is only when I close Access down and 
reopen it that the new tables are appearing.  Even then I am not able to 
change any data (this last point doesn't matter as we just want to access 
the data for reports but I thought I would throw it in anyway).

Here is the current code:

--Code on the cmd button:
Private Sub cmdDataLink_Click()

     On Error GoTo Err_cmdDataLink_Click

     If basDataFileLink() = True Then
         MsgBox "Data files linked successfully."
     Else
         MsgBox "You will need to make sure that the data files are linked 
before you can run any reports."
     End If

Exit_cmdDataLink_Click:
     Exit Sub

Err_cmdDataLink_Click:
     Select Case Err
         Case 0
             MsgBox "No Error", vbCritical, "Link Data"
         Case Else
             Call basErrorMsg("cmdDataLink_Click")
     End Select
     Resume Exit_cmdDataLink_Click

End Sub

Public Function basDataFileLink() As Boolean

     On Error GoTo Err_basDataFileLink

     Dim mdb As DAO.Database, mtbl As DAO.TableDef, strDataPath As String, 
strNewConnect As String

     Set mdb = CurrentDb

     Call basInfo("Linking data files.")
     basDataFileLink = False
     If IsNull(Me!txtDataFolder) = True Then Me!txtDataFolder = " "
     strDataPath = Me!txtDataFolder
     strNewConnect = "ODBC;DSN=Visual FoxPro Tables;SourceDB=" & 
strDataPath & 
";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"
     For Each mtbl In mdb.TableDefs
         'Only check this table link if this table IS linked.
         If InStr(mtbl.Connect, "DSN=Visual FoxPro Tables") <> 0 Then
             mtbl.Connect = strNewConnect ' & mtbl.Name
             mtbl.RefreshLink
         End If
     Next
     DBEngine(0)(0).TableDefs.Refresh

     mdb.Close
     Set mdb = Nothing
     basDataFileLink = True

Exit_basDataFileLink:
     DoCmd.Close acForm, "frmInfoForm"
     Exit Function

Err_basDataFileLink:
     Select Case Err
         Case 3011
             MsgBox "Unable to find all the data files.", vbCritical, "Link 
To Data Files"
         Case Else
             Call basErrorMsg("basDataFileLink")
     End Select
     Resume Exit_basDataFileLink

End Function

If anyone is interested in testing it for me I can send sample tables.


Regards

David Emerson
Dalyn Software Ltd
25 Cunliffe St, Churton Park
Wellington, New Zealand
Ph/Fax (04) 478-7456
Mobile 027-280-9348