AccessVBA入門②DAO(Data Access Object)

Sub SQL実行()
  Dim DB As DAO.Database
  Set DB = CurrentDb
  DB.Execute "SELECT * FROM T名簿"
  Set DB = Nothing
End Sub

Sub テーブル作成()
  Dim DB As DAO.Database
  Dim TD As DAO.TableDef
  Set DB = CurrentDb
  Set TD = DB.CreateTableDef("T名簿")
  TD.Fields.Append TD.CreateField("番号",dbText,5)
  TD.Fields.Append TD.CreateField("氏名",dbText,20)
  TD.Fields.Append TD.CreateField("ポイント",dbLong)
  DB.TableDefs.Append TD
  Set TD = Nothing: Set DB = Nothing
End Sub

Sub クエリ作成()
  Dim DB As DAO.Database
  Dim QD As DAO.QueryDef
  Dim SQL As String
  Set DB = CurrentDb
  SQL = "SELECT * FROM T名簿　WHERE 番号 > 100;"
  Set QD = DB.CreateQueryDef("100番超", SQL)
  Set QD = Nothing: Set DB = Nothing
End Sub

Sub クエリ作成2()
  Dim DB As DAO.Database
  Dim QD As DAO.QueryDef
  Dim SQL As String
  Set DB = CurrentDb
  SQL = "INSERT INTO T名簿 VALUES('200','B',2000);"
  Set QD = DB.CreateQueryDef("", SQL)
  QD.Execute
  Set QD = Nothing: Set DB = Nothing
End Sub

Sub クエリ作成3()
  Dim DB As DAO.Database
  Dim QD As DAO.QueryDef
  Dim RS As DAO.Recordset
  Dim SQL As String
  Set DB = CurrentDb
  SQL = "SELECT * FROM T名簿　WHERE 番号 > 100;"
  Set QD = DB.CreateQueryDef("", SQL)
  Set RS = QD.OpenRecordset()
  Do Until RS.EOF
    Debug.Print RS("番号"),RS("氏名"),RS("ポイント")
    RS.MoveNext
  Loop
  Set QD = Nothing: Set RS = Nothing: Set DB = Nothing
End Sub