エクセル上でADOを用いて、アクセス上のクエリを出力するVBAを作成したいです。

アクセス上のクエリには自作関数(値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].[単価])

長いので分割して書きます。

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2015/10/17 21:59:42
  • 終了:2015/10/24 22:00:07
id:kazkaz23

現行エラーが出ている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

宜しくお願いします。

回答(1件)

id:cx20 No.1

cx20回答回数603ベストアンサー獲得回数1072015/10/17 23:25:05

残念ながら 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

id:kazkaz23

ありがとうございます。逆にDAOなら可能でしょうか?

2015/10/18 21:45:12
id:cx20

DAO であってもユーザー定義関数をクエリに含めることはできません。
Jet / ACE データベースエンジンにユーザー定義関数が含まれていない為です。
以下は、VBScript を使用した DAO 使用サンプルです。

' File: DaoTest.vbs
' Usage: CScript //Nologo DaoTest.vbs
Option Explicit
 
Call Main()
 
Sub Main()
    Dim dbe
    Dim db
    Dim rs
    Set dbe = CreateObject("DAO.DBEngine.120")
    Set db = dbe.OpenDatabase("Hello.mdb")
    Set rs = db.OpenRecordset("SELECT x10(1) AS Message")
    'Set rs = db.OpenRecordset("SELECT * FROM q1")
    WScript.Echo rs(0).Value
End Sub
<実行結果>
DAO.Database: 式に未定義関数  'x10' があります。

<参考>
■ Hello, DAO World!
http://cx20.main.jp/blog/hello/2012/03/22/hello-dao-world/

2015/10/18 22:51:37

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません