【補足】ADOとSQL文のワイルドカード

添付図では、テーブル[参加者名簿]の列ＩＤの'あ’の最大値を取得しています。注意を要するのは、そのSQL文です。

＞SELECT MAX(ID) FROM 参加者名簿 WHERE ID Like 'あ%'

と（ADOの場合には）ワイルドカードは'*'ではなく'%'を用います。

＊なお、このようにSQL文はイミディエイトウインドウでテストできます。

投稿日時 - 2016-01-20 15:15:48

自動採番の二つのやり方は２つです。

１、テーブル「採番控え」を用意する。
２、個別テーブルの既存値を取得する。

【テーブル「採番控え」方式】

? NewID("ID列名")
10

Public Function NewID(ByVal strIDName As String) As Long
On Error GoTo Err_NewID
　　 Dim N　　　As Long
　　 Dim strSQL As String
　　 Dim cnn　　As ADODB.Connection
　　 Dim rst　　As ADODB.Recordset
　　
　　 Set cnn = CurrentProject.Connection
　　 Set rst = New ADODB.Recordset
　　 strSQL = "SELECT final_value FROM id管理表 WHERE id_name='" & strIDName & "'"
　　 cnn.Errors.Clear
　　 cnn.BeginTrans
　　 With rst
　　　　 .Open strSQL, _
　　　　　　　 cnn, _
　　　　　　　 adOpenDynamic, _
　　　　　　　 adLockOptimistic
　　　　 If Not .BOF Then
　　　　　　 N = .Fields(0) + 1
　　　　　　 .Fields(0) = N
　　　　　　 .Update
　　　　 End If
　　 End With
　　 cnn.CommitTrans
Exit_NewID:
On Error Resume Next
　　 rst.Close
　　 cnn.Close
　　 Set rst = Nothing
　　 Set cnn = Nothing
　　 NewID = N
　　 Exit Function
Err_NewID:
　　 N = -1
　　 If cnn.Errors.Count > 0 Then
　　　　 ErrMessage cnn.Errors(0), strSQL
　　　　 cnn.RollbackTrans
　　 Else
　　　　 MsgBox "プログラムエラーが発生しました。システム管理者に報告して下さい。(NewID)", _
　　　　　　　　vbExclamation, " 関数エラーメッセージ"
　　 End If
　　 Resume Exit_NewID
End Function

　これは 15年前にアップした私のWEBサイトのコピペ。実際に利用していたNewID()ですが、アップの際に所々に写しミスが混入しているやも知れません。が、やることは伝わるかと思います。

【個別テーブルの既存値参照方式】

? DBLookup("SELECT MAX(列名) FROM テーブル名 WHERE XXXXX"）
10

Public Function DBLookup(ByVal strQuerySQL As String, _
　　　　　　　　　　　　　Optional ByVal ReturnValue = Null) As Variant
On Error GoTo Err_DBLookup
　　Dim DataValue
　　Dim rst As ADODB.Recordset

　　Set rst = New ADODB.Recordset
　　With rst
　　　　.Open strQuerySQL, _
　　　　　　　CurrentProject.Connection, _
　　　　　　　adOpenStatic, _
　　　　　　　adLockReadOnly
　　　　If Not .BOF Then
　　　　　　.MoveFirst
　　　　　　DataValue = .Fields(0)
　　　　End If
　　End With
Exit_DBLookup:
On Error Resume Next
　　rst.Close
　　Set rst = Nothing
　　DBLookup = IIf(Len(DataValue & ""), DataValue, ReturnValue)
　　Exit Function
Err_DBLookup:
　　MsgBox "SELECT 文の実行時にエラーが発生しました。(DBLookup)" & Chr$(13) & Chr$(13) & _
　　　　　 "・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　 "・SQL Text=" & strQuerySQL, _
　　　　　 vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DBLookup
End Function

　個別テーブルの既存値参照方式は、単にSQL文を発行するだけですのでNewID()のような専用の関数は不要。なお、ここでDLookup()、DMax()を利用していないのは、ADOを利用した方がより高速で処理されるからです。

＞顧客名の頭文字を五十音順の「あかさたな」で行ごとに連番をつけて管理している。

これは考え直されたがいいですよ。

理由１：SQL文を作成するコードを書かなければならない。
理由２：意味がない連番体系。

ということで、「あかさたな」で行ごとに連番！の拘られるのであれば、そのＳＱＬ文を作成するコードが課題です。

投稿日時 - 2016-01-20 13:00:26