AccessとExcelの連携について

質問

アクセス初心者です。
私は小さなお店の事務を最近任されました。
次のような事をやりたいのですが、どのような手順をやればできるでしょうか。

これまでエクセルで見積書や請求書を作成していて、顧客データも別シートで作ってそこからひっぱってきてました。
それを今回、顧客管理をアクセスを使いデータベースを作り、例えばエクセルの見積書作成時に顧客を入力する際は、アクセスのデータのコードを入力して顧客情報をエクセルに反映していきたいのです。（名前や担当者など）
最初は、見積などですが、今後色々増えていくとは思います。

手順やどんな機能を使えばうまくいくのでしょうか。
とりあえずアクセスのデータを作ってみましたが、連携方法はVBAでやるのでしょうか？
前任者が辞めてしまいわかる人がいません。
A ccess 2013です。

よろしくお願い致します。

回答No.7

山田 太郎（@f_a_007） · ベストアンサー

【バグ報告】

On Error GoTo Err_DBSelect
　　Dim cnn As Object 'ADOコネクションオブジェクト
　　Dim rst As Object 'ADOレコードセットオブジェクト
　　Dim fld As Object 'ADOフィールドオブジェクト
　　Dim strList　　 As String　' 全てのデータを区切子で連結して格納

Dim文はこれだけでした。

回答No.6

山田 太郎（@f_a_007）

【追記】DBSelect()をチョイ改善しました！

で、結果的に CutStr() が不要になりました。が、添付図のように、CutStr()でDBSelect()の戻り値を切り出すことで、簡単に顧客の名前、郵便番号、住所、あるいは担当をExcelに表示することができます。

添付図の場合、B3にDBSELECT()を仕組んでいますので、読みを変更すれば、自動的に表の値は更新されます。

Public Function DBSelect(ByVal strDB As String, _
　　　　　　　　　　　　 ByVal strSQL As String, _
　　　　　　　　　　　　 Optional colDelimita As String = ";", _
　　　　　　　　　　　　 Optional rowDelimita As String = ";") As String
On Error GoTo Err_DBSelect
　　Dim cnn As Object 'ADOコネクションオブジェクト
　　Dim rst As Object 'ADOレコードセットオブジェクト
　　Dim fld As Object 'ADOフィールドオブジェクト
　　Dim strWhere　　As String
　　Dim strList　　 As String　' 全てのデータを区切子で連結して格納
　　Dim strCountSQL As String

　　' ---------------
　　'　Set
　　' ---------------
　　Set cnn = CreateObject("ADODB.Connection")
　　Set rst = CreateObject("ADODB.Recordset")
　　'
　　' データベース 　オープン
　　'
　　cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDB & ";"
　　'adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDB
　　' ----------------------------------------
　　'　レコードセット　オープン
　　' ----------------------------------------
　　With rst
　　　　.Open strSQL, cnn
　　　　If Not .BOF Then
　　　　　　.MoveFirst
　　　　　　Do
　　　　　　　　For Each fld In .Fields
　　　　　　　　　　With fld
　　　　　　　　　　　　strList = strList & .Value & colDelimita
　　　　　　　　　　End With
　　　　　　　　Next fld
　　　　　　　　strList = Mid(strList, 1, Len(strList) - 1) & rowDelimita
　　　　　　　　　　 .MoveNext
　　　　　　Loop Until (.EOF)
　　　　Else
　　　　　　strList = ""
　　　　End If
　　End With
Exit_DBSelect:
On Error Resume Next
　　rst.Close
　　Set rst = Nothing
　　DBSelect = IIf(Len(strList) > 0, Replace(strList & "[END]", rowDelimita & "[END]", ""), "")
　　Exit Function
Err_DBSelect:
　　MsgBox Err.Description
　　Resume Exit_DBSelect
End Function

回答No.5

山田 太郎（@f_a_007）

【補足】EXCELにAccessの複数レコードの列を読み込むには？

　Excelに”読み”に対応した顧客.名前を読み込む関数を用意するだけでは、全てには対応できません。なぜなら、顧客情報の全てを確認のために参照・表示することもあるからです。ですから、それを可能にする関数=DBSelect()も必要です。DBLookup()とDBSelect()があれば、質問者の目的は達成されると思います。なお、ADODBやOLEの参照するようにExcelで設定する必要があるかも知れませんが、それは検索して対処されて下さい。

