none
SQLserverからAccessにテーブルを作成する RRS feed

  • 質問

  • 初めまして。お世話になります。

    【環境】
    ・SQL Server2008 R2
    ・Microsoft Access 2010

    【質問】
    SQL Serverのストアドプロシージャにて、
    SQL Server側にあるテーブルを、
    AccessのMDBファイルにエクスポートする方法を教えてください。

    【理由】
    Access側でADOを使用すると
    SQL ServerのテーブルをMDBファイルにエクスポートすることができました。
    以下のコードは、SQL Serverにある『テーブルA』を、Access側で『テーブルB』として作成するものです。

    Dim strSQL As String
    Dim DB As ADODB.Connection
    
    Set DB = CurrentProject.Connection
    
    strSQL = "SELECT"
    strSQL = strSQL & " テーブルA.*"
    strSQL = strSQL & " INTO テーブルB"
    strSQL = strSQL & " FROM テーブルA"
    strSQL = strSQL & " IN ''[ODBC;driver={SQL Server};server=サーバー名;uid=ログインID;pwd=パスワード;database=データベース名]"
    DB.Execute strSQL
    
    Set DB = Nothing

    同じようなことがSQL Serverのストアドプロシージャでもできるのかと考え、質問しました。
    よろしくお願い致します。
     

    2011年9月27日 2:08

回答

  • SQL Serverの機能で、リンクサーバーというものがあります。

    Management Studio の、サーバーオブジェクト => リンクサーバーで作成してみて下さい。

    ここでリンクサーバーを登録すると、Accessであれば下記の様に取得出来ます。

    SELECT * FROM [リンクサーバー名]...[テーブル名]

    ※ストアドプロシージャでの動作も確認出来てます。

    • 回答としてマーク nyandaba 2011年9月28日 6:19
    2011年9月27日 6:06
  • まず、最初にですが、SQL Server における SELECT ・・・ INTO ・・・ FROM ・・・ の様なSQL文は、

    ACCESS では使えません。

     

    ですので、CREATE TABLE の後に INSERT するという様にする形になると思います。

    1. リンクサーバーのプロパティで、サーバーオプションの RPC 及び RPC 出力 を True にする。

    ※これをしないと、2.における CREATE TABLE のSQL文がエラーになります。

    2. CREATE TABLE 文を発行する。

    この時、下記の様な形で実行しないとエラーになります。

    EXECUTE ('CREATE TABLE [テーブルB](・・・)') AT [リンクサーバー名]

    実際には、CREATE TABLE 文を文字列変数などを用いて連結していくと綺麗かと思います。

    ※SELECT * FROM sys.columns WHERE object_id = (SELECT object_id FROM sys.tables WHERE name = 'テーブルA') ORDER BY column_id

    上記のSQL文で列定義を取得してCREATE 文を生成すると良いと思います。

     

    DECLARE @sql VARCHAR(8000)

    @sql = ''

    @sql = @sql + 'CREATE TABLE [' + テーブル名 + ']'

    @sql = @sql + ' ('

    ・・・ここで sys.column の内容を FETCH して内容を生成する。

     

    EXECUTE (@sql) AT [リンクサーバー名]

     

    3. INSERT 文を発行する。

    INSERT INTO [リンクサーバー名]...[テーブルB] SELECT * FROM テーブルA

     

    以上で出来ると思います。

    わかりづらい部分があるかと思いますが、頑張って下さい。

    • 回答としてマーク nyandaba 2011年9月28日 6:17
    2011年9月27日 8:57

