vba
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
comment block in vba msaccess
Feb 8th
Enable the edit toolbar, hignlight lines to comment, and click the “comment block” icon on the edit toolbar (middle of toolbar). This will add the ‘ to whatever you highlight.
List of SendKeys
Feb 8th
| Key | Code |
|---|---|
| BACKSPACE | {BACKSPACE}, {BS}, or {BKSP} |
| BREAK | {BREAK} |
| CAPS LOCK | {CAPSLOCK} |
| DEL or DELETE | {DELETE} or {DEL} |
| DOWN ARROW | {DOWN} |
| END | {END} |
| ENTER | {ENTER}or ~ |
| ESC | {ESC} |
| HELP | {HELP} |
| HOME | {HOME} |
| INS or INSERT | {INSERT} or {INS} |
| LEFT ARROW | {LEFT} |
| NUM LOCK | {NUMLOCK} |
| PAGE DOWN | {PGDN} |
| PAGE UP | {PGUP} |
| PRINT SCREEN | {PRTSC} (reserved for future use) |
| RIGHT ARROW | {RIGHT} |
| SCROLL LOCK | {SCROLLLOCK} |
| TAB | {TAB} |
| UP ARROW | {UP} |
| F1 | {F1} |
| F2 | {F2} |
| F3 | {F3} |
| F4 | {F4} |
| F5 | {F5} |
| F6 | {F6} |
| F7 | {F7} |
| F8 | {F8} |
| F9 | {F9} |
| F10 | {F10} |
| F11 | {F11} |
| F12 | {F12} |
| F13 | {F13} |
| F14 | {F14} |
| F15 | {F15} |
| F16 | {F16} |
| Keypad add | {ADD} |
| Keypad subtract | {SUBTRACT} |
| Keypad multiply | {MULTIPLY} |
| Keypad divide | {DIVIDE} |
| Key | Code |
|---|---|
| SHIFT | + |
| CTRL | ^ |
| ALT | % |
Check if table is exist vba
Feb 8th
Problem:
Check if table is exist
Solution:
Option Compare Database
Public Function TableExists(strTable As String) As Boolean
Dim strName As String
On Error Resume Next
‘If table exists already then strName will be > “”
strName = CurrentDb.TableDefs(strTable).name
TableExists = Not (strName = “”)
End Function