David Emerson
newsgrps at dalyn.co.nz
Fri Feb 25 13:15:50 CST 2005
Gustav,
The files are Visual Foxpro dbf files.
Tried replacing CurrentDB with no luck.
Also tried delete and relink but also had the same result.
Have even tried ADO instead of DOA with the same result.
The problem seems to be with Access not actually recognising the new links
until Access is closed and opened again. Maybe it is something to do with
the connection code.
The offer is still open for me to send a sample file for others to test.
David
At 25/02/2005, you wrote:
>Hi David
>
>I have no firm advice on this. But you could try two things.
>First, can't you link dbf files directly (dBase x files)?
>Second, what happens if you replace CurrentDb with DBEngine:
>
> Set mdb = DBEngine(0)(0)
>
>If that adds nothing, you could try to not use RefreshLink but delete
>the tables and relink them.
>
>/gustav
>
>
> >>> newsgrps at dalyn.co.nz 24-02-2005 20:04:34 >>>
>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
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com