David Emerson
newsgrps at dalyn.co.nz
Wed Feb 23 21:34:00 CST 2005
Thanks. Now I am getting closer. The code below is now working since I
closed the mdb variable.
When I run the code the connection strings are changed but there is now
another problem. When the tables are opened they still are showing the old
data. Closing the database and opening it in Access doesn't help. It is
only when I close Access down and reopen it that the new tables are appearing.
It seems that until Access itself is closed that the actual links to the
data tables are updated. How can I make the change occur at the same time
as the connection string is changed?
David
At 24/02/2005, you wrote:
>'verify if current path is correct for tblStudent
>debug.print DLookup("[Database]", "msysObjects", "[Name] = 'tblStudent'")
>See if this gives you correct path for linked table
>
>David Emerson wrote:
>
>>Here is what I have so far.
>>
>>Public Function basCheckLinksFS()
>>
>> Dim mdb As DAO.Database, mtbl As TableDef, strDataPath As String,
>> strNewConnect As String
>>
>> Set mdb = CurrentDb
>>
>> strDataPath = "d:\CLIENTS\PM SERVICES\DATA\20050216"
>> strNewConnect = "ODBC;DSN=Visual FoxPro Tables;SourceDB=" &
>> strDataPath &
>> ";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;;TABLE="
>>
>>
>> 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
>>
>>End Function
>>
>>
>>The problem is with the refreshlink command near the end. It returns
>>error 3011 - The Microsoft Jet database engine could not find the object
>>rs2YTD. I know that the strDatapath is correct and the foxpro dbf file
>>is in the folder.
>>
>>David
>
>--
>Marty Connelly
>Victoria, B.C.
>Canada
>
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
Regards
David Emerson
Dalyn Software Ltd
25 Cunliffe St, Churton Park
Wellington, New Zealand
Ph/Fax (04) 478-7456
Mobile 027-280-9348