none
ActiveXとODBC接続――VBAで接続に失敗する原因は? Access/Excel/CSV/SQL Server/MySQLそれぞれの場合の具体的コーディング RRS feed

  • 質問

  •  ActiveXコントロールやODBC接続のVBAコーディングについてご教示ください。

    環境
    Windows 10 Pro(x64)
    Access 2016(x86 / 拡張子.accdb)
    Excel 2016(x86 / 拡張子.xlsx)

    1. Access側からExcelへ接続する場合
    2. Excel側からAccessへ接続する場合
    3. Access側からCSVファイルへ接続する場合
    4. Access側からMySQLへ接続する場合 
    5. Excel側からMySQLへ接続する場合 
    6. DSNとx86(32ビット)x64(64ビット)の問題

     最後の[6]はVBAコーディングのミスではなく、DSNなどが原因で起こるそうです。
     DBサーバー側のリモート接続許可、権限などはキチンとできている、構成マネージャーのTCP/IP設定、FWのポート開け等々の設定はキチンとできているにも関わらず、ODBC接続に失敗する場合、同名で構成違いのDSN(システムDSNとユーザーDSN)が存在していることが原因だそうです。Word/Excel/AccessなどのOfficeの64ビット版には、一部不具合があるということで、32ビット版がインストールされていることが多いと思います。OSはWindows 10という64ビットOSなのに、ExcelやAccessは32ビットなので、32ビットExcelやAccessから設定したDSNを呼び出すと、エラーメッセージが表示される、ということだそうです。32ビット版のODBCドライバーをインストールしようと思います。
     
     ODBC接続は色々とハードルが高いですね。ActiveXコントロールやODBC接続のVBAコーディングも、その一つでしょう。下記VBAコーディングが明らかにミスなのはわかるのですが……ODBC接続にはいったん成功したのですが、それを何回も書き換えたりしているうちに、自分が未熟過ぎてどう書けばAccess側からExcelへ接続できたのか分からなくなってきました。どうか、ご指導ご教示のほどよろしくお願い申し上げます。

    [1]の場合
    Sub  myConnection ()

        Dim cn As ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim xlApp As Object
        Dim xlBook As Object
        Dim xlSheet As Object

        -----------中略-------------------

    ' *******************************************
    ' 接続(Access側からExcelブックに接続する)
    ' *******************************************

        Set cn = New ADODB.Connection

        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                            & "Data Source="C:\SQL\ABC.xlsx";" & "ReadOnly=False;"

        cn.Open

        Set xlApp = CreateObject("Excel.Application")
        Set rs = CreateObject("ADODB.Recordset")
        rs.Open "T_UNION", cn, adOpenKeyset, adLockOptimistic

    ' *******************************************
    ' SQL(SQL文の記述。省略)
    ' *******************************************
    Set cn = Nothing
    Set rs = Nothing
    End Sub


    • 編集済み Cosmology-18 2017年4月24日 16:16 OSとOfficeソフトのビットの違いを書き添えた
    2017年4月24日 16:09

すべての返信

  • 追記:

    cn.Provider = "Microsoft.ACE.OLEDB.12.0"
    cn.Properties("Extended Properties").Value = "Excel 12.0; IMEX=1; Database=C:\SQL\ABC.xlsx "
    cn.Open

    "IMEX=1"等を追加しました。

    2017年4月25日 1:39