{"id":1046,"date":"2017-03-06T22:14:14","date_gmt":"2017-03-06T13:14:14","guid":{"rendered":"http:\/\/okamurax.com\/?p=1046"},"modified":"2017-03-06T22:14:57","modified_gmt":"2017-03-06T13:14:57","slug":"access-vba-%e8%89%b2%e3%80%85","status":"publish","type":"post","link":"https:\/\/appbay.org\/?p=1046","title":{"rendered":"AccessVBA \u30c6\u30fc\u30d6\u30eb\u4f5c\u6210\u3001\u4ed6"},"content":{"rendered":"<pre class=\"lang:vb decode:true \">Sub test()\r\n\r\n'\u30c6\u30fc\u30d6\u30eb\u4f5c\u6210 --------------------------------------------------------\r\n\r\nSet t = CurrentDb.CreateTableDef(\"ttest\")\r\nSet f = t.CreateField(\"id\", dbDate)\r\n\r\nt.Fields.Append f\r\nCurrentDb.TableDefs.Append t\r\n\r\n'\u30d1\u30b9\u30b9\u30eb\u30fc\u30af\u30a8\u30ea\u4f5c\u6210 --------------------------------------------------------\r\n'SQLServer\u3078\u63a5\u7d9a\r\n\r\nqueryName = \"test\"\r\n\r\nFor i = 0 To CurrentDb.QueryDefs.Count - 1\r\n  If CurrentDb.QueryDefs(i).Name = queryName Then CurrentDb.QueryDefs.Delete queryName\r\nNext i\r\n\r\nSet t = CurrentDb.CreateQueryDef(queryName)\r\n\r\nWith t\r\n.Connect = \"ODBC; Driver=SQL Server; Server=ESPRIMO\\SQLEXPRESS; Database=my_database\"\r\n.ReturnsRecords = True\r\n.SQL = \"SELECT * FROM tbl;\"\r\nEnd With\r\n\r\nSet t = Nothing\r\n\r\n'\u66f4\u65b0\u7cfb\u306a\u3069\u306e\u30d1\u30b9\u30b9\u30eb\u30fc\u30af\u30a8\u30ea\u3082\u4f5c\u6210\u3067\u304d\u308b --------------------------------------------------------\r\n\r\nqueryName = \"test\"\r\n\r\nFor i = 0 To CurrentDb.QueryDefs.Count - 1\r\n  If CurrentDb.QueryDefs(i).Name = queryName Then CurrentDb.QueryDefs.Delete queryName\r\nNext i\r\n\r\nSet t = CurrentDb.CreateQueryDef(queryName)\r\n\r\nWith t\r\n.Connect = \"ODBC; Driver=SQL Server; Server=ESPRIMO\\SQLEXPRESS;Database=my_database\"\r\n.ReturnsRecords = False\r\n.SQL = \"DELETE FROM tbl;\"\r\nEnd With\r\n\r\nSet t = Nothing\r\n\r\n'\u30ea\u30f3\u30af\u30c6\u30fc\u30d6\u30eb(mdb\u3078\u63a5\u7d9a) --------------------------------------------------------\r\n\r\nSet t = CurrentDb.CreateTableDef(\"AccessLink\")\r\nt.Connect = \";DATABASE=\" &amp; Application.CurrentProject.Path &amp; \"\/tmp.mdb;\"\r\nt.SourceTableName = \"tbl\"\r\nCurrentDb.TableDefs.Append t\r\n\r\n'\u30c6\u30fc\u30d6\u30eb\u524a\u9664 --------------------------------------------------------\r\n\r\ntableName = \"test_tbl\"\r\n\r\nFor i = 0 To CurrentDb.TableDefs.Count - 1\r\n\r\n  If CurrentDb.TableDefs(i).Name = tableName Then\r\n    CurrentDb.TableDefs.Delete tableName\r\n    Exit For\r\n  End If\r\n\r\nNext i\r\n\r\n'\u30af\u30a8\u30ea\u306e\u5b9f\u884c --------------------------------------------------------\r\n\r\n'new_table\u3092\u4f5c\u6210\u3057old_table\u306e\u30c7\u30fc\u30bf\u3092new_table\u3078\u5165\u308c\u308b\u3002\r\nCurrentDb.Execute \"select * into new_table from old_table\"\r\n\r\n'new_table\u306bold_table\u306e\u30c7\u30fc\u30bf\u3092\u5165\u308c\u308b\u3002\r\nCurrentDb.Execute \"insert into new_table select * from old_table\"\r\n\r\nEnd Sub<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Sub test() &#8216;\u30c6\u30fc\u30d6\u30eb\u4f5c\u6210 &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211; Set t = CurrentDb.CreateTableDef(&#8221; &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/appbay.org\/?p=1046\" class=\"more-link\"><span class=\"screen-reader-text\">&#8220;AccessVBA \u30c6\u30fc\u30d6\u30eb\u4f5c\u6210\u3001\u4ed6&#8221; \u306e<\/span>\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[5],"class_list":["post-1046","post","type-post","status-publish","format-standard","hentry","category-1","tag-vba"],"_links":{"self":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/1046","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1046"}],"version-history":[{"count":3,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/1046\/revisions"}],"predecessor-version":[{"id":5105,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/1046\/revisions\/5105"}],"wp:attachment":[{"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1046"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1046"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1046"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}