Option Explicit
Sub CreateSQLReview()
Dim conn As Object
Dim rs As Object
Dim strSQL As String
Dim strOutput As String
Dim i As Integer
' Access データベースへの接続
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\ThinkpadMark3\自学\filename変更_bat類\.accdb\サンプルData.accdb;"
' SQL クエリの作成
strSQL = "SELECT * FROM サブクエリ練習;"
' SQL クエリの実行
Set rs = conn.Execute(strSQL)
' ヘッダーの作成
strOutput = "Field Names: "
For i = 0 To rs.Fields.Count - 1
strOutput = strOutput & rs.Fields(i).Name & ", "
Next i
strOutput = Left(strOutput, Len(strOutput) - 2) & vbCrLf
' データの出力
Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
strOutput = strOutput & rs.Fields(i).Value & ", "
Next i
strOutput = Left(strOutput, Len(strOutput) - 2) & vbCrLf
rs.MoveNext
Loop
' Excel に出力
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets.Add
ws.Range("A1").Value = strOutput
' 掃除
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub
Sub CreateSQLStatement2()
Dim conn As Object
Dim rs As Object
Dim strSQL As String
Dim i As Integer
Dim ws As Worksheet
Dim nextRow As Long
' 新しいシートの作成
Set ws = ThisWorkbook.Sheets.Add
' Access データベースへの接続
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\ThinkpadMark3\自学\filename変更_bat類\.accdb\サンプルData.accdb;"
' クエリの名前を指定
strSQL = "SELECT * FROM サブクエリ練習"
' クエリの実行
Set rs = conn.Execute(strSQL)
' ヘッダーの取得
For i = 1 To rs.Fields.Count
ws.Cells(1, i).Value = rs.Fields(i - 1).Name
Next i
' データの取得
nextRow = 2
Do While Not rs.EOF
For i = 1 To rs.Fields.Count
ws.Cells(nextRow, i).Value = rs.Fields(i - 1).Value
Next i
nextRow = nextRow + 1
rs.MoveNext
Loop
' 掃除
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub
