Access (VBA)

Access VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Access 2016)
DAOからADOに変更
投稿日時: 20/06/22 22:49:26
投稿者: ai

いつもお世話になっております。
 
この度、SQLサーバーに移行するため
まず初めにDAOで書かれたVBAを、全てADOに書き換えを考えております。
 
今まで動いていた物があるのですが、
パラメータクエリ付のADOの書き方が分かりません・・・。
動いているDAOのVBAは下記のようになっております。
 
 
 
 
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
 
                     
Dim qd As DAO.QueryDef
Dim pr1 As Date
Dim pr2 As Date
                     
 
Set db = CurrentDb
Set qd = db.QueryDefs("パラメータ付クエリ名")
        
              
pr1 = [Forms]![メインメニュー]![抽出基準日_始]
pr2 = [Forms]![メインメニュー]![抽出基準日_終]
     
qd.Parameters("[Forms]![メインメニュー]![抽出基準日_始]").Value = pr1
qd.Parameters("[Forms]![メインメニュー]![抽出基準日_終]").Value = pr2
     
Set rs1 = qd.OpenRecordset 'インプットファイル
                         
Set rs2 = db.OpenRecordset("アウトプットテーブル名") 'アウトプットファイル
 
                         
Do Until rs1.EOF
 
 
  
  rs2.AddNew
  rs2!項目 = rs1!項目
    rs2.Update
  rs1.MoveNext
 
 
 
Loop
 
Set db = Nothing
Set rs1 = Nothing
Set rs2 = Nothing
 
 
 
大変お手数なのですが上記のVBAを
ADOに変更して書くと、どの様に書けば良いのか記載して頂くと助かります。
ご教示のほど、よろしくお願い致します。

回答
投稿日時: 20/06/23 18:06:59
投稿者: sk

引用:
この度、SQLサーバーに移行するため
まず初めにDAOで書かれたVBAを、全てADOに書き換えを考えております。

「 ADO によって SQL Server データベースに直接アクセスする
(フロントエンド側のクエリではなく、バックエンド側の
ストアドプロシージャを呼び出す)」という話なのか、
「フロントエンド側のリンクテーブルの参照先を
SQL Server データベース上のテーブル/ビューに変更する
(フロントエンド側のクエリはそのまま使う)」という話なのか、
どちらなのでしょうか。
 
引用:
今まで動いていた物があるのですが、
パラメータクエリ付のADOの書き方が分かりません・・・。

とりあえず後者と仮定します。
 
--------------------------------------------------------------------
 
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
  
Dim pr1 As Date
Dim pr2 As Date
                      
pr1 = [Forms]![メインメニュー]![抽出基準日_始]
pr2 = [Forms]![メインメニュー]![抽出基準日_終]
 
Set cn = CurrentProject.Connection
 
Set cmd = New ADODB.Command
 
With cmd
    Set .ActiveConnection = cn
    .CommandType = adCmdStoredProc
    .CommandText = "パラメータ付クエリ名"
    Set prm = .CreateParameter("[Forms]![メインメニュー]![抽出基準日_始]", _
                               adDate, adParamInput, , pr1)
    .Parameters.Append prm
    Set prm = Nothing
    Set prm = .CreateParameter("[Forms]![メインメニュー]![抽出基準日_終]", _
                               adDate, adParamInput, , pr2)
    .Parameters.Append prm
    Set prm = Nothing
    Set rs1 = .Execute
End With
                          
Set rs2 = New ADODB.Recordset
rs2.Open "SELECT * FROM [アウトプットテーブル]", cn, adOpenKeyset, adLockOptimistic
 
Do Until rs1.EOF
    rs2.AddNew
    rs2![項目] = rs1![項目]
    rs2.Update
    rs1.MoveNext
Loop
  
Set rs2 = Nothing
Set rs1 = Nothing
Set cmd = Nothing
Set cn = Nothing
 
--------------------------------------------------------------------

投稿日時: 20/06/24 17:57:20
投稿者: ai

sk様
 
 
ありがとうございます!
記載頂いた内容で、問題なく動いているようです!
(お察しの通りフロントエンド側のクエリはそのまま使う予定です)
 
 
一点お聞きしたいのですが
 
rs2.Open "SELECT * FROM [アウトプットテーブル]", cn, adOpenKeyset, adLockOptimistic
 
