EXCEL VBA で MSDE を操作したい

質問

EXCELで作っている帳票データをデータベース化するにあたり、MSDEでテーブルを作りSQLクエリを使用してEXCEL VBAプロジェクト上からテーブルの追加／更新を行いたいのですが、MSSQLServerにアクセスする方法（具体的なコードの記述法や手順）がわかりません。

昔同様な事をやったことがあり、その時はサーバーがoracle(7)で、「oracle GLUE」というアドインツールを使いましたが同様に何かツールが必要なのでしょうか。
どなたか、どうかご教授お願いいたします。

回答No.6

ARC · ベストアンサー

えっと、Access2000でデータベースを新規作成するときに
「新しいデータベース」を選べば、Access97や以前のバージョンと同じく、MDB形式のデータベースを作成できます。(MDB形式は、根本的にMSDEとは異なります)

新規作成するときに、「プロジェクト(新しいDB)」」を選択すると、MSDE(SQL-Server互換)でアクセスできるデータベースを作成できます。

database=XXXに指定する文字は、「プロジェクト(新しいDB)」を選ぶと起動するウィザードで、[新しいSQL Server データベース名を指定して下さい]の項目のところ、指定した名称を入力します。

もし、通常のMDB形式で作成したDBにアクセスするんであれば、補足してください。

それと、OSQLですが、MSDEをインストールしたときに同時にインストールされる、DOS用のSQL発行ツールです。
Oracleで云うところの「SQL*Plus」みたいなやつです。

起動方法は、コマンドプロンプト上で、
OSQL -S サーバー名 -U ユーザー名 -P パスワード …
ですね。(終了は、EXIT[ENTER])

お礼

出来ました！！

大分日にちが経ってしまいましたが、ようやく出来ました。
問題はやはり
database="xxxxxxx"
の部分。
はるか昔まで記憶を遡り、DB構築したときに使った名称を全て叩き込んで一つ一つ実行していったんです。
初期の頃は、何度もやり直したりいろいろ試したので、似たようで少しずつ違う名称をたくさん使ったので大変でしたが、ようやく見つけました。
が結局、ここでいうdatabase名というものをどこで確認できるのか解らずに「とりあえず先に進める」という状況なので、すっきりしません。
また次の機会に同じ苦労を繰り返す事になりそうで気持ち悪いですが･･･ま現在の、遅れまくった仕事が優先ですね。

全く無知も甚だしい私に付き合っていただきましてありがとうございます。m(_ _)m　本当に助かりました。
そしてまた更なる無知を披露するべくやってくると思いますのでうんざりせずに付き合ってやってください(^^;)。
それでは。

回答No.5

ARC

もしかしたら、データベース名が識別できていないのかもしれません。
strCNNに ";Database=XXXX" を書き加えると、うまく動くと思うのですが…

違ってたら、補足ください。

補足

優先の仕事があって大分時間が経ってしまいましたが...
まだ解決していません(；_；)。

";Database=XXXX" のXXXXに何を入れるのかわかりません。
以前、

>database項には、OSQL等で、
>create database HimitsuDB
>とした時の名前を記述します。

とありましたが、OSQLて何でしょう？
全く無知で申し訳ないんですが...

以前も書いたかと思うのですが、
DBはACCESS(2000)で作成しましたのでMSSQLServer
なわけですけど、"MSDE上の名前"とか"MSDEで～"とか
繰り返し見るにつけ、私がACCESSで作成しているものは
もしかしてそもそも一般的に言うMSDEとは別のものでは？と、自分が大きな勘違いをしている気がして不安になってきました。
でも、日経ソフトウェアのMSDEでDBを作成する特集記事を参考にしたんですから、そんな事は無い、とは思っているんですが...どうでしょう?

回答No.4

ARC

う～ん、何故でしょう(?o?) <#3

とりあえず、SQLに問題が無いことを確認するために、他の環境から、同じIDでログインし、同じSQLを実行してみてください。

P.S.
～8/25まで、非常に忙しくなりそうです。そのため返事等が遅くなるかもしれません。m(_ _)m

補足

お忙しいところ本当にすみません。

とりあえずACCESSでEXCEL VBAに書き込んだSQLクエリをそのままコピーして発行してみましたが問題ありませんでした。
ただ、そのACCESSでも、VBAコードで走らせると同じエラーになります。

それで考えてみたのですが、ACCESSでテーブルを作成していて、教えていただいたVBAの接続設定をdatabaseではなくDSNで行っているので、そこに問題があるのでは？
接続したDB上の、使用するテーブルを宣言しなければいけないとか...そういうことはないでしょうか。

とりあえず自分でもHELP等調べてみてはいますが、今のところ目的に合った解答は見つかっていません。
（いつも思いますが、MSのヘルプってわかりにくい...）

お時間が出来ましたらお返事頂ければ幸いです。

回答No.3

ARC