すべての返信

  • SQL Serverの機能で、リンクサーバーというものがあります。

    Management Studio の、サーバーオブジェクト => リンクサーバーで作成してみて下さい。

    ここでリンクサーバーを登録すると、Accessであれば下記の様に取得出来ます。

    SELECT * FROM [リンクサーバー名]...[テーブル名]

    ※ストアドプロシージャでの動作も確認出来てます。

    • 回答としてマーク nyandaba 2011年9月28日 6:19
    2011年9月27日 6:06
  • aviator__さん、こんにちは。
    回答をしていただき、ありがとうございます。

    早速、教えていただいたリンクサーバーをManagement Studioにて作成してみたところ、
    Accessのテーブルに対し、クエリからSELECT、UPDATE、INSERT、DELETEの処理を行うことができました。

    そこで、
    SQL Serverにある『テーブルA』を、同じSQL Server側にて『テーブルC』として作成できるか、
    以下のコードで試してみたところ、正常に動作し、『テーブルC』は作成されました。

    SELECT テーブルA.* INTO テーブルC FROM テーブルA
    

    同じ要領で、
    SQL Serverにある『テーブルA』を、Access側にて『テーブルB』として作成できるか、
    以下のコードで試してみたところ、エラーが発生しました。

    SELECT テーブルA.* INTO [リンクサーバー名]...[テーブルB] FROM テーブルA

    【エラー内容】
    オブジェクト の名前 'リンクサーバー名...テーブルB' は、プレフィックスの最大数を超えています。最大数は 2 です。

    また、
    オブジェクトエクスプローラの以下の場所を右クリックして表示されるコンテキストメニュ―の中に
    『新しいテーブル』という項目はありませんでした。

    【オブジェクトエクスプローラ】
    「サーバー名」≫「サーバオブジェクト」≫「リンクサーバー」≫「作成したリンクサーバ名」≫「カタログ」≫「default」≫「テーブル」

    SQL Server側にあるテーブルをAccessのMDBファイルにエクスポートする方法は
    リンクサーバーを用いた方法では実現できないのでしょうか。
    教えてください。よろしくお願いします。

    2011年9月27日 6:54
  • まず、最初にですが、SQL Server における SELECT ・・・ INTO ・・・ FROM ・・・ の様なSQL文は、

    ACCESS では使えません。

     

    ですので、CREATE TABLE の後に INSERT するという様にする形になると思います。

    1. リンクサーバーのプロパティで、サーバーオプションの RPC 及び RPC 出力 を True にする。

    ※これをしないと、2.における CREATE TABLE のSQL文がエラーになります。

    2. CREATE TABLE 文を発行する。

    この時、下記の様な形で実行しないとエラーになります。

    EXECUTE ('CREATE TABLE [テーブルB](・・・)') AT [リンクサーバー名]

    実際には、CREATE TABLE 文を文字列変数などを用いて連結していくと綺麗かと思います。

    ※SELECT * FROM sys.columns WHERE object_id = (SELECT object_id FROM sys.tables WHERE name = 'テーブルA') ORDER BY column_id

    上記のSQL文で列定義を取得してCREATE 文を生成すると良いと思います。

     

    DECLARE @sql VARCHAR(8000)

    @sql = ''

    @sql = @sql + 'CREATE TABLE [' + テーブル名 + ']'

    @sql = @sql + ' ('

    ・・・ここで sys.column の内容を FETCH して内容を生成する。

     

    EXECUTE (@sql) AT [リンクサーバー名]

     

    3. INSERT 文を発行する。

    INSERT INTO [リンクサーバー名]...[テーブルB] SELECT * FROM テーブルA

     

    以上で出来ると思います。

    わかりづらい部分があるかと思いますが、頑張って下さい。

    • 回答としてマーク nyandaba 2011年9月28日 6:17
    2011年9月27日 8:57
  • aviator__さん、こんにちは。
    回答をしていただき、ありがとうございます。

    早速教えていただいた手順で以下のように実行してみましたところ、
    リンクサーバーを用いた方法で、SQL Server側にあるテーブルをAccessのMDBファイルに作成することができました。

    【実行した手順】

    1. リンクサーバーのプロパティで、サーバーオプションの RPC 及び RPC 出力 を True にする。

    ここで、以下のコードを実行させ、Access側に作成するテーブルBを予め削除しました。

    SET @sql = 'DROP TALE [' + テーブルB + ']'
    EXECUTE (@sql) AT [リンクサーバー名]
    2. CREATE TABLE 文を発行する。

    EXECUTE ('CREATE TABLE [テーブルB](・・・)') AT [リンクサーバー名] 

    SELECT * FROM sys.columns WHERE object_id = (SELECT object_id FROM sys.tables WHERE name = 'テーブルA') ORDER BY column_id

    上記のSQL文で列定義を取得してCREATE 文を生成すると良いと思います。

    ここでは、列定義を以下のコードで取り出し、FETCH してCREATE TABLE 文を作成しました。

    SELECT * FROM INFORMATION_SCHEMA.COLUMNS I 
    WHERE I.TABLE_NAME = 'テーブルA'
    ORDER BY I.ORDINAL_POSITION

    3. INSERT 文を発行する。

    INSERT INTO [リンクサーバー名]...[テーブルB] SELECT * FROM テーブルA

    丁寧に教えていただいたので、とてもわかりやすかったです。
    ありがとうございました。



    • 編集済み nyandaba 2011年9月28日 6:22
    2011年9月28日 6:16