Accessã®ãã¯ãã§ããŒã³ãŒãã¢ããªãäœã
RPAã§ããŒã³ãŒããšèšãããŠãæä»£ã§ãããå®ã®ãšãã倧æããAccessã§ããŒã³ãŒããªæ©èœã¯å®çŸãããŠãããããŸããããããDBãšããä»çµã¿ã«ã€ããŠããããæ«æããçµå±Excelã«æ»ã£ãŠRPAãšããéçç£çãªãã¿ãŒã³ãããã®ãäºå®ã§ãïŒäœããExcelã¯Power Queryã§ããŒã³ãŒãã®æé©åãå®çŸåºæ¥ãŸãïŒ
ããã§ãããŠãä»ã©ããã®Accessã®ãã¯ãæ©èœãã¡ãã£ãšã ãæã£ãŠã¿ãŠãããŒã³ãŒãã§ã¢ããªãäœã£ãŠã¿ãããšæããŸããAccessã«ã€ããŠã¯éå»èšäºãããã€ããããŸããããŸãã¯ä»¥äžã®ãšã³ããªãŒããåãçµãã§ã¿ããšè¯ãããšæããŸãã
ç®æ¬¡
- 1 ä»åå©çšãããã¡ã€ã«ç
- 2 Accessã®ãã¯ãã®æŠèŠ
- 3 Accessãã¯ããè²ã 詊ããŠã¿ã
- 4 AccessåŠçã®èªåå
- 5 é¢é£ãªã³ã¯
ä»åå©çšãããã¡ã€ã«ç
- Accessã§ããŒã³ãŒãã¢ããªãäœã - Microsoft Accessãã¡ã€ã«
Accessãã¡ã€ã«ãªã®ã§Accessãå¿ èŠã§ãããå®è¡ããã ãã§ãããªãã°ãAccess Runtimeã§ãåäœããŸããäœæè ã ãAccessã䜿ããé åžããå Žåã¯çžæã«ã¯Access Runtimeã䜿ã£ãŠãããã®ãã³ã¹ãçã«ãè¯ãã§ãããã
ãŸãããã®ãã¯ããããã¯ã°ã©ãŠã³ãã¯VBAãã®ãã®ãªã®ã§ãåããŠã®èµ·åæã«ãããã¯ãããããšãããã®ã§è§£é€ãå¿ èŠã§ãã
VBAãã¯ããæ¢å®ã§ãããã¯ãããçºã®å¯Ÿçæ¹æ³
Accessã®ãã¯ãã®æŠèŠ
ãã¯ããšèããšãExcelã®ãã®ãã¯ãïŒæäœãèšé²ããŠVBAã®ã³ãŒããšããŠæ®ãæ©èœïŒãæãæµ®ãã¹ã人ãå€ãã®ã§ãããAccessã«æèŒãããŠããã¯ãã¯å šãã®å¥ç©ã§ãããã£ãŠãVBAã®ã³ãŒããšããŠæ®ããã®ã§ãç¡ãã§ãããã©ã¡ãããšãããšFileMakerã®ã¹ã¯ãªãããšããæ©èœã«è¿ãååšã§ããã¬ãŽãããã¯ã®ããã«ãçšæãããŠãåœä»€ã®ãããã¯ãç¹ããŠãïŒã€ã®åŠçãå®çŸããã®ããAccessã®ãã¯ãæ©èœã§ãã
ãã¯ããšããŠäœã£ãŠãããŠããã©ãŒã ã®ãã¿ã³ã«å²ãåœãŠããšãã£ãäºãåºæ¬ã«ãªããšæããŸããããã€ã¢ãã°ã衚瀺ããŠåŠçãåå²ãšãã£ããã®ãããã€ãçšæããŠãããŠåŒã³åºããŠäžããŸããç»é¢ã¯ãªãã³ã¡ãã¥ãŒã®ãäœæãâããã¯ãããéããšãããã¯ããã¶ã€ã³ããšããã¿ãã衚瀺ãããã®ã§ãããã§äœã蟌ãã§ããããšã«ãªããŸãã
å³ïŒãã¯ãç·šéç»é¢
Accessãã¯ããè²ã 詊ããŠã¿ã
çšæãããŠããã¯ãã®ã¹ãããã®ãã¡ãéåžžã«ãã䜿ããã®ã ããããã¯ã¢ããããŠã¿ãŸãããããããçµã¿åãããŠïŒã€ã®åŠçãäœããèªååãè¡ãããšã«ãªããŸãã
ãã¯ãã®åºæ¬
ã·ã³ãã«ãªãã¯ããäœã
ãŸãã¯ã¡ãã»ãŒãžãåºãã ãã®ãã¯ããäœã£ãŠã¿ãŸãïŒã¢ã¯ã·ã§ã³ã«ã¿ãã°ããæ€çŽ¢ããŠè¿œå ãåºæ¥ãŸãïŒããã¯ãäœæç»é¢ã«ãŠ
- ãã«ããŠã³ãã¯ãªãã¯ããŠããã¡ãã»ãŒãžããã¯ã¹ããéžæãã
- ã¹ãããã远å ãããã®ã§ãã¡ãã»ãŒãžãã¡ãã»ãŒãžã®çš®é¡ãã¿ã€ãã«ãèšå ¥ãã
- éããŠãã¯ããä¿åããïŒä»åã¯å€é£¯ãšããååã§ä¿åããŸããïŒ
- åç·šéããå Žåã¯ãå·Šãµã€ãããŒã®å¯Ÿè±¡ã®ãã¯ããå³ã¯ãªãã¯âãã¶ã€ã³ãã¥ãŒã§å¯èœã«ãªããŸãã
å³ïŒãŸãã¯æ» è¶ã·ã³ãã«ãªãã¯ããã
ãã©ãŒã ã«å²ãåœãŠã
åé ã§äœã£ããã¯ãããã©ãŒã ã®ãã¿ã³ã«å²ãåœãŠããšãã£ãå Žåã¯ä»¥äžã®æé ã§æ§ç¯ããŸãã
- ãã©ãŒã ãäœæãããã¿ã³ãé 眮ãã
- ã³ãã³ããã¿ã³ãŠã£ã¶ãŒããèµ·åããã®ã§ãããã®ä»ããéžã³ããã¯ãã®å®è¡ããéžæãã
- 次ãžãã¯ãªãã¯ããããäœã£ãŠããããã¯ãã®äžèЧãåºãã®ã§éžæãã
- 次ãžãã¯ãªãã¯ããããã¢ã€ã³ã³ãæååãæå®ããŠå®äºãã¯ãªãã¯ãã
ãã©ãŒã ãä¿åããŠããã©ãŒã ãã¥ãŒã«åãæ¿ããŠããã¿ã³ãã¯ãªãã¯ãããšãã¯ããå®è¡ãããŠãã¡ãã»ãŒãžã衚瀺ãããã¯ãã§ãããã®ãã¯ãã®æå®ã¯ãå³ãµã€ãããŒã®ããããã£ã·ãŒãã®ãã¯ãªãã¯æãã€ãã³ãã«åã蟌ã¿ãã¯ããšããŠç»é²ãããŠããŸããä»ã®ã€ãã³ãã«ç»é²ãããå ŽåïŒäŸãã°ãã©ãŒã«ã¹ååŸæãªã©ã®ã€ãã³ãïŒãªã©ã¯ããã®ããããã£ã·ãŒãããç»é²ãããšè¯ãã§ãããã
å³ïŒã³ãã³ããã¿ã³ãŠã£ã¶ãŒã
å³ïŒåã蟌ã¿ãã¯ããšããŠç»é²ãããŠãã
ãªãã³ã«èªäœãã¯ããç»é²
èªåãäœæãããã¯ãããã¡ãã¡Accessã®å·Šãµã€ãããŒããæ¢ãåºããŠå®è¡ãããšããã®ããçµæ§æéãé£ãã§ãã£ããé¢åãªããšããããŸãããªã®ã§éåžžã«ãã䜿ããã¯ããèªåãç¬èªã«çšæãããªãã³ã®ã¿ãã«ç»é²ããç¶æ ã«ããŠããã°ããã€ã§ãã¯ã³ã¿ããã§å®è¡åºæ¥ãŸããç¹ã«ã¢ããªãäœã蟌ãã§ãããšãã®éèŠãçãŸããŸãã
ç¬èªã«ãªãã³ã¿ããçšæããŠç»é²ããæé ã¯ä»¥äžã®éãã§ãã
- ãã¡ã€ã«âãªãã·ã§ã³ãéãããªãã³ã®ãŠãŒã¶èšå®ãéã
- ã³ãã³ãã®éžæã§ãã¯ããæå®ãã
- èªåãäœã£ãŠããããã¯ããåºãŠããã®ã§éžãã§ãã
- å³åŽã®ããã«ã®äžã«ãããæ°ããã¿ãããã¯ãªãã¯ããŠãååã®å€æŽã§ã¿ãã®ååãæå®ãã
- 4.ã®ã°ã«ãŒãåãååã®å€æŽã§é©åœãªã°ã«ãŒãåãã€ããŠãã
- 3.ã§éžãã ãã¯ããçãäžã«ããã远å ãã®ãã¿ã³ã§ã°ã«ãŒãã«è¿œå ãã
- OKãã¯ãªãã¯ãããšæ°ãããªãã³ã¿ãã远å ãããèªåã®ãã¯ãã衚瀺ããã
åãèŠé ã§ã¯ã€ãã¯ã¢ã¯ã»ã¹ããŒã«ããŒã«ç»é²ããäºãå¯èœã§ãã
å³ïŒãªãã³ã«èªäœãã¯ãã远å ã§ãã
å³ã¯ãªãã¯ã¡ãã¥ãŒã«ãã¯ããç»é²
Accessã®å³ã¯ãªãã¯ã¡ãã¥ãŒã«ãã¯ããå®è¡ããã¡ãã¥ãŒã远å ããæ¹æ³ã§ãããªãã³ãããçŽæ¥çã«äœæ¥ãããå Žåããã¡ãã®ã»ããã¹ã ãŒãºã«å®è¡ãåºæ¥ãã®ã§ãããçšåºŠã¢ããªã®å 容ãå å®ããŠãããè£ åããŠã¿ããšè¯ããããããŸããã
äœãæ¹ã¯ä»¥äžã®éããä»åã¯è¡šç€ºããå 容åæç»ãããã®ïŒRefreshããïŒãå³ã¯ãªãã¯ã§è£ åããŠã¿ãããšæããŸãã
- ãã¯ãã®ç·šéç»é¢ãéã
- ãµããã¯ãã®ã¹ãããã远å ãããååã«ã¯ã&éããããšå ¥ããŸãã
- æ°ããã¢ã¯ã·ã§ã³ã®è¿œå ã«ãŠã£ã³ããŠãéããã¹ãããã远å ããäžèŠ§è¡šãéããããæå®ãã
- ä¿åããïŒã¯ããŒãºãšåœåããïŒ
- è€æ°ãµããã¯ããç»é²ããããšã§ã¡ãã¥ãŒãããã€ãç»é²å¯èœã§ãã
åŒãç¶ããäžèšã§äœã£ããã¯ããã¡ãã¥ãŒã«è¿œå ãããã¯ããäœæããŸãã
- ãã¯ãã®ç·šéç»é¢ãéã
- ã¡ãã¥ãŒã®è¿œå ã¹ããããèŠã€ããŠè¿œå ã
- ã¡ãã¥ãŒåã«ãã©ãŒã ã³ãã³ããšåœåããŠãã¡ãã¥ãŒãã¯ãåã«ã¯åè¿°ã§äœã£ããã¯ããŒãºãã远å ãã
- æŽæ°ã³ãã³ããšããåç§°ã§ä¿åãã
ããã§ã¡ãã¥ãŒã®æºåãåºæ¥ãã®ã§ã以äžã®æé ã§ãã©ãŒã ã«ã¡ãã¥ãŒã远å ããŸãã
- äºãäœã£ãŠããããã©ãŒã ããã¶ã€ã³ãã¥ãŒã§éã
- ããããã£ã·ãŒããéãããã®ä»ã¿ãã®äžã«ãããã·ã§ãŒãã«ããã¡ãã¥ãŒãããã¯ããã«ãªã£ãŠãã®ã確èªãã
- ããªãããã·ã§ãŒãã«ããã¡ãã¥ãŒããŒãã®é ç®ã§ãçŽæ¥ãæŽæ°ã³ãã³ãããšå ¥åãã
- å¥ã®ããããã£ãè§Šã£ãŠããéãã
ããã§ããã®ãã©ãŒã ã«å¯ŸããŠå³ã¯ãªãã¯ã¡ãã¥ãŒãç»é²ãããŠãå³ã¯ãªãã¯ãããšãã®å 容ãåºãŠããïŒäœãæ¢åã®å³ã¯ãªãã¯ã¡ãã¥ãŒã¯åºãŠããªããªãã®ã§èŠæ³šæïŒ
å³ïŒå³ã¯ãªãã¯ãããŒã³ãŒãã§äœãã
ã¯ãšãªãéã
äºãäœæããŠãããåçš®ã¯ãšãªïŒSelect, Insert, Update, Deleteãªã©ïŒãå®è¡ããŸããããã«ã¯ãªãã¯ããäºã§å¯Ÿè±¡ã®ã¯ãšãªãå®è¡ãããããã«ãªãã®ã§ãå šäœã®ïŒã¹ããããšããŠå©çšããæ©äŒã¯éåžžã«å€ããã®ã«ãªããŸãã
åœç¶å©çšããããã«ã¯äºãã¯ãšãªãšãã®å ã«ãªãããŒãã«ãå¿ èŠãšãªããInsertãUpdateãªã©ã®å Žåã¯ä»ã®ããŒãã«ãå¿ èŠã«ãªããŸãã
éãå Žåã«ãèªã¿åãå°çšã«ããããç·šéã¢ãŒãã«ããããè²ã ãšæå®ã¯å¯èœã§ããInsertãUpdate, Deleteã®å Žåã¯å®è¡æã«ç¢ºèªã®ãã€ã¢ãã°ãåºãŸãã®ã§èªååãããŠãå Žåã«ã¯èŠæ³šæããã®ç¢ºèªã¡ãã»ãŒãžãé衚瀺ã«ãããå Žåã¯ã以äžã®å Žæã®ãã§ãã¯ããªãã«ããå¿ èŠããããŸãã
- ã¡ãã¥ãŒãããã¡ã€ã«âãªãã·ã§ã³ãéã
- ã¯ã©ã€ã¢ã³ãã®èšå®ã®äžã«ããç·šéâ確èªé ç®ãèŠã€ãã
- ã¢ã¯ã·ã§ã³ã¯ãšãªã«ãã§ãã¯ãå ¥ã£ãŠãã®ã§ããããå€ã
VBAã®å Žåã¯ãDoCmd.SetWarnings Falseã§é衚瀺ã«ãªããŸãã
å³ïŒã¯ãšãªãéãã¹ããã
å³ïŒç¢ºèªãã€ã¢ãã°ã®èšå®ç®æ
ã¡ãã¥ãŒã³ãã³ãã®å®è¡
Accessã«æšæºçã«çšæãããŠãæ©èœãåŒã³åºãçºã®ãã®ã§ããšããããã®äžã§ãã䜿ããã®ãšããã
- ä¿åæžã¿ã®ãšã¯ã¹ããŒãæäœ
- ãã£ã«ã¿ãŒé¢ä¿ã®åŠç
- Excelãã¡ã€ã«ã®ã€ã³ããŒã/ãªã³ã¯
åçŽã«åŒã³åºããŠãã ããªã®ã§ãç¹ã«çްããæå®ãåºæ¥ããããããªãã®ã§ããããã¡ãã¡ãªãã³ãã察象ã®åŠçãèŠã€ããŠäººéãå®è¡ãããããäºã䜿ãã§ãããæ©èœãã»ããããŠããã°ãã¹ã ãŒãºãªåŠçã«ç¹ããããšãå¯èœã§ãã
ããã·ãŒãžã£ã®å®è¡
VBAã«ãŠæšæºã¢ãžã¥ãŒã«ã远å ããèªåã§é¢æ°ãã³ãŒããçšæããŠãå Žåã察象ã®ãã¿ã³ã«å¯ŸããŠåŠçãçŽæ¥èšè¿°ããã®ã§ã¯ãªãããã®é¢æ°ãåŒã³åºããŠåŠçãããå Žåã«äœ¿ããŸããã®ã§ãããã¯ããŒã³ãŒãã§å©çšãããã®ã§ã¯ãªããå©äŸ¿æ§åäžã®ããã«äœ¿ãã¹ãããã§ãã
äºåã«VBAã®åŠçãçšæãå¿ èŠã§ããèªåèªèº«ã¯ãã©ãŒã ã®åé ç®ã«çŽæ¥çã«ã³ãŒããæžãäºãå€ãã®ãšã颿°åŒã³åºããããããã£ã·ãŒãã«çŽæ¥ã€ãã³ããšããŠèšè¿°ããäºãå€ãã®ã§ãããŸã䜿ãããšã¯ãããŸãããã倧ããªé£ç¶ããåŠçããããããªå Žé¢ã§äœ¿ãå Žåã«ãã¯ããšããŠçšæããŠãããŠå®è¡ããäºããããŸãã
æžåŒèšå®ãä¿æãããŸãŸãšã¯ã¹ããŒã
åè¿°ã®æ¹æ³ã§PDFåºåãããå Žåããã¡ãã¡ã¯ãšãªãéããŠäººéãä»åšããŠåŠçãããå¿ èŠããããŸããããã¡ãã®ã¹ãããã®å ŽåãçŽæ¥çã«PDFåºåãå šèªååããããšãå¯èœããŸããPDFã ãã§ãªãåŠçãããããŒãã«ãã¯ãšãªã®å 容ãExcel圢åŒçã§ãšã¯ã¹ããŒãããããšãå¯èœã«ãªããŸãã
- ãã¯ãäœæç»é¢ãéã
- æžåŒèšå®ãä¿æãããŸãŸãšã¯ã¹ããŒãã®ã¹ãããã远å ãã
- éãã¯ãšãªãããŒãã«ãæå®ãåºåãã¡ã€ã«åœ¢åŒãéžæããïŒPDFãExcelãªã©ïŒ
- åºåãã¡ã€ã«ã«å¯ŸããŠãåºåå ã®ãã«ãã¹ããã¡ã€ã«åãŸã§å«ããŠæå®ãã
- èªåèµ·åã¯ãããã®ãŸãŸã§OK
ããã§å®è¡ãããšã4.ã®å Žæã«ãšã¯ã¹ããŒãããããŠå®äºããŸãã
å³ïŒçްããåºåæå®ãããäºãå¯èœ
ãã©ãŒã ãéã
Accessã¯Excelãšã¯ç°ãªããéåžžããŸãããŒãã«/ã¯ãšãªã®ãŸãŸäœæ¥ãããããšã¯å°ãªãã§ããå ¥åçšãæœåºçšã®ãŠãŒã¶ã€ã³ã¿ãŒãã§ãŒã¹ãšããŠãã©ãŒã ãçšæããŠãããã«ãã¿ã³ããµããã©ãŒã ãšã¯ãšãªã衚瀺ããŠãäœæ¥ãããããã«äœã蟌ãã®ãéåžžã®äœ¿ãæ¹ã§ãã
ãããã®ãã©ãŒã ãéãã³ãã³ããçšæããŠãããšéåžžã«æ¥œã§ãã以äžã®æé ã§ãã©ãŒã ãéãããã«ããŸããåæã«ãã£ã«ã¿ãããããšãåºæ¥ãŸããããã®å Žåéåžžã¯è¡šåœ¢åŒã§ãã©ãŒã ãäœã£ãŠããå¿ èŠããããŸãã
- ãã¯ãç·šéç»é¢ãéã
- ãã©ãŒã ãéãã¹ãããã远å ãã
- ãã©ãŒã åããã¥ãŒã®åœ¢åŒãæœåºæ¡ä»¶ãæå®ãããå Žåã¯Whereæ¡ä»¶ãããŒã¿ã¢ãŒããæå®ããŸãã
- ä¿åãã
å®è¡ãããšããã©ãŒã ãéããŸããæ¡ä»¶åŒãæå®ããŠãå Žåã¯ãäºããã£ã«ã¿ããçµæã衚瀺ãããããã«ãªããŸãã
å³ïŒãã£ã«ã¿ãŒãæå®ããäºãå¯èœ
å³ïŒãã©ãŒã ãéãããšãåºæ¥ã
åãæ
Accessã®ãã¯ãã¯çãæã«æãå±ããããªã¹ããããããŸãçšæãããŠããªãã®ã§ãèªååãèªç±åºŠãåŸãçºã«ã¯VBAã¯æ¬ ãããŸãããããããããã€ãã®ãã¯ãã®ã¹ããããçµã¿åãããäºã§å®çŸå¯èœãªããšããããŸãã
ãã£ã«ã¿ãŒã®å®è¡
ããŒãã«ãã¯ãšãªãéããŠãæ¡ä»¶æå®ãããŠãã£ã«ã¿ããäºãå¯èœã§ããã¯ãšãªãããŒãã«ãéãâãã£ã«ã¿ã®å®è¡ãšããã¹ãããã«ãŠãæ¡ä»¶ãæå®ããäºã§ãèªåçã«ãã£ã«ã¿ããçµæã衚瀺ããäºãå¯èœã«ãªããŸãã
- ãã¯ãäœæç»é¢ãéã
- ããŒãã«ãéãã¹ãããã远å ãããã¥ãŒã¯ãããŒã¿ã·ãŒããã¥ãŒããšããŠæå®ãã
- ãã£ã«ã¿ãŒã®å®è¡ã¹ãããã远å ãã
- ãã£ã«ã¿åã¯é©åœã«ä»ããŠã以äžã®ãããªWhereæ¡ä»¶åŒã«ã¯ååãšå€ããã£ãŠåŒã远å ãã
1[ååã¿ã€ã]=0
ååã¿ã€ãã0ã®ãã®ã ããæœåºãã - è€æ°ã®æ¡ä»¶ãä»ãããå Žåã¯ãandãorãã€ããŠä»¥äžã®ããã«èšè¿°ãã
1[ååã¿ã€ã]=0 And [äŸ¡æ Œ]>=600 - ä¿åããŠéãã
å³ïŒWhereæ¡ä»¶ã§æœåºæ¡ä»¶ãèšè¿°ãã
ããŒãã«ãã¯ãšãªãå°å·ãã
ã¯ãšãªãéãâå°å·ãå®è¡âãã¬ãã¥ãŒãéãããšããïŒé£ã®ã¹ãããã§ããããå°å·ãã¬ãã¥ãŒãéããããšããã¹ãããã¯ååšããªãã®ã§ã以äžã®ããã«æ§ç¯ããŸãã
- ãã¯ãäœæç»é¢ãéã
- ã¯ãšãªãéãã¹ãããã远å ãããã¥ãŒã¯ãå°å·ãã¬ãã¥ãŒããšããŠæå®ãã
- ãªããžã§ã¯ãã®å°å·ã¹ãããã远å ãã
- å°å·ã®å®è¡ãPDFåºåã®å Žåã¯ä¿åå Žæã®æå®ãªã©ã¯äººåã§è¡ãå¿ èŠãããã
- 4.ã®åŠçãå®äºãããããŠã£ã³ããŠãéããã¹ãããã远å ããã¯ãšãªãæå®ããŠå°å·ãã¬ãã¥ãŒãéããã
ãããããã¬ããŒããçšæããã«ãã£ã«ã¿ããçµæããã€ã¬ã¯ãã«å°å·ããŠãéãããŸã§ã®åŠçã§ãããPDFãªã©ã®å Žåã¯ä¿åããå Žæããã¡ã€ã«åã¯ãã¯ãããã¯æå®ãåºæ¥ãªãã®ã§ã人éã®äœæ¥ãéäžã§å¿ èŠã§ãã
å³ïŒå°å·ããŠéãããšããæµããäœã
æ¡ä»¶åå²
IFã«ããæ¡ä»¶åå²ãªã©ãå€å®ããæ¡ä»¶ã«åèŽããŠãå Žåã«ãã¯ããå®è¡ãããšãã£ãã±ãŒã¹ã§å©çšããŸãã以äžã®äºäŸã¯ãã©ãŒã äžã®ãã§ãã¯ããã¯ã¹ããªã³/ãªãã®æã«å€å®ããŠå®è¡ããäºäŸã«ãªããŸãã
- ãã©ãŒã äžã«ãã§ãã¯ããã¯ã¹ãïŒå远å ããããã®æãå³ãµã€ãããŒã®ããããã£ã·ãŒãã®ãååãã®æ¬ã®æååãæ§ããŠããïŒãã§ãã¯2ãšãã£ããããªååãã€ããŠãïŒ
- ãã®æããã§ãã¯ããã¯ã¹ã®æ¢å®å€ã«ãTrueããªã©ãå ¥ããŠãããŸãããã
- ãã¯ãäœæç»é¢ãéããŠãã¢ã¯ã·ã§ã³ã«ã¿ãã°ãããIFããéžæããŠã¹ãããã远å ãã
- IFã®é
ç®å
ã«ä»¥äžã®ãããªæ¡ä»¶åãèšè¿°ãã
1IF [ãã§ãã¯2] = true - æ°ããã¢ã¯ã·ã§ã³ã®è¿œå ã«ãŠããã¯ãã®å®è¡ãéžã³ãäºãäœã£ãŠããããã¯ããæå®ãã
- Trueãããªãå Žåã®åŠçã¯ãElseã®è¿œå ãElse IFã®è¿œå ãã¯ãªãã¯ããŠããã«å€å®ã远å ããŠããã
- ãã¯ããä¿åããŠéãã
- ãã©ãŒã äžã«ãã¿ã³ãé 眮ããŠãIFå€å®ã®ãã¯ããå²ãåœãŠã
ããã§ã¯ãªãã¯ãããšããã§ãã¯ããã¯ã¹2ããã§ãã¯ãããŠãæã«ãã¿ã³ãã¯ãªãã¯ãããšããã¯ããå®è¡ãããããã«ãªããŸãã
å³ïŒIFã§ãã§ãã¯ããã¯ã¹ã®ç¶æ ãå€å®
ç¹æ®ãªãã¯ã
Accessã®ãã¯ãã«ã¯ç¹æ®ãªãã¯ãã2ã€çšæãããŠããŸããAutoexecãšAutokeysã®2ã€ãããã§ãåè ãèµ·åæã«èªåçã«å®è¡ããããã¯ãã§ãåŸè ããã¡ã³ã¯ã·ã§ã³ããŒã«å²ãåœãŠããã¯ãã«ãªããŸãã
Autoexec
ãã¯ãã®ååãAutoexecã«ããã ãã§ãèªåçã«èµ·åæã«å®è¡ãããããã«ãªããã¯ãã§ããè²ã ãªåæåã§ãã£ãããèµ·åãããèªåçã«åŠçãããããããªå 容ãèšè¿°ããäºã§ããŠãŒã¶ã®ååã®æéãçãããããã®ãç®çã§å©çšããŸãã
代衚çãªäœ¿ãæ¹ã¯ãèµ·åããæã«ããã²ãŒã·ã§ã³ãŠã£ã³ããŠãé衚瀺ã«ããããšãã£ãå¶åŸ¡ã«äœ¿ãããšãå€ããšæããŸããèªåã®å Žåã¯VBAã³ãŒããæžããŠãã®ã§ããã®é¢æ°ãåŒã³åºããŠèµ·åæã®åæåã§ããå©çšããŠããŸãã
ã¿ã¹ã¯ã¹ã±ãžã¥ãŒã©çãšçµã¿åãããŠãèªåçã«æå®æå»ã«Accessã®ãã¡ã€ã«ãéããŠãAutoexecã§èªåçã«åŠçãªããŠä»çµã¿ã«äœ¿ãã®ãäœæ¥èªååã«æå¹ã§ãã
Autokeys
ããŒããŒãã®ã·ã§ãŒãã«ããããŒã®çµã¿åããã§èªåçã«ãã¯ããå®è¡ããããã«ããçºã®ãã¯ããAutokeysã§ããæ ã«ããäžã€å®è¡éšéã®ãã¯ããçšæããå¿ èŠããããŸããããã§èŠå®ããã·ã§ãŒãã«ããããŒãåªå ãšãªããããæšæºã®ã·ã§ãŒãã«ããããŒãšåãå Žåããã¡ãã¯ç¡å¹åãããã®ã§æ³šæã
以äžã®æé ã§äœæããŸããä»åã¯F1ããŒã§å€é£¯ãã¯ããå®è¡ããããã«ããŸãã
- ãã¯ãäœæç»é¢ã§ããã«ããŠã³ããããµããã¯ããã远å ãã
- ãµããã¯ãã®åç§°ã«ã{F1}ããšå ¥å
- åãã»ã¯ã·ã§ã³å ã«ããæ°ããã¢ã¯ã·ã§ã³ã®è¿œå ãããããã¯ãã®å®è¡ãã远å ãã
- ãã¯ãåã«ã¯ãã§ã«äœã£ãŠãããã¯ããæå®ãã
- ä¿åããŠäžæŠãã¡ã€ã«ãéãã
- ãã¡ã€ã«ãéãçŽããšèªåçã«AutoKeysãå®è¡ãããŠãF1ããŒãæŒããšãã¯ããå®è¡ãããããã«ãªã
ãã®2.ã®æ§æã¯å ¬åŒãµã€ãã®ããã¥ã¡ã³ãã«èšèŒãããŠããã®ã§ãããŒã®çµã¿åãããªã©ãæ§ç¯ããäºãå¯èœã§ãã
å³ïŒF1ããŒã§ãã¯ããå®è¡ã§ãã
AccessåŠçã®èªåå
ã¿ã¹ã¯ã¹ã±ãžã¥ãŒã©ã䜿ã£ãŠå®æå®è¡
宿çã«èªåçã«ç¹å®ã®Accessãã¡ã€ã«ãèµ·åããŠåŠçããããå Žåãã¿ã¹ã¯ã¹ã±ãžã¥ãŒã©ã䜿ã£ãŠèµ·åãããäºãå¯èœã§ããåè¿°ã®Autoexecãã¯ããšçµã¿åãããŠãåŠçã®æåŸã«Accessã®çµäºãšãã£ãã¹ãããã远å ããŠããã°ãã¿ã¹ã¯ã¹ã±ãžã¥ãŒã©ã®æéèšå®æ¬¡ç¬¬ã§æ§ã ãªãã¿ãŒã³ã§ãAccessã§åŠçãããããäºãå¯èœã«ãªã£ãŠããŸãã
- ã¿ã¹ã¯ã¹ã±ãžã¥ãŒã©ãèµ·åãã
- å³ãµã€ãããŒãããã¿ã¹ã¯ã®äœæããã¯ãªãã¯ãã
- ååãé©åœã«å ¥ããŠãããªã¬ãŒã¿ããã¯ãªãã¯ãã
- æ°èŠãã¿ã³ãã¯ãªãã¯ãã
- ãã®ããªã¬ãŒç»é¢ã§ãæ¯æ¥ãæ¯æãªã©ã®æéèšå®ãã»ããããŸãã
- ã»ãããããããšã®ç»é¢ã«æ»ããæäœã¿ããéã
- æ°èŠãã¿ã³ãã¯ãªãã¯ãã
- æäœã¯ããã°ã©ã ã®éå§ã§ããã®åç §ãã¿ã³ãã¯ãªãã¯ããŠãaccess.exeãæ¢ããŠæå®ããïŒéåžžã¯ã"C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE"ã«ããïŒ
- åŒæ°ã«éãããaccdbãã¡ã€ã«ã®ãã«ãã¹ãå ¥ããïŒäŸïŒ"C:\Users\ãŠãŒã¶å\Desktop\Database4.accdb"ïŒ
- OKãã¯ãªãã¯ããŠéãã
ããã§æå®ã®ããªã¬ãŒã«åèŽããæå»ã«èªåçã«Accessãã¡ã€ã«ãèµ·åããããã«ãªããŸãã
å³ïŒããªã¬ãŒèšå®ç»é¢
å³ïŒã¿ã¹ã¯ã¹ã±ãžã¥ãŒã©ã«ç»é²ãããæ§å
VBSã䜿ã£ãŠãã¯ããå®è¡
å¥ã®ããã°ã©ã ããAccessã®ãã¡ã€ã«ãèµ·åããŠãäžã«å ¥ã£ãŠããã¯ããå®è¡ããããšããã±ãŒã¹ããããŸããAccessãæäœããå ŽåCOMãçµç±ããå¿ èŠããããŸãããVBScriptã®å Žåãããå¯èœã«ãªãã®ã§ãä»ã®ããã°ã©ã ããåŒã³åºãããå ŽåãVBSãå©ãããšã§å®çŸãå¯èœã§ãã
以äžã®ãããªã³ãŒããèšè¿°ããããšã§åŒã³åºããå¯èœã§ãã
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Dim accessman Set accessman = CreateObject(âAccess.Applicationâ) âAccessã®ãã¡ã€ã«ãèµ·åãã accessman.visible = true accessman.OpenCurrentDatabase âC:\Users\ãŠãŒã¶ãŒå\Desktop\ãã¯ãå®è¡çš.accdbâ WScript.Sleep 7000 âãã¯ãå®è¡ accessman.Application.Run "å€é£¯" WScript.Sleep 7000 âAccessãã¡ã€ã«éãã accessman.Quit 'çµäºåŠç set AcApp = Nothing |
ããã§ãã¢ã¯ã»ã¹ãèµ·åãããã¡ã€ã«ãéããäžã«ãããå€é£¯ããã¯ããå®è¡ãããŸãã
ã³ãã³ãã©ã€ã³ã¹ã€ãã
Accessã¯èµ·åæã«ã³ãã³ãããã³ããããå®è¡ããå Žåãã³ãã³ãã©ã€ã³ã¹ã€ãããšåŒã°ãããã®ã䜿ã£ãŠãå¶åŸ¡ããäºãå¯èœã«ãªã£ãŠããŸããäž»ã«èªååã§äœ¿ãã¹ã€ãããšãããã/xã¹ã€ãããã/cmdã¹ã€ãããã®ïŒçš®é¡ã«ãªããŸããåè¿°ã®ã¿ã¹ã¯ã¹ã±ãžã¥ãŒã©ãVBSãšçµã¿åãããŠäœ¿ãäºã§ãèµ·åæã«èªåçã«ãã¯ããå®è¡ããããšãã£ãäºãå¯èœã«ãªããŸãã
â»ä»ã«ãããã€ãå¶åŸ¡çšã®ã³ãã³ãã©ã€ã³ã¹ã€ãããçšæãããŠããŸãïŒèªã¿åãå°çšã§éãããçãªã©ïŒ
/xã¹ã€ãã
ãã¡ãã¯åçŽã§ãã³ãã³ãã©ã€ã³ãããã¡ã€ã«ãéãå Žåã«ã以äžã®ããã«ã¹ã€ãããšãã¯ãåãç¹ããäºã§ãèµ·åæã«èªåçã«å¯Ÿè±¡ã®ãã¯ããå®è¡ãããäºãã§ããããã«ãªããŸãã
1 |
c:Â¥usersÂ¥usernameÂ¥desktopÂ¥test.accdb /x ãã¯ãå |
ãããå®éã«ã³ãã³ãããã³ããããå®è¡ããå Žåã«ã¯ã以äžã®ãããªåœ¢ã«ãªãã
1 |
"C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE" "C:\Users\hiroyuki\Desktop\Database4.accdb" /x å€é£¯ |
Autoexecã«é Œãããåçã«ãã¯ããå®è¡åºæ¥ãã®ã§ãç¶æ³ãçšéã«å¿ããŠåŠçãåããŠåããã¡ã€ã«ãå®è¡ãããå Žåã«æå¹ã§ãã
/cmdã¹ã€ãã
ããã¯ãAutoexecãã¯ãã«å¯ŸããŠåŒæ°ãæž¡ããŠå®è¡ãããäºãåºæ¥ãããã«ããã¹ã€ããã§ãå®éã«äœ¿ãå Žåã«ã¯æšæºã¢ãžã¥ãŒã«ã«ãŠäºãVBAã§é¢æ°ãçšæããŠããå¿ èŠããããŸãã以äžã®æé ã§äœæããŸãã
- 以äžã®ãããªé¢æ°ãVBAã§çšæããŠãã
1234Public Function autofuncman()'åŒæ°ãåãåã£ãŠã¡ãã»ãŒãžã衚瀺MsgBox (Command)End Function
CommandãåŒæ°ã®å ¥ã£ãŠããå Žæã«ãªããŸããïŒå®éã«ã¯ããã¯Command颿°ãšåŒã¶ãã®ã§ãåŒæ°ã®å€æ°ã§ã¯ãªãïŒ - Autoexecãã¯ããäœæããããã·ãŒãžã£ã®å®è¡ã§ã¹ãããã远å ãããã·ãŒãžã£åã¯äžèšã®é¢æ°åïŒautofuncmanïŒãšããŠä¿åãã
- äžæŠä¿åããŠéãã
- ã³ãã³ãããã³ãããéã以äžã®ãããªã³ãã³ããå®è¡ããïŒ/cmdã¹ã€ãããå©çšããïŒ
1"C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE" "C:\Users\hiroyuki\Desktop\Database4.accdb" /cmd "鶯ãã³ãã³" - åŒæ°ã®ã¡ãã»ãŒãžã衚瀺ãããããã«ãªã
å³ïŒAutoexecãã¯ããšé£åããŠåãã
ãã¯ããVisual Basicã«å€æ
ãã¯ãã®æ©èœã¯ç°¡åã«ããŒã³ãŒãã§äœãããšèšã£ãŠãåºæ¥ãäºãéãããŠããŸããVBAã§ãããªãã°ãããšããããäºãå¯èœãšãªããããVBAã身ã«çããããšãã£ãå Žåã¯æåããVBAã§æ§ç¯ããã»ããèªç±åºŠãå šç¶éããŸããããããæ¢åã®ãã¯ãã§äœã£ãŠããŸã£ãŠããã®ã¯ãããäžåºŠã³ãŒããæžãããšãªãããã¿ã³ïŒã€ã§Visual Basicã®ã³ãŒãã«å€æãå¯èœã§ãã
- ãã¯ãã®ç·šéç»é¢ãéã
- å·Šäžã®ãªãã³ã«ããããã¯ããVisual Basicã«å€æããã¯ãªãã¯ãã
- ãšã©ãŒåŠçãã³ã¡ã³ããªã©ã®å¯åŠããã§ãã¯ããŠã倿ãã¿ã³ãã¯ãªãã¯
- æšæºã¢ãžã¥ãŒã«ãšããŠè¿œå ãããŠãäŸãã°ä»¥äžã®ãããªã³ãŒããçæããã
123456789101112131415161718192021222324Option Compare Database'------------------------------------------------------------' æ¡ä»¶å€å®''------------------------------------------------------------Function æ¡ä»¶å€å®()On Error GoTo æ¡ä»¶å€å®_ErrWith CodeContextObjectIf (.[ãã§ãã¯2] = True) ThenDoCmd.RunMacro "å€é£¯", , ""End IfEnd Withæ¡ä»¶å€å®_Exit:Exit Functionæ¡ä»¶å€å®_Err:MsgBox Error$Resume æ¡ä»¶å€å®_ExitEnd Function - æ¡ä»¶å€å®()ãšãã颿°ãæ¢åã®ãã¿ã³ã®ã¯ãªãã¯æã€ãã³ãã«æžãæãã
å³ïŒVisual Basicã«ã³ãŒã倿ãå¯èœ
é¢é£ãªã³ã¯
- ã¹ã¯ãªããã®äœæãšç·šé - FileMaker Pro
- ããŒã¿ããŒã¹ãéãããšãã«å®è¡ããããã¯ããäœæãã
- ããŒããŒã ã·ã§ãŒãã«ããã䜿çšã㊠Access ãã¯ããå®è¡ãã
- Access 2010 ã® AutoKeys ãã¯ãã®äœãæ¹
- Command 颿°
- Access 2013 ã³ãã³ãã©ã€ã³ ã¹ã€ããã䜿ã£ãŠåž³ç¥šãèªåå°å·ããæ¹æ³ïœèµ·ååŸã®ãã¯ãå®è¡
- Ms Accessã®èµ·åã¹ã€ãããšå€©æçãšããããããã®ãªãè£æ
- ã¢ã¯ã·ã§ã³ ã¯ãšãªã®ã¡ãã»ãŒãžãé衚瀺ã«ããã«ã¯
- Access ã®ã€ãã³ãé§ååããŒã¿ãã¯ããšUSysApplicationLogãšãã°ããŒã¿
- ãã¯ãã䜿çšããŠã«ã¹ã¿ã ã¡ãã¥ãŒããã³ã·ã§ãŒãã«ãã ã¡ãã¥ãŒãäœæãã