エクセル上で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回まで
  • 登録:
  • 終了: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

回答回数607ベストアンサー獲得回数108

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

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

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

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

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

回答リクエストを送信したユーザーはいません