AccessVBA テーブル作成、他

Sub test()

'テーブル作成 --------------------------------------------------------

Set t = CurrentDb.CreateTableDef("ttest")
Set f = t.CreateField("id", dbDate)

t.Fields.Append f
CurrentDb.TableDefs.Append t

'パススルークエリ作成 --------------------------------------------------------
'SQLServerへ接続

queryName = "test"

For i = 0 To CurrentDb.QueryDefs.Count - 1
  If CurrentDb.QueryDefs(i).Name = queryName Then CurrentDb.QueryDefs.Delete queryName
Next i

Set t = CurrentDb.CreateQueryDef(queryName)

With t
.Connect = "ODBC; Driver=SQL Server; Server=ESPRIMO\SQLEXPRESS; Database=my_database"
.ReturnsRecords = True
.SQL = "SELECT * FROM tbl;"
End With

Set t = Nothing

'更新系などのパススルークエリも作成できる --------------------------------------------------------

queryName = "test"

For i = 0 To CurrentDb.QueryDefs.Count - 1
  If CurrentDb.QueryDefs(i).Name = queryName Then CurrentDb.QueryDefs.Delete queryName
Next i

Set t = CurrentDb.CreateQueryDef(queryName)

With t
.Connect = "ODBC; Driver=SQL Server; Server=ESPRIMO\SQLEXPRESS;Database=my_database"
.ReturnsRecords = False
.SQL = "DELETE FROM tbl;"
End With

Set t = Nothing

'リンクテーブル(mdbへ接続) --------------------------------------------------------

Set t = CurrentDb.CreateTableDef("AccessLink")
t.Connect = ";DATABASE=" & Application.CurrentProject.Path & "/tmp.mdb;"
t.SourceTableName = "tbl"
CurrentDb.TableDefs.Append t

'テーブル削除 --------------------------------------------------------

tableName = "test_tbl"

For i = 0 To CurrentDb.TableDefs.Count - 1

  If CurrentDb.TableDefs(i).Name = tableName Then
    CurrentDb.TableDefs.Delete tableName
    Exit For
  End If

Next i

'クエリの実行 --------------------------------------------------------

'new_tableを作成しold_tableのデータをnew_tableへ入れる。
CurrentDb.Execute "select * into new_table from old_table"

'new_tableにold_tableのデータを入れる。
CurrentDb.Execute "insert into new_table select * from old_table"

End Sub

Sub test()

'テーブル作成 --------------------------------------------------------

Set t = CurrentDb.CreateTableDef("ttest")

Set f = t.CreateField("id", dbDate)

t.Fields.Append f

CurrentDb.TableDefs.Append t

'パススルークエリ作成 --------------------------------------------------------

'SQLServerへ接続

queryName = "test"

For i = 0 To CurrentDb.QueryDefs.Count - 1

If CurrentDb.QueryDefs(i).Name = queryName Then CurrentDb.QueryDefs.Delete queryName

Next i

Set t = CurrentDb.CreateQueryDef(queryName)

With t

.Connect = "ODBC; Driver=SQL Server; Server=ESPRIMO\SQLEXPRESS; Database=my_database"

.ReturnsRecords = True

.SQL = "SELECT * FROM tbl;"

End With

Set t = Nothing

'更新系などのパススルークエリも作成できる --------------------------------------------------------

queryName = "test"

For i = 0 To CurrentDb.QueryDefs.Count - 1

If CurrentDb.QueryDefs(i).Name = queryName Then CurrentDb.QueryDefs.Delete queryName

Next i

Set t = CurrentDb.CreateQueryDef(queryName)

With t

.Connect = "ODBC; Driver=SQL Server; Server=ESPRIMO\SQLEXPRESS;Database=my_database"

.ReturnsRecords = False

.SQL = "DELETE FROM tbl;"

End With

Set t = Nothing

'リンクテーブル(mdbへ接続) --------------------------------------------------------

Set t = CurrentDb.CreateTableDef("AccessLink")

t.Connect = ";DATABASE=" & Application.CurrentProject.Path & "/tmp.mdb;"

t.SourceTableName = "tbl"

CurrentDb.TableDefs.Append t

'テーブル削除 --------------------------------------------------------

tableName = "test_tbl"

For i = 0 To CurrentDb.TableDefs.Count - 1

If CurrentDb.TableDefs(i).Name = tableName Then

CurrentDb.TableDefs.Delete tableName

Exit For

End If

Next i

'クエリの実行 --------------------------------------------------------

'new_tableを作成しold_tableのデータをnew_tableへ入れる。

CurrentDb.Execute "select * into new_table from old_table"

'new_tableにold_tableのデータを入れる。

CurrentDb.Execute "insert into new_table select * from old_table"

End Sub