{"id":982,"date":"2017-02-22T19:31:40","date_gmt":"2017-02-22T10:31:40","guid":{"rendered":"http:\/\/okamurax.com\/?p=982"},"modified":"2020-05-10T16:42:31","modified_gmt":"2020-05-10T07:42:31","slug":"vba-sql%e5%ae%9f%e9%a8%93%e7%94%a8%ef%bc%88sql-server%ef%bc%89","status":"publish","type":"post","link":"https:\/\/appbay.org\/?p=982","title":{"rendered":"VBA SQL\u5b9f\u9a13\u7528\u2461(SQL Server)"},"content":{"rendered":"

\u4ee5\u524d\u3001Access\u306b\u30c6\u30fc\u30d6\u30eb\u3092\u624b\u8efd\u4f5c\u6210\u3059\u308b\u30a8\u30af\u30bb\u30ebVBA\u304c\u3042\u3063\u305f\u306e\u3067\u3001\u4eca\u56de\u306fSQL Server\u7248\u3002\u30af\u30a8\u30ea\u306e\u8907\u6570\u884c\u5bfe\u5fdc\u3068\u3001\u30b7\u30fc\u30c8\u540d\u3067\u30c6\u30fc\u30d6\u30eb\u4f5c\u6210\u3078\u5c11\u3057\u4fee\u6b63\u3057\u3066\u3044\u308b\u3002<\/p>\n

Sub auto_open()\r\n\r\nApplication.CommandBars(\"cell\").Reset\r\n\r\nWith Application.CommandBars(\"cell\").Controls.Add\r\n.OnAction = \"t\"\r\n.Caption = \"\u30c6\u30fc\u30d6\u30eb\u4f5c\u6210\u30fb\u767b\u9332\"\r\nEnd With\r\n\r\nWith Application.CommandBars(\"cell\").Controls.Add\r\n.OnAction = \"tt\"\r\n.Caption = \"SQL\u5b9f\u884c\"\r\nEnd With\r\n\r\nEnd Sub\r\n\r\nSub auto_close()\r\n\r\nOn Error Resume Next\r\n\r\nFor i = 1 To 2\r\n  Application.CommandBars(\"cell\").Controls(\"\u30c6\u30fc\u30d6\u30eb\u4f5c\u6210\u30fb\u767b\u9332\").Delete\r\n  Application.CommandBars(\"cell\").Controls(\"SQL\u5b9f\u884c\").Delete\r\nNext i\r\n\r\nEnd Sub\r\n\r\nSub t()\r\n\r\nSet cn = CreateObject(\"adodb.connection\")\r\ncn.Open \"Provider=SQLOLEDB;Data Source=ESPRIMO\\SQLEXPRESS;Initial Catalog=my_database;Integrated Security=SSPI;\"\r\n\r\ntbl = ActiveSheet.Name\r\n\r\n'\u30c6\u30fc\u30d6\u30eb\u524a\u9664\r\n\r\nOn Error Resume Next\r\ncn.Execute \"drop table \" & tbl\r\n\r\n'\u30c6\u30fc\u30d6\u30eb\u4f5c\u6210\r\n\r\nOn Error GoTo 0\r\n\r\nfld = \"\"\r\n\r\nFor c = Selection(1).Column To Selection(Selection.Count).Column\r\n\r\n  Select Case Right(ActiveSheet.Cells(Selection(1).Row, c).Value, 1)\r\n    Case \"s\"\r\n    tmp = \"varchar(255)\"\r\n    Case \"i\"\r\n    tmp = \"bigint\"\r\n    Case \"m\"\r\n    tmp = \"money\"\r\n    Case \"d\"\r\n    tmp = \"date\"\r\n  End Select\r\n  \r\n  fld = fld & ActiveSheet.Cells(Selection(1).Row, c).Value & \" \" & tmp & \",\"\r\n\r\nNext c\r\n\r\nfld = Left(fld, Len(fld) - 1)\r\n\r\nq = \"create table \" & tbl & \" (id integer identity(1,1) primary key,\" & fld & \")\": Debug.Print q\r\ncn.Execute q\r\n\r\n'\u30c7\u30fc\u30bf\u767b\u9332\r\n\r\nfld = \"\"\r\n\r\nFor c = Selection(1).Column To Selection(Selection.Count).Column\r\n  fld = fld & ActiveSheet.Cells(Selection(1).Row, c).Value & \",\"\r\nNext c\r\n\r\nfld = Left(fld, Len(fld) - 1)\r\n\r\nFor r = (Selection(1).Row) + 1 To Selection(Selection.Count).Row\r\n  For cc = Selection(1).Column To Selection(Selection.Count).Column\r\n    rec = rec & \"'\" & ActiveSheet.Cells(r, cc).Value & \"'\" & \",\"\r\n  Next cc\r\n\r\n  rec = Left(rec, Len(rec) - 1)\r\n  \r\n  q = \"insert into \" & tbl & \" (\" & fld & \") values (\" & rec & \")\": Debug.Print q\r\n  cn.Execute q\r\n\r\n  rec = \"\"\r\nNext r\r\n\r\nIf cn.State = 1 Then cn.Close\r\nSet cn = Nothing\r\n\r\nMsgBox \"done\"\r\n\r\nEnd Sub\r\n\r\nSub tt()\r\n\r\nSet cn = CreateObject(\"adodb.connection\")\r\nSet rn = CreateObject(\"adodb.recordset\")\r\n\r\ncn.Open \"Provider=SQLOLEDB;Data Source=ESPRIMO\\SQLEXPRESS;Initial Catalog=my_database;Integrated Security=SSPI;\"\r\n\r\nIf Selection.Rows.Count = 1 Then\r\n  rn.Open ActiveCell.Value, cn\r\nElse\r\n  For r = 1 To Selection.Rows.Count\r\n    q = q & \" \" & Selection(r)\r\n  Next r\r\n  rn.Open q, cn: Debug.Print q\r\nEnd If\r\n\r\n'\u30d5\u30a3\u30fc\u30eb\u30c9\u4f5c\u6210\r\n\r\ni = 0\r\n\r\nFor c = Selection(1).Column To (Selection(1).Column + rn.Fields.Count) - 1\r\n  Cells(Selection(Selection.Count).Row + 1, c).Value = rn.Fields(i).Name\r\n  i = i + 1\r\nNext c\r\n\r\n'\u30c7\u30fc\u30bf\u8aad\u8fbc\r\n\r\nCells(Selection(Selection.Count).Row + 2, Selection(1).Column).CopyFromRecordset rn\r\n\r\nIf rn.State = 1 Then rn.Close\r\nSet rn = Nothing\r\n\r\nIf cn.State = 1 Then cn.Close\r\nSet cn = Nothing\r\n\r\nEnd Sub\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
\u4ee5\u524d\u3001Access\u306b\u30c6\u30fc\u30d6\u30eb\u3092\u624b\u8efd\u4f5c\u6210\u3059\u308b\u30a8\u30af\u30bb\u30ebVBA\u304c\u3042\u3063\u305f\u306e\u3067\u3001\u4eca\u56de\u306fSQL Server\u7248\u3002\u30af\u30a8\u30ea\u306e\u8907\u6570\u884c\u5bfe\u5fdc\u3068\u3001\u30b7\u30fc\u30c8\u540d\u3067\u30c6\u30fc\u30d6\u30eb\u4f5c\u6210\u3078\u5c11\u3057\u4fee\u6b63\u3057\u3066\u3044\u308b\u3002 Sub auto_open() Application … <\/p>\n
“VBA SQL\u5b9f\u9a13\u7528\u2461(SQL Server)” \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-982","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\/982","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=982"}],"version-history":[{"count":5,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/982\/revisions"}],"predecessor-version":[{"id":3466,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/982\/revisions\/3466"}],"wp:attachment":[{"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=982"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=982"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=982"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}