Microsoft Access Club Access超初心者対象Forum Access初級者対象Forum Access VBA Tips Forum DAO、ADO、SQL Forum

     

リストへもどる

投稿記事の一括表示

タイトルストアドプロシージャのインポート
記事No42827
投稿日: 2017/10/14(Sat) 08:19
投稿者DIO
解決済: ON
OS:windows7
Access Version:2007

こんにちは
タイトルの通り、外部アクセスファイルからストアドプロシージャの結果をテーブルとしてインポートしたいです。
以下がコードになります。
-----------------------------------------------------------
Private Sub データ更新_Click()
DoCmd.TransferDatabase acImport, _
"Microsoft Access", "C:\〜\AccessDB.adp", acStoredProcedure, "プロシージャ名", "テーブル名"
End Sub
-----------------------------------------------------------
これを実行すると
「実行時エラー'7861':
'|'は、Accessデータベースファイルにインポート、エクスポート、またはコピーできません」
とエラーが表示されます。

どうすればストアドプロシージャをインポートできるでしょうか?

タイトルRe: ストアドプロシージャのインポート
記事No42829
投稿日: 2017/10/16(Mon) 14:36
投稿者mayu
解決済: ON
> 外部アクセスファイルからストアドプロシージャの結果をテーブルとしてインポートしたいです。

ストアドがある場所は、ADPファイルの中ではなく、
SQL Serverがインストールされているサーバ上になります。

また、ストアドを実行するには、Exec( Execute )ステートメントを呼び出す必要があり、
場合によっては、パラメータの入出力も必要ですが
TransferDatabaseメソッドには
インタラクティブにクエリを処理する機能はありません。
( ADPで実施するなら、DoCmd.OutputTo acOutputStoredProcedure )

以上のことから、目的と手段が合致していないように思います。

> どうすればストアドプロシージャをインポートできるでしょうか?

まずは、SQL Server上に登録されたアカウントとパスワードを用い、
Transact-SQLを駆使して、手動でストアドを実行することは出来ますか。

また、ストアドの中身と引数について理解されていますか。
ここが曖昧だとアドバイスが難しいです。

タイトルRe^2: ストアドプロシージャのインポート
記事No42830
投稿日: 2017/10/17(Tue) 09:58
投稿者DIO
解決済: ON
> まずは、SQL Server上に登録されたアカウントとパスワードを用い、
> Transact-SQLを駆使して、手動でストアドを実行することは出来ますか。
SQL Serverに触れたことがなく、「Transact-SQL」というのも聞いたことがないのでできないです。
しかし、アカウントとパスワードは管理者に問い合わせば入手できると思います。

> また、ストアドの中身と引数について理解されていますか。
> ここが曖昧だとアドバイスが難しいです。
すみません、上記と同じく全く理解していないです。

タイトルRe^3: ストアドプロシージャのインポート
記事No42831
投稿日: 2017/10/17(Tue) 12:55
投稿者mayu
解決済: ON
> SQL Serverに触れたことがなく、「Transact-SQL」というのも聞いたことがないのでできないです。

ストアドに対しては、作成・編集から クエリの発行に至るまで
Transact-SQL という SQL-Server用の構文を用いて操作します。
Access-SQL用の構文や関数は一切使えません。

質疑応答を続けるかどうかは、DIOさんの判断に委ねますが
未経験でいきなりストアドの操作 という状況は回避できなかったのでしょうか...。
SQL-Server未経験ですと、回答内容の理解や意志の疎通を含め、
解決へのハードルは非常に高いと思います。

> しかし、アカウントとパスワードは管理者に問い合わせば入手できると思います。
> すみません、上記と同じく全く理解していないです。

ということは、SQLServer接続先の環境も ご存知ないものと推測します。
とりあえず、回答に必要な情報を以下に掲載します。

無理そうでしたら、スキルを磨いて出直すことも視野に入れましょう。

----------------------------------------------------------------------------------
【 1 】 DIOさんの環境
----------------------------------------------------------------------------------
SQL Serverへ接続してストアドを発行するにあたり、

 ・ SQL-Serverがインストールされている { ホスト名( IPアドレス ) }
 ・ SQL-Serverの { インスタンス名 }
 ・ ストアドが登録されている { データベース名 }
 ・ データベースに接続する { アカウント名 }
 ・ データベースに接続する { パスワード }
 ・ SQL-Serverの { バージョン }
 ・ SQL-Serverへの接続するアカウントの { 種別( SQLServer認証 / Windows認証 ) }

