クエリで抽出したデータに書き込みたい

質問

どうしても分からなくなってしまったので、質問させていただきます。

以前、連番フィールドだけのテーブルを作りたいと質問して以下のような回答を頂きました。もちろん回答内容で連番フィールドの作成はできました。

Private Sub Sample1()
　　Dim rs As New ADODB.Recordset
　　Dim i As Integer

　　rs.Open "ZZZZ連番テーブル", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
　　For i = 1 To 9999
　　　　rs.AddNew
　　　　rs("仮想ID") = "ZZZZ" & Format(i, "0000")
　　　　rs.Update
　　Next
　　rs.Close
End Sub

そして今回は、また連番を振りたいのですが
今回は条件が加わっていまして、元テーブルからまずクエリで既存データの登録番号を昇順(空き番号ではないが番号が所々飛んでいるもの)で抽出します。
この虫食いのように飛んでいる番号を綺麗に連番にしようと思い、まずは別フィールドに変更したい連番のフィールドを作成したいのですが。
これがうまくいきません。
上に記述したVBAでは既にレコードがあるデータにAddNewが有効でないのは分かるのですが、
どのようにすれば元テーブルから抽出クエリ(昇順で抽出したデータ)を使って
別フィールドに連番を振ることができるのでしょうか？
フィールドを書き換えるのではなく別フィールドに連番を振るだけなのですが・・・

回答No.4

30246kiku · ベストアンサー

> ご指摘の通り(1)側の Like 'ZZZZ*'　になっていました。
> ADO用のクエリ(抽出条件 Like 'ZZZZ%')を作り、クエリが変わるのでフォームも作り変えて再度試してみたところ

何か大げさになってきましたね。

「連番」部分だけ更新/設定するだけでしたので、クエリを使わずやっていたのが例でした。

以下の Sample3 を、VBEのどこでもいいので貼り付け後、
☆１でコメントを付けている部分の、☆～☆の文字列を正式なもの（フィールド名）に書き換えて実行してみてください。
（実行する前にデータはバックアップしておいてください）

実行は、 Sample3 内をクリックし、カーソルが入ったことを確認し、
メニュー付近にある再生ボタンのような横三角をクリックします。
（もしくは、F5キーを押します）

Private Sub Sample3()
　　Dim rs As New ADODB.Recordset
　　Dim i As Integer
　　Const フィールド名 As String = "☆連番☆"　　　　' ☆１
　　Const テーブル名 As String = "☆元テーブル☆"　　' ☆１
　　Const 抽出 As String = "☆登録番号☆"　　　　　　' ☆１
　　Const 並び替え As String = "☆登録番号☆"　　　　' ☆１

　　i = 1
　　rs.Source = "SELECT " & フィールド名 & " FROM " & テーブル名 & _
　　　　　　　　" WHERE " & 抽出 & " LIKE 'ZZZZ%' ORDER BY " & 並び替え & ";"
　　rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
　　While (Not rs.EOF)
　　　　rs(フィールド名) = "ZZZZ" & Format(i, "0000")
　　　　rs.Update
　　　　rs.MoveNext
　　　　i = i + 1
　　Wend
　　rs.Close
End Sub

※　クエリという言葉がよく使われているので、そのクエリが更新できないものなのでは？と疑っています。

お礼

ご回答ありがとうございます。
環境が会社になので、結果報告などは月曜日になってしまうのですがよろしくお願いします。

補足

お礼の続きで書き込みします。
分かれてしまって申し訳ありません。
>クエリという言葉がよく使われているので、そのクエリが更新できないものなのでは？と疑っています。
クエリ自体はクエリ作成ウィザードから作ってみたのですが、もしかするとこの作成方法ではクエリでデータを抽出できても
その抽出データでは元データのフィールドに書き込みなどできない。
とかあるのでしょうか・・・
環境がないため自宅で見させていただきましたが、月曜日に早速ためさせていただき書き込みしますので申し訳ないのですがお付き合いくださいませ＞＜
この連番を振ったあとにも大物があるので焦ってます。。。（汗

回答No.6

30246kiku

> 「データのバックアップ」というのは

テスト用のデータをすぐ作り直せるのなら不要と思います。
本チャンのデータを使うということであれば、

mdbファイルなら
エクスプローラでコピーする程度でよいと思います。

それ以外なら、
データを戻せる方法（今までやっていた方法？）で、取っておけばよいと思います。

お礼

ご回答ありがとうございます！
本当に助かりました！ありがとうございました！
思っていた通りの連番を綺麗に振ることができました！
感謝いたします。

Sample3とっても見やすく分かりやすくて30246kikuさんにはとても感謝しています。

回答No.5

30246kiku

> 以下の Sample3 を、VBEのどこでもいいので貼り付け後、

ごめんなさい。
標準モジュール内ということでした。

※ １度実行すれば用済みになると思ってます。
（Private で作成しているので、手動で実行してください）

補足

こちらの回答もありがとうございます。
先の回答の「データのバックアップ」というのは
http://office.microsoft.com/ja-jp/access/HA102355861041.aspx
こちらの方法でいいのでしょうか？
それとも
http://www.accessclub.jp/samplefile/samplefile_160.htm
こちらの方法のものがよいのでしょうか？

何から何まで聞いてしまって申し訳ありません。
すぐに回答いただけるとは思いませんが、明日早くにやってみたいと思いましたので・・・