と記載頂いたところなのですが
「adOpenKeyset」の所が、空白のVBAサンプルをよく見ます。
空白だと「adOpenForwardOnly」が入っていることになるらしいのですが
「adOpenKeyset」を指定してあげた方がよろしいのでしょうか?
(データベースに矛盾がなくなるなど)
 
調べてみたのですが、今一理解出来なく
大変お手数なのですが、ご教示頂けましたら幸いです。

回答
投稿日時: 20/06/25 11:19:07
投稿者: sk

引用:
rs2.Open "SELECT * FROM [アウトプットテーブル]", cn, adOpenKeyset, adLockOptimistic

引用:
「adOpenKeyset」の所が、空白のVBAサンプルをよく見ます。
空白だと「adOpenForwardOnly」が入っていることになるらしいのですが
「adOpenKeyset」を指定してあげた方がよろしいのでしょうか?
(データベースに矛盾がなくなるなど)

ADODB.Recordset オブジェクトの Open メソッドの実行時において、
「指定されたカーソルの種類(あるいは省略時の既定値)」と、
「実際に適用されるカーソルの種類」は必ずしも一致しません
 
実際にどのカーソル/ロックの種類が適用されるかは、CursorLocation プロパティ、
CursorType プロパティ、LockType プロパティ(及び Connection オブジェクトの
CursorLocation プロパティ)の設定値の組み合わせによって決定されます。
 
例えば CursorLocation プロパティに対して定数 adUseClient が
指定された場合、レコードセットが開かれた際のカーソルの種類は
無条件で静的カーソルになります。
 
(標準モジュール)
------------------------------------------------------------------
Sub CursorTypeTest1()
 
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
     
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
     
    With rs
        Set .ActiveConnection = cn
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Source = "テーブル名"
         
        Debug.Print "指定時のカーソルの種類: " & GetAdoCursorTypeName(.CursorType)
        Debug.Print "指定時のロックの種類: " & GetAdoLockTypeName(.LockType)
         
        .Open
         
        Debug.Print "実際に適用されるカーソルの種類: " & GetAdoCursorTypeName(.CursorType)
        Debug.Print "実際に適用されるロックの種類: " & GetAdoLockTypeName(.LockType)
         
        .Close
    End With
     
    Set rs = Nothing
    Set cn = Nothing
 
End Sub
 
'カーソルの種類を文字列で返す関数
Function GetAdoCursorTypeName(CursorType As ADODB.CursorTypeEnum) As String
 
    Select Case CursorType
        Case adOpenDynamic
            GetAdoCursorTypeName = "動的カーソル"
        Case adOpenForwardOnly
            GetAdoCursorTypeName = "前方スクロールカーソル"
        Case adOpenKeyset
            GetAdoCursorTypeName = "キーセットカーソル"
        Case adOpenStatic
            GetAdoCursorTypeName = "静的カーソル"
        Case adOpenUnspecified
            GetAdoCursorTypeName = "種類の指定なし"
        Case Else
            GetAdoCursorTypeName = "指定が正しくありません"
    End Select
 
End Function
 
'ロックの種類を文字列で返す関数
Function GetAdoLockTypeName(LockType As ADODB.LockTypeEnum) As String
 
    Select Case LockType
        Case adLockBatchOptimistic
            GetAdoLockTypeName = "共有的バッチ更新"
        Case adLockOptimistic
            GetAdoLockTypeName = "レコード単位の共有的ロック"
        Case adLockPessimistic
            GetAdoLockTypeName = "レコード単位の排他的ロック"
        Case adLockReadOnly
            GetAdoLockTypeName = "読み取り専用"
        Case adLockUnspecified
            GetAdoLockTypeName = "種類の指定なし"
        Case Else
            GetAdoLockTypeName = "指定が正しくありません"
    End Select
 
End Function
------------------------------------------------------------------
 
仮に CursorLocation プロパティと CursorType プロパティの指定を省略していても、
LockType プロパティに定数 adLockOptimistic が指定されていれば、
Open 時のカーソルの種類はキーセットカーソルとなります。

投稿日時: 20/07/02 22:08:03
投稿者: ai

sk様
 
 
お礼が遅くなってしまい、申し訳ありませんでした!
詳しいご説明ありがとうございます!
丁寧にご教示頂きましたので理解が深まりました。
この場を借りてお礼申し上げます^^