　まあ、Excel の VBA を書くのは今日が初めて。ですから、洗練という点ではもう１００歩かもしれません。そこんところは、質問者で更に改善されて下さい。

Public Function DBSelect(ByVal strDB As String, _
　　　　　　　　　　　　 ByVal strSQL As String, _
　　　　　　　　　　　　 Optional colDelimita As String = ";", _
　　　　　　　　　　　　 Optional rowDelimita As String = ";") As String
On Error GoTo Err_DBSelect
　　Dim R　　　　　 As Integer ' 行インデックス
　　Dim N
　　Dim cnn As Object 'ADOコネクションオブジェクト
　　Dim rst As Object 'ADOレコードセットオブジェクト
　　Dim fld As Object 'ADOフィールドオブジェクト
　　Dim strWhere　　As String
　　Dim strList　　 As String　' 全てのデータを区切子で連結して格納
　　Dim strCountSQL As String

　　' ---------------
　　'　Set
　　' ---------------
　　Set cnn = CreateObject("ADODB.Connection")
　　Set rst = CreateObject("ADODB.Recordset")
　　'
　　' データベース 　オープン
　　'
　　cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDB & ";"
　　'adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDB
　　' ----------------------------------------
　　'　レコードセット　オープン
　　' ----------------------------------------
　　With rst
　　　　.Open strSQL, cnn
　　　　If Not .BOF Then
　　　　　　strCountSQL = "SELECT Count(*) FROM " & _
　　　　　　　　　　　　　CutStr(UCase(strSQL), "FROM", 2)
　　　　　　N = DBLookup(strDB, strCountSQL) - 1
　　　　　　.MoveFirst
　　　　　　For R = 0 To N
　　　　　　　　For Each fld In .Fields
　　　　　　　　　　With fld
　　　　　　　　　　　　strList = strList & .Value & colDelimita
　　　　　　　　　　End With
　　　　　　　　Next fld
　　　　　　　　strList = Mid(strList, 1, Len(strList) - 1) & rowDelimita
　　　　　　　　If Not .EOF Then
　　　　　　　　　　 .MoveNext
　　　　　　　　End If
　　　　　　Next R
　　　　Else
　　　　　　strList = ""
　　　　End If
　　End With
Exit_DBSelect:
On Error Resume Next
　　rst.Close
　　Set rst = Nothing
　　DBSelect = IIf(Len(strList) > 0, Replace(strList & "[END]", rowDelimita & "[END]", ""), "")
　　Exit Function
Err_DBSelect:
　　MsgBox Err.Description
　　Resume Exit_DBSelect
End Function

Public Function CutStr(ByVal Text As String, _
　　　　　　　　　　　　ByVal Separator As String, _
　　　　　　　　　　　　ByVal N As Integer) As String
　　Dim strDatas() As String

　　strDatas = Split("" & Separator & Text, Separator, , 0)
　　CutStr = strDatas(N * Abs(N <= UBound(strDatas)))
End Function

回答No.4

山田 太郎（@f_a_007）

【補足】DBLookup()にエラートラップを追加！

Public Function DBLookup(ByVal strDB As String, ByVal StrSQL As String) As String
On Error GoTo err_DBLookup:
　　Dim cnn As Object 'ADOコネクションオブジェクト
　　Dim rst As Object 'ADOレコードセットオブジェクト

　　' ---------------
　　'　Set
　　' ---------------
　　Set cnn = CreateObject("ADODB.Connection")
　　Set rst = CreateObject("ADODB.Recordset")
　　'
　　' データベース 　オープン
　　'
　　cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDB & ";"
　　'adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDB
　　' ----------------------------------------
　　'　レコードセット　オープン
　　' ----------------------------------------
　　With rst
　　　　.Open StrSQL, cnn
　　　　If Not .BOF Then
　　　　　　.MoveFirst
　　　　　　DBLookup = .Fields(0)
　　　　Else
　　　　　　DBLookup = ""
　　　　End If
　　End With

　　' ---------------
　　'　終了処理
　　' ---------------
　　rst.Close
　　cnn.Close
　　Set rst = Nothing
　　Set cnn = Nothing
Exit_CbLookup:
　　Exit Function
err_DBLookup:
　　MsgBox Err.Description
　　Resume Exit_CbLookup
End Function

回答No.3

山田 太郎（@f_a_007）

【補足】Excel で Access の[顧客台帳]を参照するには・・・