driver={SQL Server}; <- ここは固定です。
server=Onboro-Server; <- サーバー名です。(MSDE上で設定したもの)
uid=ARC; <- ログインID
pwd=Himitsu <- パスワード
database=HimitsuDB <- MSDE上のデータベースの名前(ODBCのデータソースではありません)

database項には、OSQL等で、
create database HimitsuDB
とした時の名前を記述します。

ちなみに、データソースで指定する場合は、
strCNN = "driver={SQL Server};DSN=データソース名;uid=XXX;pwd=XXX;server=XXX"
のように指定します。

>"～uid=sa;pwd=;database=～"
>と、単純に省略していますがよいのでしょうか？

よいです。

>あと、Openに接続文字列のstrCNNが絡んでませんが...
>もしかして
>"CNN.Open strCNN"
>とかなるのではないかと思ってみたのですが、違いますか？

はい、まことにその通りです(^^;;
OKWebに書き込むときに、うっかり削除してしまっていました。

補足

おかげさまでデータベースへの接続はうまくいったようです。
が、また問題が...
SQLクエリを発行すると今度はテーブル名がはじかれます。

strSQL = "SELECT name FROM Table_1;"
Set RST = New ADODB.Recordset
RST.CursorType = adOpenStatic
RST.Open strSQL, strCNN

のような記述で

"オブジェクト名'Table_1'は無効です"

と言われてしまいます。
何度もテーブル名等名称を確認しましたが間違いはありません。
何故でしょう？？？？

回答No.2

ARC

VBAのエディタから、[ツール]-[参照設定]を実行し、「Microsoft ActiveX Data Object X.x Library」にチェックをつけて下さい。

んで、次みたいなコードを書けば、SQLを発行できます。
不明点があれば、補足下さい。

Private Sub コマンド2_Click()
　　Dim CNN As ADODB.Connection
　　Dim RST As ADODB.Recordset
　　Dim CMD As ADODB.Command

　　Dim RecCt As Long
　　Dim strCNN As String
　　Dim strSQL As String

'接続文字列の準備
　　strCNN = "driver={SQL Server};" & _
　　　　"server=Onboro-Server; uid=ARC; pwd=Himitsu; database=HimitsuDB"

'SQLを実行
　　Set CNN = New Connection
　　CNN.ConnectionTimeout = 30
　　CNN.Open

　　Set CMD = New Command
　　CMD.ActiveConnection = CNN
　　CMD.CommandText = "INSERT INTO T個人 ( 氏名 )" _
　　　　　　　　　　& " SELECT '菜梨 権瓶' AS 氏名;"
　　CMD.CommandType = adCmdUnknown
　　CMD.Execute RecCt
　　MsgBox RecCt & "人追加したよ？"

'レコードセットを開く
　　strSQL = "SELECT * FROM T個人;"
　　Set RST = New ADODB.Recordset
　　RST.CursorType = adOpenStatic
　　RST.Open strSQL, strCNN
　　RST.MoveLast
　　RST.MoveFirst
　　MsgBox "最初のヒトは、" & RST![氏名] & "さん。" & vbNewLine _
　　　　& "全部で" & RST.RecordCount & "人登録されてるよ？"
End Sub

補足

ARCさん、詳細な回答ありがとうございます。
が、Openで挫けてしまいました。
「データソース名及び指定された規定のドライバが見つかりません」となって先に進めません。
そこで接続文字列の確認をしたいのですが。
コントロールパネルの「ODBCデータソースアドミニストレータ」によるところの

driver={SQL Server}; <- （ここは固定ですよね。）
server=Onboro-Server; <- サーバー名
uid=ARC; <- ログインID
pwd=Himitsu <- パスワード
database=HimitsuDB <- データソース名

で良いのでしょうか。

また、現在ログインIDは標準の"sa"にしてあり、パスワードは設定していないので、
"～uid=sa;pwd=;database=～"
と、単純に省略していますがよいのでしょうか？

ここまでに間違いが無いとしたら原因がどこにあるか思い当たる事はありますか？

あと、Openに接続文字列のstrCNNが絡んでませんが...
もしかして
"CNN.Open strCNN"
とかなるのではないかと思ってみたのですが、違いますか？

回答No.1

mnabe

MSDEの参照設定をしてあげれば出来ます。

　後は、SQLの発行を行ってOKです。

補足

え～と、ごめんなさい。良くわかりません。
「MSDEの参照設定」とは、具体的にどこにある機能の事ですか？
私の質問が悪かったですね。
SQLの発行についても、以前oracle DBでやった時は「oracle GLUE」のライブラリ関数をEXCEL VBAにストアして発行したのですが、今回の場合はどのプロシジャを使うのでしょう？
SQLクエリを素で書き込むわけではないですよね。
それにDBへの接続命令もEXCEL側で出さなければならいと思うのですが...

まさに「具体的」なコードや、設定場所がわからないんです。申し訳ありません。