{"id":1983,"date":"2024-07-07T23:19:13","date_gmt":"2024-07-07T14:19:13","guid":{"rendered":"https:\/\/euc-access-excel-db.com\/tips\/?p=1983"},"modified":"2024-07-07T23:22:04","modified_gmt":"2024-07-07T14:22:04","slug":"access-import-sql-excel01","status":"publish","type":"post","link":"https:\/\/euc-access-excel-db.com\/tips\/ct07_se\/ct075010_ac2ktips\/access-import-sql-excel01","title":{"rendered":"\u25cf AccessVBA \uff5e Excel\u30b7\u30fc\u30c8\u3092SQL\u3067\u90e8\u5206\u30a4\u30f3\u30dd\u30fc\u30c8\u3059\u308b\u65b9\u6cd5\uff12\u3064\u3002\u65e2\u5b58\u30c6\u30fc\u30d6\u30eb\u3078\u3068\u3001\u65b0\u898f\u30c6\u30fc\u30d6\u30eb\u4f5c\u6210\u3057\u306a\u304c\u3089\u3002"},"content":{"rendered":"<p>\u25cf AccessVBA \uff5e Excel\u30b7\u30fc\u30c8\u3092SQL\u3067\u90e8\u5206\u30a4\u30f3\u30dd\u30fc\u30c8\u3059\u308b\u65b9\u6cd5\uff12\u3064\u3002\u65e2\u5b58\u30c6\u30fc\u30d6\u30eb\u3078\u3068\u3001\u65b0\u898f\u30c6\u30fc\u30d6\u30eb\u4f5c\u6210\u3057\u306a\u304c\u3089\u3002<\/p>\n<p><SPAN>\u3000\u3000<\/SPAN><br \/>\n<span style=\"color: #ff0000;\">\u203b\u307e\u3060\u66f8\u304d\u304b\u3051\u3067\u3059\u3002\u3059\u307f\u307e\u305b\u3093\u3002<\/span><br \/>\n<span style=\"color: #ff0000;\">\u203b\u9593\u9055\u3063\u3066\u305f\u3089\u3059\u307f\u307e\u305b\u3093\u3002<\/span><br \/>\n<span style=\"color: #ff0000;\">\u203b\u30e1\u30e2\u66f8\u304d\u306a\u306e\u3067\u3001\u81ea\u5206\u3067\u3082\u610f\u5473\u4e0d\u660e\u306a\u7b87\u6240\u3082\u591a\u3044\u3067\u3059\u3002\u3054\u3081\u3093\u306a\u3055\u3044\u3002<\/span><br \/>\n<SPAN>\u3000\u3000<\/SPAN><br \/>\n<SPAN>\u3000\u3000<\/SPAN><\/p>\n<p>\u2605 \u306f\u3058\u3081\u306b<\/p>\n<p>\uff12\u3064\u3042\u3063\u3066\u3001<br \/>\n\uff11\u3064\u3081\u306f\u3001\u65e2\u5b58\u306e\u30c6\u30fc\u30d6\u30eb\u306bSQL\u3067\u7d5e\u308a\u8fbc\u3093\u3060\u30ec\u30b3\u30fc\u30c9\u3092\u5438\u3044\u8fbc\u3080\u3082\u306e\u3067\u3001<br \/>\n\uff12\u3064\u3081\u306f\u3001\u65e2\u5b58\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u3044\u3063\u305f\u3093\u6d88\u3057\u3066\u3001\u540c\u540d\u306e\u65b0\u898f\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u308a\u3001<br \/>\n\u305d\u306e\u4e2d\u306b\u3001SQL\u3067\u7d5e\u308a\u8fbc\u3093\u3060\u30ec\u30b3\u30fc\u30c9\u3092\u5438\u3044\u8fbc\u307f\u307e\u3059\u3002<\/p>\n<p>\u4e00\u5fdc\u3001\u4e21\u65b9\u3068\u308210\u4e07\u4ef6\u306e40\u5217\u3001\u306e\u5185\u5bb9\u304b\u3089\u6570\u884c\u30fb\u6570\u5217\u306b\u7d5e\u3063\u3066\u51e6\u7406\u3057\u307e\u3057\u305f\u3002<br \/>\n\uff08PassMark\u306eCPU\u30b9\u30b3\u30a2\u304c6000\u304f\u3089\u3044\u306e\u30ce\u30fc\u30c8PC\u3067\u3084\u308a\u307e\u3057\u305f\uff09<\/p>\n<p>6\u79d2\u304f\u3089\u3044\u304b\u304b\u308a\u307e\u3057\u305f\u3002<br \/>\n20\u4e07\u4ef6\u3060\u306810\u79d2\u304b\u304b\u3089\u306a\u3044\u304f\u3089\u3044\u3067\u3057\u305f\u3002<\/p>\n<p><SPAN>\u3000\u3000<\/SPAN><br \/>\n\u2605\u65e2\u5b58\u306e\u30c6\u30fc\u30d6\u30eb\u306bSQL\u3067\u7d5e\u308a\u8fbc\u3093\u3060\u30ec\u30b3\u30fc\u30c9\u3092\u5438\u3044\u8fbc\u3080\u3082\u306e<\/p>\n<pre class=\"lang:default decode:true \">\r\n'\r\n'\r\n\r\n\r\nGPT\u3078\u306e\u8cea\u554f\u5185\u5bb9\r\nAccess\u306bExcel\u30b7\u30fc\u30c8\u3092\u3001SQL\u3092\u4f7f\u3063\u3066\u90e8\u5206\u30a4\u30f3\u30dd\u30fc\u30c8\u3059\u308bVBA\u30b3\u30fc\u30c9\u3092\u6559\u3048\u3066\r\n\r\n\r\nOption Compare Database\r\nOption Explicit\r\n\r\nSub ImportExcelToAccess()\r\n    Dim db As DAO.Database\r\n    Dim rs As DAO.Recordset\r\n    Dim strExcelPath As String\r\n    Dim strSQL As String\r\n\r\n    Dim s_WsName As String\r\n    Dim s_ConnectStr As String\r\n    \r\n    ' Excel\u30d5\u30a1\u30a4\u30eb\u306e\u30d1\u30b9\r\n'    strExcelPath = \"d:\\1\\10r10c.xlsx\"\r\n    strExcelPath = \"d:\\1\\10man40c.xlsx\"\r\n\r\n    '\u5438\u3044\u8fbc\u307f\u305f\u3044Excel\u30b7\u30fc\u30c8\u540d\r\n    s_WsName = \"SheetA\"\r\n    \r\n    ' Access\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306e\u53c2\u7167\u3092\u53d6\u5f97\r\n    Set db = CurrentDb\r\n\r\n\r\n    Let s_ConnectStr = \"[Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=\"\r\n    \r\n    ' Excel\u30b7\u30fc\u30c8\u304b\u3089\u30c7\u30fc\u30bf\u3092\u30a4\u30f3\u30dd\u30fc\u30c8\u3059\u308bSQL\u30af\u30a8\u30ea\u3092\u4f5c\u6210\r\n    ' \u300cExcelData\u300d\u3068\u3044\u3046\u65e2\u5b58\u30c6\u30fc\u30d6\u30eb\u300cf01\u300d\u3001\u300cf02\u300d\u3068\u3044\u3046\u5217\u306b\r\n    '  \u6307\u5b9a\u3057\u305fExcel\u30b7\u30fc\u30c8\u306e\u300cf01\u300d\u3001\u300cf02\u300d\u306e\u30ec\u30b3\u30fc\u30c9\u3092\u5438\u3044\u8fbc\u307f\u3002\r\n    '  \u3053\u306e\u30c6\u30b9\u30c8\u3067\u306f\u300cWHERE f01 = 3\u300d\u306e\u307f\u3092\u3002\r\n    strSQL = \"INSERT INTO ExcelData (f01, f02) \" & _\r\n             \"SELECT f01, f01 \" & _\r\n             \"FROM \" & s_ConnectStr & strExcelPath & \"].[\" & s_WsName & \"$] \" & _\r\n             \"WHERE f01 = 3;\"\r\n    \r\n    ' SQL\u30af\u30a8\u30ea\u3092\u5b9f\u884c\r\n    db.Execute strSQL, dbFailOnError\r\n    \r\n    ' \u30af\u30ea\u30fc\u30f3\u30a2\u30c3\u30d7\r\n    Set rs = Nothing\r\n    Set db = Nothing\r\n    \r\n    Debug.Print \"Data imported successfully!\"\r\n    \r\nEnd Sub\r\n\r\n\r\n'\r\n'\r\n<\/pre>\n<p><SPAN>\u3000\u3000<\/SPAN><\/p>\n<p><SPAN>\u3000\u3000<\/SPAN><br \/>\n\u2605\u65e2\u5b58\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u3044\u3063\u305f\u3093\u6d88\u3057\u3066\u3001\u540c\u540d\u306e\u65b0\u898f\u30c6\u30fc\u30d6\u30eb\u3092\u81ea\u52d5\u3067\u4f5c\u308a\u3001\u305d\u306e\u4e2d\u306b\u3001SQL\u3067\u7d5e\u308a\u8fbc\u3093\u3060\u30ec\u30b3\u30fc\u30c9\u3092\u5438\u3044\u8fbc\u307f<\/p>\n<pre class=\"lang:default decode:true \">\r\n'\r\n'\r\n\r\n\r\nGPT\u3078\u306e\u8cea\u554f\u5185\u5bb9\r\nAccess\u306bExcel\u30b7\u30fc\u30c8\u3092\u3001SQL\u3092\u4f7f\u3063\u3066\u90e8\u5206\u30a4\u30f3\u30dd\u30fc\u30c8\u3057\u3001\u304b\u3064\u3001\u305d\u308c\u3067\u30c6\u30fc\u30d6\u30eb\u4f5c\u6210\u307e\u3067\u3092\u3059\u308bVBA\u30b3\u30fc\u30c9\u3092\u6559\u3048\u3066\r\n\r\n\r\nOption Compare Database\r\nOption Explicit\r\n\r\n\r\nSub ImportExcelToAccessAndCreateTable()\r\n    Dim db As DAO.Database\r\n    Dim strExcelPath As String\r\n    Dim strSQL As String\r\n    Dim newTableName As String\r\n    \r\n    Dim s_WsName As String\r\n    Dim s_ConnectStr As String\r\n    \r\n    ' Excel\u30d5\u30a1\u30a4\u30eb\u306e\u30d1\u30b9\r\n'    strExcelPath = \"d:\\1\\10r10c.xlsx\"\r\n    strExcelPath = \"d:\\1\\10man40c.xlsx\"\r\n    \r\n    '\u5438\u3044\u8fbc\u307f\u305f\u3044Excel\u30b7\u30fc\u30c8\u540d\r\n    s_WsName = \"SheetA\"\r\n\r\n    ' \u65b0\u3057\u3044\u30c6\u30fc\u30d6\u30eb\u306e\u540d\u524d\r\n    newTableName = \"Exceldata01\"\r\n    \r\n    ' Access\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306e\u53c2\u7167\u3092\u53d6\u5f97\r\n    Set db = CurrentDb\r\n    \r\n    ' \u65e2\u5b58\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u524a\u9664\uff08\u65e2\u306b\u5b58\u5728\u3059\u308b\u5834\u5408\uff09\r\n    On Error Resume Next\r\n    db.Execute \"DROP TABLE \" & newTableName, dbFailOnError\r\n   \r\n    On Error GoTo 0\r\n    \r\n    \r\n    Let s_ConnectStr = \"[Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=\"\r\n    \r\n    ' \u300cExcel\u30b7\u30fc\u30c8\u304b\u3089\u5438\u3044\u8fbc\u3093\u3060\u30ec\u30b3\u30fc\u30c9\u3067\u65b0\u898f\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3059\u308b\u300d\r\n    '  \u3068\u3044\u3046SQL\u30af\u30a8\u30ea\u3092\u4f5c\u6210\u3002\r\n    '  \u6307\u5b9a\u3057\u305f\u30c6\u30fc\u30d6\u30eb\u540d\u3068\u30d5\u30a3\u30fc\u30eb\u30c9\u540d\u3067\u4f5c\u3063\u305f\u65b0\u898f\u30c6\u30fc\u30d6\u30eb\u306b\u3001\r\n    '  Excel\u30b7\u30fc\u30c8\u306eSQL\u3067\u7d5e\u308a\u8fbc\u3093\u3060\u90e8\u5206\u3092\u5438\u3044\u8fbc\u307f\u307e\u3059\u3002\r\n    '  \u3053\u306e\u30c6\u30b9\u30c8\u3067\u306f\u3001\u300cExceldata01\u300d\u3068\u3044\u3046\u65b0\u898f\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u308a\u3001\r\n    '  \u305d\u306e\u4e2d\u306b\u3001\u300cf01\u300d\u3001\u300cf02\u300d\u3001\u300cf03\u300d\u3001\u300cf04\u300d\u3001\u3068\u3044\u3046\u30d5\u30a3\u30fc\u30eb\u30c9\u3082\u4f5c\u308a\u3001\r\n    '  \u6307\u5b9a\u3057\u305fExcel\u30b7\u30fc\u30c8\u306e\r\n    ' \u300cf01\u300d\u3001\u300cf02\u300d\u3001\u300cf03\u300d\u3001\u300cf04\u300d\u3001\u306e\u30ec\u30b3\u30fc\u30c9\u3092\u5438\u3044\u8fbc\u307f\u3002\r\n    ' \u300cWHERE f01 BETWEEN 3 AND 8\u300d\u306e\u90e8\u5206\u306e\u307f\u3092\u3002\r\n    \r\n    strSQL = \"SELECT f01, f02, f03 , f04 \" & _\r\n             \"INTO \" & newTableName & \" \" & _\r\n             \"FROM \" & s_ConnectStr & strExcelPath & \"].[\" & s_WsName & \"$] \" & _\r\n             \"WHERE f01 BETWEEN 3 AND 8;\"\r\n\r\n    \r\n    ' SQL\u30af\u30a8\u30ea\u3092\u5b9f\u884c\u3057\u3066\u65b0\u3057\u3044\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\r\n    db.Execute strSQL, dbFailOnError\r\n'    db.Execute strSQL\r\n    \r\n    ' \u30af\u30ea\u30fc\u30f3\u30a2\u30c3\u30d7\r\n    Set db = Nothing\r\n    \r\n    Application.RefreshDatabaseWindow\r\n    '\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u30a6\u30a3\u30f3\u30c9\u30a6\u306b\u53cd\u6620\u8868\u793a\u3002\r\n    \r\n    Debug.Print \"Table created and data imported successfully!\"\r\n    \r\nEnd Sub\r\n\r\n\r\n\r\n'\r\n'\r\n<\/pre>\n<p><SPAN>\u3000\u3000<\/SPAN><\/p>\n<p><SPAN>\u3000\u3000<\/SPAN><br \/>\n<SPAN>\u3000\u3000<\/SPAN><\/p>\n","protected":false},"excerpt":{"rendered":"\u25cf AccessVBA \uff5e Excel\u30b7\u30fc\u30c8\u3092SQL\u3067\u90e8\u5206\u30a4\u30f3\u30dd\u30fc\u30c8\u3059\u308b\u65b9\u6cd5\uff12\u3064\u3002\u65e2\u5b58\u30c6\u30fc\u30d6\u30eb\u3078\u3068\u3001\u65b0\u898f\u30c6\u30fc\u30d6\u30eb\u4f5c\u6210\u3057\u306a\u304c\u3089\u3002 \u3000\u3000 \u203b\u307e\u3060\u66f8\u304d\u304b\u3051\u3067\u3059\u3002\u3059\u307f\u307e\u305b\u3093\u3002 \u203b\u9593\u9055\u3063\u3066\u305f\u3089\u3059\u307f\u307e\u305b\u3093\u3002 \u203b\u30e1\u30e2\u66f8\u304d\u306a\u306e\u3067\u3001\u81ea\u5206\u3067 ...","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[37],"_links":{"self":[{"href":"https:\/\/euc-access-excel-db.com\/tips\/wp-json\/wp\/v2\/posts\/1983"}],"collection":[{"href":"https:\/\/euc-access-excel-db.com\/tips\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/euc-access-excel-db.com\/tips\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/euc-access-excel-db.com\/tips\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/euc-access-excel-db.com\/tips\/wp-json\/wp\/v2\/comments?post=1983"}],"version-history":[{"count":0,"href":"https:\/\/euc-access-excel-db.com\/tips\/wp-json\/wp\/v2\/posts\/1983\/revisions"}],"wp:attachment":[{"href":"https:\/\/euc-access-excel-db.com\/tips\/wp-json\/wp\/v2\/media?parent=1983"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/euc-access-excel-db.com\/tips\/wp-json\/wp\/v2\/categories?post=1983"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}