回答No.3

30246kiku

> 混ざっているものをZZZZだけを抜き出して(昇順で)いるものですが

この抜き出しに指定した文字列はどのような記述になっていますか。
WHERE 部分を以下に書きます。

(1)
WHERE 登録番号 LIKE 'ZZZZ*'

(2)
WHERE 登録番号 LIKE 'ZZZZ%'

(1)は DAO での書き方で、(2)は ADO での書き方です。

例で示した記述は ADO なので、(2)の記述になります。
一度確認してみてください。

補足

ご回答ありがとうございます。
クエリの抽出条件を確認してみました。
ご指摘の通り(1)側の Like 'ZZZZ*'　になっていました。
ADO用のクエリ(抽出条件 Like 'ZZZZ%')を作り、クエリが変わるのでフォームも作り変えて再度試してみたところ

Private Sub Sample2()
　　Dim rs As New ADODB.Recordset
　　Dim i As Integer

　　i = 1
　　rs.Source = "SELECT * FROM 元テーブル ORDER BY 登録番号;"
　　rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
　　While (Not rs.EOF)
　　　　rs("連番") = "XXXX" & Format(i, "0000")
　　　　rs.Update ←←←
　　　　rs.MoveNext
　　　　i = i + 1
　　Wend
　　rs.Close
End Sub

←←←の部分で黄色いマーカーで止まってしまいます
エラーとしましては、
ODBC--リンクテーブル '元テーブル'での更新に失敗しました。
となってしまいます。何が考えられますでしょうか？

回答No.2

30246kiku

> エラーは出ないのですが、登録番号には何も書き込みされていませんでした。

元テーブルの中の「連番」フィールドにのみ値を設定しています。
登録番号には既に値が入っており、登録番号昇順でレコードセットを操作します。

> フィールドを書き換えるのではなく別フィールドに連番を振るだけなのですが・・・
と書かれていましたので

お礼

すみません図がずれました！
お礼投稿で申し訳ありません

登録番号　　　連番　→　登録番号　　　連番
(現在)　　　　　　　　　　(動作後)　　　
ZZZZ0001　　　　　　　　ZZZZ0001　　　ZZZZ0001
ZZZZ0003　　　　　　　　ZZZZ0003　　　ZZZZ0002
ZZZZ0004　　　　　　　　ZZZZ0004　　　ZZZZ0003
ZZZZ0006　　　　　　　　ZZZZ0006　　　ZZZZ0004
ZZZZ0007　　　　　　　　ZZZZ0007　　　ZZZZ0005

補足

ご回答ありがとうございます。
申し訳ありません。こちらの記載ミスでした・・・
そうです。連番が入るはずのフィールドに何も書かれていないのです。
エラーはないのですが何か動作した様子もありません。
登録番号　 連番 → 登録番号 連番
(現在)　　　
ZZZZ0001 ZZZZ0001 ZZZZ0001
ZZZZ0003 ZZZZ0003 ZZZZ0002
ZZZZ0004 ZZZZ0004 ZZZZ0003
ZZZZ0006 ZZZZ0006 ZZZZ0004
ZZZZ0007 ZZZZ0007 ZZZZ0005
動作後はこの様にしたいのですが、
登録番号も連番も同じテーブルで問題ないですよね？
ちなみにrs.Open後のクエリは固有情報(ABCD)と不特定情報(ZZZZ)
等が混ざっているものをZZZZだけを抜き出して(昇順で)いるものですが
こちらも問題はないでしょうか

回答No.1

30246kiku

元テーブルに「連番」フィールドを追加したとして、
その「連番」に、仮想ID先頭から埋めていく例となります。

Private Sub Sample1()
　　Dim rs As New ADODB.Recordset
　　Dim rsUP As New ADODB.Recordset

　　rs.Source = "SELECT 仮想ID FROM ZZZZ連番テーブル ORDER BY 仮想ID;"
　　rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
　　rsUP.Source = "SELECT * FROM 元テーブル ORDER BY 登録番号;"
　　rsUP.Open , CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
　　While (Not rsUP.EOF)
　　　　If (Not rs.EOF) Then
　　　　　　rsUP("連番") = rs("仮想ID")
　　　　　　rsUP.Update
　　　　　　rs.MoveNext
　　　　End If
　　　　rsUP.MoveNext
　　Wend
　　rs.Close
　　rsUP.Close
End Sub

※ WHERE 条件など、適宜追加してください。

ZZZZ連番テーブル が関係しないものでしたら、以下となります。

Private Sub Sample2()
　　Dim rs As New ADODB.Recordset
　　Dim i As Integer

　　i = 1
　　rs.Source = "SELECT * FROM 元テーブル ORDER BY 登録番号;"
　　rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
　　While (Not rs.EOF)
　　　　rs("連番") = "XXXX" & Format(i, "0000")
　　　　rs.Update
　　　　rs.MoveNext
　　　　i = i + 1
　　Wend
　　rs.Close
End Sub

補足

ご回答ありがとうございます！
>ZZZZ連番テーブル が関係しないものでしたら、以下となります。
こちらを参考にさせていただきました。
エラーは出ないのですが、登録番号には何も書き込みされていませんでした。
何か書き込む情報がたりないのでしょうか
変更した部分は
元テーブル。登録番号。連番。XXXX。
rs.Openの後に使用するクエリ名です。

何か追加するのがたりないのでしょうか・・・