Posts tagged TableDef

display list of table from other database with DAO

Apr 8th

Posted by yussi ariefiyono in vba

No comments

this function will display list of table from other database, it come handy if you want to make a tool for inventory of all your db

Sub displayTable()
Dim dbs As Database
Dim otable As DAO.TableDef
Dim ItemName As String
Set dbs = OpenDatabase(“Db Name”)
For Each otable In dbs.TableDefs
If UCase(Left(otable.Name, 3)) <> “MSY” Then
ItemName = otable.Name
ItemName = Replace(ItemName, “,”, “–”)
debug.print ItemName
ItemName = “”
End If
Next otable
Set otable = Nothing
Set dbs = Nothing
End Sub
,

Access link table from different database with vba

Apr 8th

Posted by yussi ariefiyono in vba

No comments

Problem:
you have lots of database with link table in it, and in one time some of path are changed.. rather than change them manually,  i create function that can do that for me :)

Solution:
In this function you can specify the link you want to find and replace it with your new link. it save me lots of time..

Function RelinkTable(setDB As String, FINDtext As String, REPLACEtext As String)
Dim dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Set dbs = OpenDatabase(setDB)
Set Tdfs = dbs.TableDefs
‘Loop through the tables collection
For Each Tdf In Tdfs
If Tdf.SourceTableName <> “” And Tdf.Connect = “;DATABASE=” & FINDtext Then   ‘if its the linktable
Tdf.Connect = “;DATABASE=” & REPLACEtext ‘Set the new source
Tdf.RefreshLink ‘Refresh the link
End If
Next ‘Goto next table
Set dbs = Nothing
Set Tdfs = Nothing
End Function
,