Delete link and recreate link automatically 
Author Message
 Delete link and recreate link automatically

Hello,

Using a MSAccess97 (Front-end / back-end set-up) in a NT
environment, I need to break the link of linked tables and
relink them automatically.
Leaving aside the relinking, I have the following question
or issue about the 'breaking the link' issue ...
If I have 3 or even 10 tables, I can write in my VBA code
the actual name of the tables and do the job ... but if I
have a dynamic amount of tables and that, furthermore, the
name of the tables might also change ... then I will have
some problems ....
Is it possible to loop through all the tables (except the
ones starting with "nl_") and break the reference
regardless of the name or the amount of tables?

I hope someone can provide me with some pointers ...

thanks a million,

kindest regards,

Valerie.



Fri, 14 May 2004 21:25:43 GMT  
 Delete link and recreate link automatically
Hi again

I use code like this to solve a similar task:
(The Transferlink is probably not the best solution but it works in my
database.)

Sub RelinkTables(Path As String)
  Dim db As Database, t As Integer
  Dim Current As Database
  Dim varReturn

  varReturn = SysCmd(acSysCmdInitMeter, "Relinking tables", 5)
  varReturn = SysCmd(acSysCmdUpdateMeter, 1)

  Set Current = CurrentDb()
  For t = Current.TableDefs.Count - 1 To 0 Step -1
    If Left(Current.TableDefs(t).Name, 4) <> "Msys" And
Left(Current.TableDefs(t).Name, 3) <> "IS_" Then
      Current.TableDefs.Delete Current.TableDefs(t).Name
    End If
  Next

  varReturn = SysCmd(acSysCmdUpdateMeter, 2)

  If adh_accFileExists(Path & "TestData.mdb") Then
    Set db = Workspaces(0).OpenDatabase(Path & "TestData.mdb")
    For t = 0 To db.TableDefs.Count - 1
      If Left(db.TableDefs(t).Name, 4) <> "MSys" Then _
        DoCmd.TransferDatabase acLink, "Microsoft Access", Path &
"YourData.mdb", acTable, db.TableDefs(t).Name, db.TableDefs(t).Name
    Next
    db.Close
  End If

  varReturn = SysCmd(acSysCmdUpdateMeter, 3)

end sub


Quote:
> Hello,

> Using a MSAccess97 (Front-end / back-end set-up) in a NT
> environment, I need to break the link of linked tables and
> relink them automatically.
> Leaving aside the relinking, I have the following question
> or issue about the 'breaking the link' issue ...
> If I have 3 or even 10 tables, I can write in my vba code
> the actual name of the tables and do the job ... but if I
> have a dynamic amount of tables and that, furthermore, the
> name of the tables might also change ... then I will have
> some problems ....
> Is it possible to loop through all the tables (except the
> ones starting with "nl_") and break the reference
> regardless of the name or the amount of tables?

> I hope someone can provide me with some pointers ...

> thanks a million,

> kindest regards,

> Valerie.



Fri, 14 May 2004 23:26:23 GMT  
 Delete link and recreate link automatically
Thanks a lot, I will try your code out and tell you how
I'm doing. ...

What about breaking the link? is it possible to loop
through all the tables and disconnect them?
At the moment, I use one line to be repeated for each
table ... I need some piece of code that would look at the
table tables and go through each table .... return the
name of the table, then do the disconnection then move on
to the next table ...
here's my line:
DoCmd.DeleteObject acTable, "ACCESS"

As you can see .... disconnection is actually delete the
table reference ....

I hope you can help,

thanks in advance,

kindest,

Val.



Sat, 15 May 2004 19:26:41 GMT  
 Delete link and recreate link automatically
Thanks a lot, I will try your code out and tell you how
I'm doing. ...

What about breaking the link? is it possible to loop
through all the tables and disconnect them?
At the moment, I use one line to be repeated for each
table ... I need some piece of code that would look at the
table tables and go through each table .... return the
name of the table, then do the disconnection then move on
to the next table ...
here's my line:
DoCmd.DeleteObject acTable, "ACCESS"

As you can see .... disconnection is actually delete the
table reference ....

I hope you can help,

thanks in advance,

kindest,

Val.



Sat, 15 May 2004 19:27:01 GMT  
 Delete link and recreate link automatically
Public Sub UnLinkTables()

    Dim db As DAO.Database
    Dim tdfs As DAO.TableDefs
    Dim lngLoop As Long

    Set db = CurrentDb
    Set tdfs = db.TableDefs

    For lngLoop = tdfs.Count - 1 To 0 Step -1
        If Len(tdfs(lngLoop).Connect) > 0 Then
            tdfs.Delete (tdfs(lngLoop).Name)
        End If
    Next lngLoop

    Set tdfs = Nothing
    Set db = Nothing

End Sub

--

Brendan Reynolds


Quote:
> Thanks a lot, I will try your code out and tell you how
> I'm doing. ...

> What about breaking the link? is it possible to loop
> through all the tables and disconnect them?
> At the moment, I use one line to be repeated for each
> table ... I need some piece of code that would look at the
> table tables and go through each table .... return the
> name of the table, then do the disconnection then move on
> to the next table ...
> here's my line:
> DoCmd.DeleteObject acTable, "ACCESS"

> As you can see .... disconnection is actually delete the
> table reference ....

> I hope you can help,

> thanks in advance,

> kindest,

> Val.



Sat, 15 May 2004 23:37:54 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Deleted records when text file is linked as table (#deleted)

2. Automatically update links in Excel w/o prompting

3. Automatically Link Tables

4. Automatically Linking a Word Document to Journal Entry

5. Linking Durations to Update Automatically

6. Automatically acknowledging links between projects

7. Modify Word Graphic Link Automatically

8. Tool for automatically generating html links for whole website directory tree

9. Updating linked tables automatically.

10. Changing the linked table path without the linked table manager

11. Excel Linking and Excel/Access Linking

12. linking all fields in linked table?

 

 
Powered by phpBB® Forum Software