Excel VBA SQL ADOフィールド名変更

質問

「年度」、「月」、「ID」、「名前」の4つフィールドからなる
「テスト」というテーブルの「年度」を「年」に書き換えたいのですが、
「年度」フィールドの値を年度から年には書き換えることができたのですが、
フィールド名の「年度」を「年」に書き換えるには、
ADOで、EXCELでは、どのように記述すればよいのでしょうか。

以下は、ACCESSのVBAの例を参考にして記述したのですが
Excel2010のVBAでは「CurrentProject」のところでエラーに
なってしまいます。

strSQL =　・・・のような形式で記述して
adoCON.Execute (strSQL)でフィールド名を
変更することはできないのでしょうか。

よろしくお願いします。

--- Excel VBA -----------------------------------
Dim adoCON As New ADODB.Connection
Dim strSQL As String
Dim odbdDB As Variant

odbdDB = "C:\TEST\Database.accdb"
adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & odbdDB & ""
adoCON.Open

'「年度」フィールドの値の年度を年に変換する
strSQL = "UPDATE テスト SET 年度 = 年度+1 WHERE VAL(月) < " & 4 & ";"
adoCON.Execute (strSQL)

adoCON.Close
Set adoCON = Nothing

'フィールド名の年度を年に変更する
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection 'エラー発生
cat.Tables("テスト").Columns("年度").Name = "年"
Set cat = Nothing
-----------------------------------------------

回答No.2

real beatin（@realbeatin） · ベストアンサー

こんにちは。

///

> 「テスト」というテーブルの「年度」を「年」に書き換えたいのですが、

> ADOで、EXCELでは、どのように記述すればよいのでしょうか。

ご提示の記述に手を加えるなら、以下のような感じでしょうか。

' ' /// ReW9074780()
Dim adoCON As New ADODB.Connection
Dim strSQL As String
Dim strConn As String　'　★
Dim odbdDB As Variant

　　odbdDB = "C:\TEST\Database.accdb"
　　strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
　　　　　　"Data Source=" & odbdDB　'　★
　　adoCON.ConnectionString = strConn　'　★
　　adoCON.Open

''「年度」フィールドの値の年度を年に変換する
　　strSQL = "UPDATE テスト SET 年度 = 年度+1 WHERE VAL(月) < " & 4 & ";"
　　adoCON.Execute (strSQL)

　　adoCON.Close
　　Set adoCON = Nothing

'フィールド名の年度を年に変更する
Dim cat As ADOX.Catalog
　　Set cat = New ADOX.Catalog
　　cat.ActiveConnection = strConn　'　★
　　cat.Tables("テスト").Columns("年度").Name = "年"
　　Set cat = Nothing
' ' ///

一応、一通りのサンプルを作成した上で、動作確認はしてあります。

> 以下は、ACCESSのVBAの例を参考にして記述したのですが
> Excel2010のVBAでは「CurrentProject」のところでエラーに
> なってしまいます。

どっちみち、文字列指定なのですし、
扱うコネクションもテーブルも共通なのですから、
"コネクションを丸ごと文字列変数に格納して使い回す"
という例でお応えしました（お求めに対する私の理解が至っていないかも知れませんが）。

> strSQL =　・・・のような形式で記述して
> adoCON.Execute (strSQL)でフィールド名を
> 変更することはできないのでしょうか。

フィールドの管理はADOX.Catalogでやるしかないです。
同じADOでも、ADODBではなくて、ADOXの仕事になります。

///

以上です。

お礼

教えていただきました方法でフィールド名の変更ができました。
変更箇所も★が示されていてわかり易かったです。
ありがとうございました。

回答No.3

piroin654

Ｎｏ1です。
ついでですから書いておきます。

ADOだけでなく、DAOでもExcelから接続し、フィールド名を
変更できます。たとえば、

Sub test2()
Dim ws As Workspace
Dim db As DAO.Database
Dim tdf As TableDef

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(ThisWorkbook.Path & "\sample.mdb")
Set tdf = db.TableDefs("テスト")

tdf.Fields("年度").Name = "年"
tdf.Fields.Refresh

Set tdf = Nothing
db.Close: Set db = Nothing
End Sub

のように。この場合は、接続にDBEngine.Workspacesを使用します。

SQL文での変更は、一旦フィールドを削除し、改めてフィールドを
作成するようになります。したがってデータが失われることに
なり、不都合な結果になります。
「Access SQL を使用してテーブルの設計を変更する」
https://msdn.microsoft.com/ja-jp/library/office/ff822825.aspx

なお、SQLのUPDATE文はデータに対する更新になるので、テーブル定義の
変更は上記のURLをよく読んでください。

お礼

ADOだけでなくDAOについても教えていただきまして
ありがとうございました。

回答No.1

piroin654

CurrentProject.Connection
は、Accessで可能な書き方です。

以下は、Excelのファイルがあるディレクトリのsample.acodbという
Accessファイルに接続し「テスト」というテーブルの「年度」というフィールド名を
「年」に変更します。ファイル名は実際に合わせて変更してください。

Sub test()
Dim cat As New ADOX.Catalog
Dim strPath As String

strPath = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\sample.accdb"
cat.ActiveConnection = strPath
cat.Tables("テスト").Columns("年度").Name = "年"
Set cat = Nothing

End Sub

お礼

教えていただきました方法でフィールド名の変更ができました。
ありがとうございました。