といった情報は、認識している必要がありますので
管理者に相談して入手・把握なさって下さい。
なお、バージョンだけは 判明次第、こちらに記載いただけますか。
他は DIOさんだけが把握していれば結構です。

----------------------------------------------------------------------------
【 2 】 ストアドのSQL
----------------------------------------------------------------------------
 ・ ADPのSQLビューから調べる
 ・ SQL Server Management Studio などのツールを使って構造を調べる
 ・ 管理者に助力を仰ぐ

方法は問いませんので
ストアドの中身( SQL )をここに貼り付けていただけますか。

タイトルストアドの情報取得サンプル(ADP経由)
記事No42832
投稿日: 2017/10/18(Wed) 17:08
投稿者mayu
解決済: ON
ADP 経由で ストアドの構造や SQL-Server の実行環境を取得するサンプルを載せておきます。

 ● ---> ADP自身 及び SQL-Server への接続情報
 ◆ ---> SQL-Server のバージョン
 ▼ ---> ストアドプロシージャの SQL
 ■ ---> ストアドプロシージャの実行結果
'---------------------------------------------------------------------------------------------

Sub get_ADP_information()
  Const TARGET_ADP           As String = "外部 ADPファイルのフルパス"
  Const PROCEDURE_NAME         As String = "実行対象ストアドプロシージャの名前"
  Const ADO_SCHEMA_PROCEDURE_PARAMETERS As Long = 26
  Dim adpApp              As Access.Application
  Dim oProject             As Access.CurrentProject
  Dim i                 As Long
  Dim lngFileNumber           As Long
  Dim lngInputParamCount        As Long
  Dim strSQL              As String
  Dim strOutputDir           As String
  Dim strFilePath            As String
  Dim blFlag              As Boolean
  
  If (LenB(Dir(TARGET_ADP)) = 0) Then Exit Sub
  
  Set adpApp = New Access.Application
  adpApp.OpenAccessProject filepath:=TARGET_ADP, Exclusive:=False
  
  For i = 0 To adpApp.CurrentData.AllStoredProcedures.Count - 1
    If (adpApp.CurrentData.AllStoredProcedures.Item(i).FullName = PROCEDURE_NAME) Then
      blFlag = True
      Exit For
    End If
  Next i
  
  Set oProject = adpApp.CurrentProject
  Rem oProject.CloseConnection
  Rem oProject.OpenConnection BaseConnectionString:="Provider=SQLOLEDB;Data Source=..."
  Debug.Print oProject.AccessConnection '●
  
  With oProject.Connection
    With .Execute("SELECT @@VERSION")
      Debug.Print vbNewLine & .Collect(0) '◆
      .Close
    End With
    
    If (blFlag) Then
      With .OpenSchema(ADO_SCHEMA_PROCEDURE_PARAMETERS, Array(Empty, Empty, PROCEDURE_NAME))
        While (Not .EOF)
          If (CStr(Nz(.Fields("PARAMETER_TYPE").Value, "")) = "1") Then
            lngInputParamCount = lngInputParamCount + 1
          End If
          .MoveNext
        Wend
        .Close
      End With
      
      strSQL = "SELECT x.text        " _
          & "  FROM syscomments x " _
          & "     , sysobjects  y " _
          & " WHERE x.id = y.id   " _
          & "   AND y.name = '" & PROCEDURE_NAME & "' ;"
      
      strOutputDir = CreateObject("WScript.Shell").SpecialFolders("Desktop")
      
      strFilePath = strOutputDir _
            & "\" _
            & Format$(Date, "yyyymmdd") _
            & "_" _
            & PROCEDURE_NAME _
            & ".txt"
      
      lngFileNumber = FreeFile()
      
      With .Execute(strSQL)
        Open strFilePath For Output As #lngFileNumber '▼
        Print #lngFileNumber, .GetString()
        Close #lngFileNumber
        .Close
      End With
    End If
  End With
  
  If (blFlag And lngInputParamCount = 0) Then
    strFilePath = strOutputDir _
          & "\" _
          & Format$(Date, "yyyymmdd") _
          & "_" _
          & PROCEDURE_NAME _
          & ".xls"
    
    strSQL = "EXEC " & PROCEDURE_NAME & ";"
    
    adpApp.DoCmd.OutputTo ObjectType:=acOutputStoredProcedure _
              , ObjectName:=strSQL _
              , OutputFormat:=acFormatXLS _
              , OutputFile:=strFilePath _
              , AutoStart:=False '■
  End If
  
  oProject.CloseConnection
  adpApp.CloseCurrentDatabase
  adpApp.Quit
  Set oProject = Nothing
  Set adpApp = Nothing