　DBlookup()、DBSelect()などの関数を自作すると簡単に参照できます。添付図では、”読み”で[顧客台帳].[名前]を参照しています。後、エラートラップを書くと完成します。

　同様に、Accessから複数レコードの複数列を参照する関数 DBSelect()も作成できます。なお、参照できるってことは、書き込みもできるってことです。

　こういう関数を利用すれば、Excel上だけで作業が完結すると思います。が、全てをAccessで開発するのが基本でしょう。Excelは作表ソフトに過ぎません。販売管理アプリケーションは、Accessで開発すべきです。

Option Explicit

Public Function DBLookup(ByVal strDB As String, ByVal StrSQL As String) As Variant
　　Dim cnn As Object 'ADOコネクションオブジェクト
　　Dim rst As Object 'ADOレコードセットオブジェクト

　　' ---------------
　　'　Set
　　' ---------------
　　Set cnn = CreateObject("ADODB.Connection")
　　Set rst = CreateObject("ADODB.Recordset")
　　'
　　' データベース 　オープン
　　'
　　cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDB & ";"
　　'adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDB
　　' ----------------------------------------
　　'　レコードセット　オープン
　　' ----------------------------------------
　　With rst
　　　　.Open StrSQL, cnn
　　　　If Not .BOF Then
　　　　　　.MoveFirst
　　　　　　DBLookup = .Fields(0)
　　　　Else
　　　　　　DBLookup = ""
　　　　End If
　　End With
　　' ---------------
　　'　終了処理
　　' ---------------
　　rst.Close
　　cnn.Close
　　Set rst = Nothing
　　Set cnn = Nothing
End Function

回答No.2

chie65536（@chie65535）

＞連携方法はVBAでやるのでしょうか？

アクセスでは、エクセルのシートをリンクして「テーブル」のように操作する事が出来ます。

あとは「普通にテーブルのように操作」すれば、エクセルのシートに反映されます。

お礼

ありがとうございます。

補足

エクセルの見積書や請求書などに、アクセスのテーブルから名前などの一部だけを引っ張ってきたいのですが、リンクさせたら全部きてしまいました。

回答No.1

山田 太郎（@f_a_007）

Q、どのような手順をやればできるでしょうか？
A、次のような手順を踏むとOK。

１、Accessのオンラインヘルプを完全に習得する。
２、SQLに関する文献を一冊は読破する。
３、リレーショナルデータベース構築に関する文献を一冊は読破する。
４、出来れば、「プログラミング言語C2」の例題を解く。

　書店に並んでいる参考書なんて２時間もあれば読み通せる程度の薄っぺらい内容。あんなの３冊、５冊読んでもAccessでアプリケーションを開発できる技量は身に付きません。頼りは、オンラインヘルプです。私は、あれを全て印刷して半年かけて３回演習。で、やっとこさ、Access での開発する手順とイメージを得ることが出来ました。

　Access に挑む前には、UNIXでクライアント・サーバーシステムを開発していました。ですから、SQL文とかデータベースの設計は完全な初心者と言う訳ではありませんでした。でも、Accessの習得には、一日12時間の独習を半年。

　VBAは、C言語とVB6を習得していましたが、それでも一日12時間の独習を半年。Accessには、Access のライブラリの開発要領、記述要領がありましたのでかなり苦労しましたよ。

　VBAを習得するには、まずは、「プログラミング言語C 2」の例題を解くのが一番です。別に、やっかいなC言語に挑戦する必要はありません。JavaScript で書けばOKです。

<script type="text/javascript">
　　<!--
　　//
　　// Javascript: f=0,20,・・・300に対して、摂氏-華氏対応表を出力する
　　//
　　var lower = 0;　　 /* 温度表の下限 */
　　var upper = 300;　 /* 上限 */
　　var step = 20;　　 /* きざみ */

　　fahr = lower;
　　document.writeln("<pre>");
　　while ( fahr <= upper) {
　　　　celsius = (fahr - 32) * 5/9;
　　　　document.write(fahr + "\t" + celsius + "\n");
　　　　fahr = fahr + step;
　　}
　　document.write("</pre>");
　　//-->
</script>

　もしくは、BASIC言語の高校生向けの教科書を手にいれて独習するかです。

　少なくとも、Access でアプリケーションを開発するのであれば、最低でも１～４の独習は必須だと思いますよ。

　頑張られてください。

お礼

ありがとうございます、
開発などやったことない私にはハードル高いですね。
事務をやりながら、傍でやるほどの時間はないかも…