AccessのデータをVBを使って置換する

質問

(1)VisualBasic6.0を用いて、Access2000のデータベースに接続します。

(2)データベースの中のテーブル（テーブル名：T-カレンダー）にあるフィールド（フィールド名：コード）に保存されているデータの一部分を置換したい。（⇒途中のアルファベットを変更したい。）（例；置換前：123FFF789　置換後：123III789）

　テーブルを直接開いて、フィールドにカーソルをあわせて、”編集メニュー”の『検索と置換』から行っても出来るのですが、Accessを触らないでVBから作ったフォームから変更（置換）をさせたい。
　◇フォームに２つテキストボックスを作って、「検索する文字列」と「置換後の文字列」を入力し、『変換』ボタンを押したら、データが置換される、という流れ。
　　一回きりの置換でなくて、何回でも同じフィールド内のデータを置換したい。

　　SQLのREPLACE関数を用いて出来るかな、とは思っているのですが、どのように作成すればよろしいのでしょうか。
　また他のやり方でシンプルな方法があればお教えください。

回答No.2

freednia · ベストアンサー

ファンクションを使用せずにDAOで接続する方法もあります。

参照設定でMicrosoft DAO ○.○ Object Libraryにチェック

Dim WS As DAO.Workspace
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim FileName As String
Dim TableName As String

FileName = "(ファイルのパス)"
TableName = "(テーブル名)"

Set WS = DBEngine.Workspaces(0)
Set DB = WS.OpenDatabase(FileName)
Set RS = DB.OpenRecordset(TableName, dbOpenDynaset)

RS.MoveFirst
Do Until RS.EOF
RS.Fields(0) = Replace(RS.Fields(0),Text1.Text,Text2.Text)
RS.MoveNext
Loop

RS.Close
DB.Close
WS.Close
Set RS = Nothing
Set DB = Nothing
Set WS = Nothing

これでテキストボックス１に入れた文字列をテキストボックス２に入れた文字列に変換できます。
途中のRS.Fields(0)はテーブルの１列目を指します。
２列目なら(1)です。

お礼

ご解答、ありがとうございます。
先のプログラムでMicrosoft DAO 3.6 Object Libraryにチェックが入っていましたので、お教えいただいたプログラムで作成しています。
途中でエラーが出るのですが、
RS.Fields(0) = Replace(RS.Fields(0),Text1.Text,Text2.Text)
のところで、
実行時エラー3020　UpdateまたはCancelUpdateメソッドには、対応するAddNewまたはEditメソッドが必要です。
というメッセージが出ます。
イミディエイトウィンドウで参照すると、希望しているテーブル内のフィールド名が見えてますし、テキストボックス内の任意で入力した文字列も見えます。
もう少しで完成しそうなのですが、お力を貸していただけませんでしょうか。

回答No.4

freednia

#2です。
すいません。抜けがありました。

RS.Fields(0) = Replace(RS.Fields(0),Text1.Text,Text2.Text)

を

RS.Edit
RS.Fields(0) = Replace(RS.Fields(0), Text1.Text, Text2.Text)
RS.Update

に変えてください。
うっかりしてました。

お礼

丁寧にお教えいただきありがとうございました。
おかげさまでうまくいきました。感謝申し上げます。

回答No.3

nandarou-1011

#1の補足です。
ADOを使いますので、VBのメニューから[プロジェクト]－[参照設定]を選び、Microsoft ActiveX Data Objects *.* Libraryにチェックを入れてください。

回答No.1

nandarou-1011

まず、標準モジュールで以下のようにAccessに接続するためのFunctionを作成してください。

Public adCN As New ADODB.Connection

Public Function Connection() As Boolean
Dim ConnectString As String
On Error GoTo Error_Enumerator
ConnectString = "DRIVER={Driver do Microsoft Access (*.mdb)};" _
& "DefaultDir=C:\(MDBのあるフォルダ名);" _
& "DBQ=****.mdb;"
adCN.Open ConnectString
Connection = True
Exit Function

Error_Enumerator:
Connection = False
End Function

次にForm_Loadイベントなどで以下のようにしてコネクションを確立させてください。

If Not Connection Then MsgBox "接続に失敗しました", vbCritical:End

そして、コマンドボタンのクリックイベントなどで、以下のようにしてSQL文を作成し実行させてください。

Dim strVar as String
strVar = "Update テーブル名 Set フィールド名 = Replace(フィールド名, '" & Text1 & "','" & Text2 & "')"　←必要であればWhere句も追加してください。
adCN.Execute strVar

※Text1 置換前文字列，Text2 置換後文字列