アクセス上のクエリには自作関数(値10倍)がありそれが原因で
式に未定義関数'値10倍'があります。となり、ダウンロードができません。
DAOではなく、ADOで自作関数を含むクエリをエクセル上にDLする
ExcelVBAの書き方があればご教示ください。
なお、今回の関数は単価を10倍にするだけの関数なので
エクセル上に式を組めばそれで解決という考え方もありますが、
今後より複雑な自作関数を組むことを考えて、今回の質問をさせて頂きました。
■Access内のモジュール
Function 値10倍(value) As Long
値10倍 = value * 10
End Function
■Access内のテーブル
単価
500
200
300
■Access内のクエリ(今回エクセルにダウンロードしたいもの)
フィールド:単価,関数: 値10倍([T_item].[単価])
長いので分割して書きます。
現行エラーが出ているVBAを書きます。
■エクセル上のVBA(現行、エラーが出ます。)
Sub DB_Read()
Dim adoCON As New ADODB.Connection
Dim adoRS As New ADODB.Recordset
Dim strSQL As String
Dim odbdDB As Variant
Dim wSheetName As Variant
Dim i As Integer
Dim accApp As Access.Application
Dim rs As New ADODB.Recordset
'カレントディレクトリのデータベースパスを取得
odbdDB = ActiveWorkbook.Path & "\sample.accdb"
'データベースに接続する
adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & odbdDB & ""
adoCON.Open
'DB接続用SQL
strSQL = "SELECT テスト.* FROM テスト ORDER BY テスト.ID;"
'レコードセットを開く
adoRS.Open strSQL, adoCON, adOpenDynamic
'アクティブなシート名を取得
wSheetName = ActiveSheet.Name
'スタート行をセット
i = 3
'テーブルの読み込み
Do Until adoRS.EOF
With Worksheets(wSheetName)
.Cells(i, 1).Value = adoRS!単価
.Cells(i, 2).Value = adoRS!関数
End With
i = i + 1
adoRS.MoveNext
Loop
'クローズ処理
adoRS.Close
Set adoRS = Nothing
adoCON.Close
Set adoCON = Nothing
End Sub
宜しくお願いします。
残念ながら Access のユーザー定義関数を ADO から直接呼び出すことはできないようです。
(ACE データベースエンジンの機能として、ユーザー定義関数が含まれていない為)
回避方法として、Access の VBA でレコードセットを返すモジュールを作成し、それを Excel VBA から Run メソッドで呼び出すという方法があるようです。
■ EXCEL─ADO→ユーザ関数入りAccessクエリを実行すると未定義エラー --DAO、ADO、SQL & Access フォーラム--
http://www.accessclub.jp/bbs6/0020/das6169.html
<参考情報>
■ Microsoft Access 2010 を使用したデータ プログラミング
https://msdn.microsoft.com/ja-jp/library/office/ff965871(v=office.14).aspx