{"id":6024,"date":"2017-09-25T06:42:58","date_gmt":"2017-09-24T21:42:58","guid":{"rendered":"http:\/\/excelwork.info\/excel\/?p=6024"},"modified":"2017-09-25T06:47:03","modified_gmt":"2017-09-24T21:47:03","slug":"databasesqlserver","status":"publish","type":"post","link":"https:\/\/excelwork.info\/excel\/databasesqlserver\/","title":{"rendered":"\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\uff08SQL Server\uff09\u306b\u63a5\u7d9a\u3059\u308b\uff08ADO\uff09"},"content":{"rendered":"<h2>\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\uff08SQL Server\uff09\u306b\u63a5\u7d9a<\/h2>\n<div class=\"m30-l\">\n<p><strong>ODBC<\/strong> \u307e\u305f\u306f <strong>OLE DB<\/strong> \u3067\u63a5\u7d9a\u3057\u307e\u3059\u3002<strong>ODBC<\/strong> \u63a5\u7d9a\u3059\u308b\u5834\u5408\u306f\u3001ODBC\u30c7\u30fc\u30bf\u30bd\u30fc\u30b9\u3092\u4f5c\u6210\u3059\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n<h3>\u63a5\u7d9a\u6587\u5b57\u5217<\/h3>\n<br \/>\n<div class=\"kkbox\"><strong>ConnectionString\uff08ADO\uff09\u306b\u8a2d\u5b9a\u3059\u308b\u6587\u5b57\u5217<\/strong><\/p>\n<p>    <span class=\"red\"><strong>\uff1cODBC \u63a5\u7d9a\uff1e<\/strong><\/span><br \/>\n    <b>DNS<\/b> = <em>DataSourceName<\/em>;<br \/>\n    [<b> UID<\/b> = <em>UserName<\/em>;]<br \/>\n    [<b> PWD<\/b> = <em>Password<\/em>;]<\/p>\n<p>    <span class=\"red\"><strong>\uff1cOLE DB \u63a5\u7d9a\uff1e<\/strong><\/span><br \/>\n    <b>Provider<\/b> = <b>SQLOLEDB<\/b>;<br \/>\n    <b> Data Source<\/b> = <em>ServerName<\/em>;<br \/>\n    [<em>Ninsyo<\/em>;]<br \/>\n    [<b> User ID<\/b> = <em>UserName<\/em>;]<br \/>\n    [<b> Password<\/b> = <em>Password<\/em>;]<\/p><\/div>\n<br \/>\n<h4>\u5f15\u6570\u30fb\u623b\u308a\u5024<\/h4>\n<div class=\"m30-l\">\n<ul style=\"margin-top:0;margin-bottom:0;padding-top:0;padding-bottom:0;\">\n<li>DataSourceName&nbsp;&nbsp;\u30fb\u30fb\u30fb&nbsp;&nbsp;ODBC \u30c7\u30fc\u30bf\u30bd\u30fc\u30b9\u540d\u3092\u6307\u5b9a\u3057\u307e\u3059\u3002<\/li>\n<li>UserName&nbsp;&nbsp;\u30fb\u30fb\u30fb&nbsp;&nbsp;\u30e6\u30fc\u30b6\u30fc\u540d\u3092\u6307\u5b9a\u3057\u307e\u3059\uff08Windows\u8a8d\u8a3c\u306e\u5834\u5408\u306f\u4e0d\u8981\uff09\u3002<\/li>\n<li>Password&nbsp;&nbsp;\u30fb\u30fb\u30fb&nbsp;&nbsp;\u30d1\u30b9\u30ef\u30fc\u30c9\u3092\u6307\u5b9a\u3057\u307e\u3059\uff08Windows\u8a8d\u8a3c\u306e\u5834\u5408\u306f\u4e0d\u8981\uff09\u3002<\/li>\n<li>ServerName&nbsp;&nbsp;\u30fb\u30fb\u30fb&nbsp;&nbsp;\u30b5\u30fc\u30d0\u30fc\u306e\u30db\u30b9\u30c8\u540d\u3001IP\u30a2\u30c9\u30ec\u30b9\u3001\u30cd\u30c3\u30c8\u30ef\u30fc\u30af\u4e0a\u306ePC\u540d\u7b49\u3092\u6307\u5b9a\u3057\u307e\u3059\u3002\u307e\u305f\u30dd\u30fc\u30c8\u3082\u6307\u5b9a\u3067\u304d\u307e\u3059\u3002<br \/>\n\t\uff08\u4f8b\uff09localhost\u3001127.0.0.1,1433\u3001DesktopPC\\SQLEXPRESS<br \/>\n\t\u203bDesktopPC \u306f\u30cd\u30c3\u30c8\u30ef\u30fc\u30af\u4e0a\u306b\u8868\u793a\u3055\u308c\u308bPC\u540d\u3067\u3001SQLEXPRESS \u306f\u30a4\u30f3\u30b9\u30bf\u30f3\u30b9\u540d\u3067\u3059\u3002\u30dd\u30fc\u30c8\u3092\u6307\u5b9a\u3059\u308b\u5834\u5408\u306f\u3001\u300c,\u300d\u306e\u3042\u3068\u306b\u6307\u5b9a\u3057\u307e\u3059\u3002<\/li>\n<li>Ninsyo&nbsp;&nbsp;\u30fb\u30fb\u30fb&nbsp;&nbsp;SQL Server\u8a8d\u8a3c\u306e\u5834\u5408\u306f\u4e0d\u8981\u3067\u3059\u3002Windows\u8a8d\u8a3c\u306e\u5834\u5408\u306f\u6b21\u306e\u3044\u305a\u308c\u304b\u3092\u6307\u5b9a\u3057\u307e\u3059\u3002Trusted_connection=yes\u3000\u307e\u305f\u306f\u3001Integrated Security=SSPI<\/li>\n<\/ul>\n<br \/><\/div>\n<\/div>\n<br \/><br \/>\n<h2>\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\uff08SQL Server\uff09\u63a5\u7d9a<\/h2>\n<div class=\"m30-l\">\n<h3 id=\"vba_sample\">\u30b5\u30f3\u30d7\u30ebVBA\u30bd\u30fc\u30b9<\/h3>\n<pre class=\"lang:vb mark:0 decode:true \" title=\"SQLServer\u3078\u63a5\u7d9a\u3059\u308b\" data-url=\"http:\/\/example.co.jp\" >\r\n    \r\nSub Sample_ADO_SQLServer()\r\n\r\n\r\n    Dim constr As String\r\n\r\n    'OLE DB\r\n    Const MYPROVIDERE = \"Provider=SQLOLEDB;\"\r\n    Const MYSERVER = \"Data Source=localhost,1433;\"                '\u30b5\u30fc\u30d0\u30fc,\u30dd\u30fc\u30c8\r\n    Const MYNINSYO = \"Trusted_connection=yes;\"                    'Windows\u8a8d\u8a3c\u306e\u5834\u5408\uff08\u300c\"Integrated Security=SSPI;\"\u300d\u3067\u3082\u3088\u3044\uff09\r\n    Const MYDATABASE = \"Initial Catalog=TestDB01;\"                '\u63a5\u7d9a\u3059\u308b\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u540d\r\n    constr = MYPROVIDERE & MYSERVER & MYNINSYO & MYDATABASE       'Windows\u8a8d\u8a3c\u306e\u5834\u5408\r\n    'Const USER = \"User ID=sqluser;\"                               'SQL Server\u8a8d\u8a3c\u306e\u5834\u5408\u306e\u307f\u6307\u5b9a\r\n    'Const PSWD = \"Password=psword;\"                               'SQL Server\u8a8d\u8a3c\u306e\u5834\u5408\u306e\u307f\u6307\u5b9a\r\n    'constr = MYPROVIDERE & MYSERVER & MYDATABASE & USER & PSWD    'SQL Server\u8a8d\u8a3c\u306e\u5834\u5408\r\n    \r\n    'ODBC\r\n    'Const DSN = \"DSN=SQLServer_TESTDB01;\"        'ODBC\u30e6\u30fc\u30b6\u30fc\u30c7\u30fc\u30bf\u30bd\u30fc\u30b9\u540d\r\n    'constr = DSN                                 'Windows\u8a8d\u8a3c\u306e\u5834\u5408\r\n    'Const USER = \"UID=sqluser;\"                  '\u30e6\u30fc\u30b6\u30fc\u540d\uff08SQL Server\u8a8d\u8a3c\u3092\u4f7f\u7528\u3059\u308b\u5834\u5408\u306e\u307f\u6307\u5b9a\uff09\r\n    'Const PSWD = \"PWD=psword;\"                   '\u30d1\u30b9\u30ef\u30fc\u30c9\uff08SQL Server\u8a8d\u8a3c\u3092\u4f7f\u7528\u3059\u308b\u5834\u5408\u306e\u307f\u6307\u5b9a\uff09\r\n    'constr = DSN & USER & PSWD                   'SQL Server\u8a8d\u8a3c\u306e\u5834\u5408\r\n    \r\n    \r\n    Dim cn As New ADODB.Connection\r\n    Dim Rs As New ADODB.Recordset\r\n    Dim strSQL As String\r\n    Dim i As Long\r\n    Dim j As Long\r\n    \r\n    cn.ConnectionString = constr\r\n    cn.Open\r\n    \r\n    strSQL = \"Select * from T_SYAIN;\"\r\n    Rs.Source = strSQL\r\n    Rs.ActiveConnection = cn\r\n    Rs.Open\r\n    \r\n    With Worksheets(\"sqlserver\")\r\n        \r\n        .Cells.Clear\r\n        i = 1\r\n        Do Until Rs.EOF\r\n            For j = 0 To Rs.Fields.Count - 1\r\n                If i = 1 Then .Cells(i, j + 1) = Rs.Fields(j).Name\r\n                .Cells(i + 1, j + 1) = Rs(j).Value\r\n            Next j\r\n            Rs.MoveNext\r\n            i = i + 1\r\n        Loop\r\n    \r\n    End With\r\n    \r\n    Rs.Close\r\n    Set Rs = Nothing\r\n    \r\n    cn.Close\r\n    Set cn = Nothing\r\n    \r\nEnd Sub\r\n    \r\n  <\/pre>\n<br \/>\n<h3>\u5b9f\u884c\u7d50\u679c<\/h3>\n<p><a href=\"https:\/\/i0.wp.com\/excelwork.info\/excel\/wp\/wp-content\/uploads\/2017\/07\/databasesqlserver_01.jpg?ssl=1\" rel=\"lightbox[6024]\"><img loading=\"lazy\" src=\"https:\/\/i0.wp.com\/excelwork.info\/excel\/wp\/wp-content\/uploads\/2017\/07\/databasesqlserver_01.jpg?resize=473%2C334&#038;ssl=1\" alt=\"\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\uff08SQLServer\uff09\u63a5\u7d9a\" width=\"473\" height=\"334\" class=\"alignnone size-full wp-image-6028\" srcset=\"https:\/\/i0.wp.com\/excelwork.info\/excel\/wp\/wp-content\/uploads\/2017\/07\/databasesqlserver_01.jpg?w=473&amp;ssl=1 473w, https:\/\/i0.wp.com\/excelwork.info\/excel\/wp\/wp-content\/uploads\/2017\/07\/databasesqlserver_01.jpg?resize=400%2C282&amp;ssl=1 400w\" sizes=\"(max-width: 473px) 100vw, 473px\" data-recalc-dims=\"1\" \/><\/a><\/p>\n<\/div>\n<br \/><br \/>\n","protected":false},"excerpt":{"rendered":"<p>\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\uff08SQL Server\uff09\u306b\u63a5\u7d9a ODBC \u307e\u305f\u306f OLE DB \u3067\u63a5 &#8230; <\/p>\n","protected":false},"author":1,"featured_media":6028,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"amp_status":"","spay_email":"","jetpack_publicize_message":""},"categories":[948],"tags":[950,954,958,957,952,951,962,814,955,949],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/excelwork.info\/excel\/wp\/wp-content\/uploads\/2017\/07\/databasesqlserver_01.jpg?fit=473%2C334&ssl=1","jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p4Ye9i-1za","_links":{"self":[{"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/posts\/6024"}],"collection":[{"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/comments?post=6024"}],"version-history":[{"count":10,"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/posts\/6024\/revisions"}],"predecessor-version":[{"id":6436,"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/posts\/6024\/revisions\/6436"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/media\/6028"}],"wp:attachment":[{"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/media?parent=6024"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/categories?post=6024"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/tags?post=6024"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}