Yussi Ariefiyono
Posts tagged TableDef
display list of table from other database with DAO
Apr 8th
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 DatabaseDim otable As DAO.TableDefDim ItemName As StringSet dbs = OpenDatabase(“Db Name”)For Each otable In dbs.TableDefsIf UCase(Left(otable.Name, 3)) <> “MSY” ThenItemName = otable.NameItemName = Replace(ItemName, “,”, “–”)debug.print ItemNameItemName = “”End IfNext otableSet otable = NothingSet dbs = NothingEnd Sub
Access link table from different database with vba
Apr 8th
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 DatabaseDim Tdf As TableDefDim Tdfs As TableDefsSet dbs = OpenDatabase(setDB)Set Tdfs = dbs.TableDefs‘Loop through the tables collectionFor Each Tdf In TdfsIf Tdf.SourceTableName <> “” And Tdf.Connect = “;DATABASE=” & FINDtext Then ‘if its the linktableTdf.Connect = “;DATABASE=” & REPLACEtext ‘Set the new sourceTdf.RefreshLink ‘Refresh the linkEnd IfNext ‘Goto next tableSet dbs = NothingSet Tdfs = NothingEnd Function