End Sub

タイトルRe: ストアドの情報取得サンプル(ADP経由)
記事No42833
投稿日: 2017/10/19(Thu) 08:22
投稿者DIO
解決済: ON
数々の有力な情報の提供ありがとうございます。
しかし、無知識な自分が会社のSQLServerを弄るのはさすがにまずいということで今回は諦めさせてもらいます。
SQLServerに触れずにデータを取得する方法があればいいのですが、無理ですよね・・・
たくさん教えていただいたのに申し訳ないです。
時間があったらSQLServerの管理者に教えてもらいながら再度挑戦したいと思います。

タイトルストアドインポートのサンプル
記事No42834
投稿日: 2017/10/19(Thu) 10:29
投稿者mayu
解決済: ON
良い決断だと思います。

> SQLServerに触れずにデータを取得する方法があればいいのですが、無理ですよね・・・

ご希望の動作を実現しようとすると
直接的か間接的( ADP経由 )かの違いはあれ
いずれにしろ、SQL-Server に対する理解や操作は必要になります。

SQL-Serverを多少扱えるようになると、コード中の
  strSQL = ...
  qry.Connect = ...
に設定している値を変更するだけでインポートが実現可能ですから
勉強用にサンプルも載せておきます。

'-------------------------------------------------------------------------------------------------

Sub import_sample()
  Const TEMP_QUERY_NAME  As String = "q_execute_proc"
  Const EXPORT_TABLE_NAME As String = "t_export_result"
  Dim db         As DAO.Database
  Dim qry         As DAO.QueryDef
  Dim strSQL       As String
  
  strSQL = "EXEC ストアド名 "                   & vbNewLine _
      & "                @Int_Argument        = 123 "     & vbNewLine _
      & "              , @Datetime_Argument   = '2017-10-19' " & vbNewLine _
      & "              , @Varchar_Argument    = 'foo_bar' "  & vbNewLine _
      & "              , @Nvarchar_Argument   = N'東京都' ;"
  
  Set db = Application.CurrentDb
  
  If (DCount("*", "[MSysObjects]", "[Type] = 5 And [Name] = '" & TEMP_QUERY_NAME & "'")) Then
    Set qry = db.QueryDefs(TEMP_QUERY_NAME)
  Else
    Set qry = db.CreateQueryDef(TEMP_QUERY_NAME)
  End If
  
  qry.Connect = "ODBC;" _
        & "DRIVER={SQL Server};" _
        & "SERVER=ホスト名\インスタンス名;" _
        & "DATABASE=データベース名;" _
        & "UID=ユーザ名;" _
        & "PWD=パスワード"
  qry.ReturnsRecords = True
  qry.ODBCTimeout = 0
  qry.SQL = strSQL
  db.QueryDefs.Refresh
  Access.Application.RefreshDatabaseWindow
  
  If (DCount("*", "[MSysObjects]", "[Type] = 1 And [Name] = '" & EXPORT_TABLE_NAME & "'")) Then
    If (SysCmd(acSysCmdGetObjectState, acTable, EXPORT_TABLE_NAME) > 0) Then
      DoCmd.Close ObjectType:=acTable, ObjectName:=EXPORT_TABLE_NAME, Save:=acSaveNo
    End If
  End If
  DoCmd.TransferDatabase Transfertype:=acExport _
             , DatabaseType:="Microsoft Access" _
             , DatabaseName:=db.Name _
             , ObjectType:=acTable _
             , Source:=TEMP_QUERY_NAME _
             , Destination:=EXPORT_TABLE_NAME _
             , StructureOnly:=False
  Set qry = Nothing
  Set db = Nothing
End Sub

'-------------------------------------------------------------------------------------------------

なお、TransferDatabase メソッドで自動生成されるテーブルでは
お任せモードということもあり、
フィールドに対するデータ型の割り当てが適当ですので
( 日付/時刻 のデータが文字列型になっている場合が多い )

インポートしたデータを演算などに用いる場合は
あらかじめテーブルは用意しておき、
SQLで DELETE, INSERT 文を発行するほうがいいでしょう。

- 以下のフォームから自分の投稿記事を修正・削除することができます -
処理 記事No パスワード

ページの先頭へ 前ページへ戻る