Accessデータベースは、大量のデータを整理された方法で効果的に保存するリレーショナルデータベース管理システムです。 Excelは、データを意味のある情報に分解するための強力なツールです。ただし、Excelはあまり多くのデータを保存できません。しかし、ExcelとAccessを一緒に使用すると、これらのツールの能力は飛躍的に向上します。それでは、AccessデータベースをデータソースとしてVBAを介してExcelに接続する方法を学びましょう。

image

データソースExcelとしてAccessデータベースを接続する

1：AcitveXデータオブジェクトへの参照を追加

ADOを使用して接続し、データベースにアクセスします。したがって、最初にADOオブジェクトへの参照を追加する必要があります。

モジュールをVBAプロジェクトに追加し、ツールをクリックします。ここで参照をクリックします。

image

次に、MicrosoftActiveXデータオブジェクトライブラリを探します。お持ちの最新バージョンを確認してください。私は6.1を持っています。 [OK]ボタンをクリックすると完了です。これで、Accessデータベースへのリンクを作成する準備が整いました。

2.Accessデータベースへの接続を確立するためのVBAコードを記述します

ExcelをAccessデータベースに接続するには、Accessデータベースが必要です。私のデータベースの名前は「TestDatabase.accdb」です。 「C：\ Users \ ManishSingh \ Desktop」の場所に保存されます。これらの2つの変数は重要です。必要に応じて変更する必要があります。残りのコードはそのままにしておくことができます。

以下のコードをコピーしてExcelVBAモジュールを作成し、要件に応じて変更を加えます。以下のコードの各行について説明しました：

Sub ADO_Connection()

'Creating objects of Connection and Recordset

Dim conn As New Connection, rec As New Recordset

Dim DBPATH, PRVD, connString, query As String

'Declaring fully qualified name of database. Change it with your database's location and name.

DBPATH = "C:\Users\ExcelTip\Desktop\Test Database.accdb"

'This is the connection provider. Remember this for your interview.

PRVD = "Microsoft.ace.OLEDB.12.0;"

'This is the connection string that you will require when opening the the connection.

connString = "Provider=" & PRVD & "Data Source=" & DBPATH

'opening the connection

conn.Open connString

'the query I want to run on the database.

query = "SELECT * from customerT;"

'running the query on the open connection. It will get all the data in the rec object.

rec.Open query, conn

'clearing the content of the cells

Cells.ClearContents

'getting data from the recordset if any and printing it in column A of excel sheet.

If (rec.RecordCount <> 0) Then

Do While Not rec.EOF

Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = _

rec.Fields(1).Value

rec.MoveNext

Loop

End If

'closing the connections

rec.Close

conn.Close

End Sub

上記のコードをコピーするか、以下のファイルをダウンロードして、要件に合わせてファイルを変更してください。

`link：/wp-content-uploads-2020-02-VBA-Database-Learning.xls [__ダウンロードファイル：VBAデータベース学習]

このVBAコードを実行すると、Excelはデータベースへの接続を確立します。その後、設計されたクエリを実行します。シート上の古いコンテンツをすべてクリアし、列Aにデータベースのフィールド1（2番目のフィールド）の値を入力します。

このVBAAccessデータベース接続はどのように機能しますか？

Dim conn As New Connection, rec As New Recordset

上記の行では、Connection変数とrecordset変数を宣言するだけでなく、Newキーワードを使用して直接初期化しています。

DBPATH = "C:\Users\ExcelTip\Desktop\Test Database.accdb"

PRVD = "Microsoft.ace.OLEDB.12.0;"

これらの2行は出場者です。 DBPATHは、データベースによってのみ変更されます。 PRVDはOLEDBプロバイダーに接続しています。

conn.Open connString

この行は、データベースへの接続を開きます。 Openは、いくつかの引数を取る接続オブジェクトの機能です。最初の必要な引数はConnectingStringです。この文字列には、OLE DBプロバイダー（ここではPRVD）とデータソース（ここではDBPATH）が含まれています。また、保護されたデータベースのオプションの引数としてadminとpasswordを使用することもできます。

Connection.Openの構文は次のとおりです。

connection.open ([ConnectionString as String], [UserID as String], [Password as String], [Options as Long=-1])

データベースにIDとパスワードがないため、ConnectionStringのみを使用します。 ConnectionStringの形式は、「Provider = provider_you want to use; _ Data Source = _fullqualified name ofdatabase」です。この文字列inconnString変数を作成して保存しました。

query = "SELECT * from customerT;"

これは、データベースで実行したいクエリです。必要なクエリを実行できます。

rec.Open query, conn

このステートメントは、定義された接続で定義されたクエリを実行します。ここでは、recordsetオブジェクトのOpenメソッドを使用しています。すべての出力はレコードセットobjectrecに保存されます。レコードセットオブジェクトから操作値または削除値を取得できます。

Cells.ClearContents

この行は、シートの内容をクリアします。つまり、シートのセルからすべてを削除します。

If (rec.RecordCount <> 0) Then

Do While Not rec.EOF

Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = _

rec.Fields(1).Value

rec.MoveNext

Loop

End If

上記の一連の行は、レコードセットが空かどうかをチェックします。レコードセットが空でない場合（クエリがいくつかのレコードを返したことを意味します）、ループが開始され、// cells-ranges-rows-and-columns-in-のフィールド1（2番目のフィールド、この場合は名）の各値の出力が開始されます。 vba / 3-best-ways-to-find-last-non-blank-row-and-column-using-vba.html [列の最後の未使用セル] `。

（これは説明のためだけに使用されます。これらの行がない場合があります。データベースへの接続を開きたいだけの場合は、これらの行の上のVBAコードで十分です。）

レコードセットの最後までループを実行するためにrec.EOFを使用しました。 rec.MoveNextは、次のレコードセットにステップアップするために使用されます。 rec.Fields（1）は、フィールド1から値を取得するために使用されます（フィールドのインデックス作成は0から始まるため、2番目です。私のデータベースでは、2番目のフィールドは顧客の名です）。

rec.Close

conn.Close

最後に、recとconnに必要なすべての作業が完了したら、それらを閉じます。

特定の接続を個別に開いたり閉じたりする場合は、これらの行を個別のサブルーチンに含めることができます。

そうですね、これはADOを使用してACCESSデータベースへの接続を確立する方法です。他の方法もありますが、これはVBAを介してアクセスのデータソースに接続する最も簡単な方法です。できる限り詳しく説明しました。以下のコメントセクションで、これが役に立ったかどうか教えてください。

関連記事：

`link：/ import-and-export-in-vba-use-a-closed-workbook-as-a-database-dao-using-vba-in-microsoft-excel [閉じたワークブックをデータベースとして使用（DAO） MicrosoftExcelでVBAを使用する] `|閉じたブックをDAO接続のデータベースとして使用するには、ExcelでこのVBAスニペットを使用します。

`link：/ import-and-export-in-vba-use-a-closed-workbook-as-a-database-ado-using-vba-in-microsoft-excel [閉じたワークブックをデータベースとして使用（ADO） MicrosoftExcelでVBAを使用する] `|閉じたブックをADO接続のデータベースとして使用するには、ExcelでこのVBAスニペットを使用します。

link：/ applications-word-outlook-in-vba-getting-started-with-excel-vba-userforms [Excel VBAUserForms入門] | *データベースにデータを挿入するには、フォームを使用します。 Excelユーザーフォームは、ユーザーから情報を取得するのに役立ちます。 VBAユーザーフォームから始める方法は次のとおりです。

===

`link：/ user-forms-input-boxes-in-vba-change-the-valuecontent-of-several-userform-controls-using-vba-in-microsoft-excel [いくつかのUserForm-controlsの値/コンテンツを変更しますExcelでVBAを使用する] `| *ユーザーフォームコントロールのコンテンツを変更するには、この単純なVBAスニペットを使用します。

`link：/ user-forms-input-boxes-in-vba-prevent-a-userform-from-closeing-when-the-user-clicks-the-x-button-using-vba-in-microsoft-excel [ ExcelでVBAを使用して、ユーザーがxボタンをクリックしたときにユーザーフォームが閉じないようにします] `|ユーザーがフォームのxボタンをクリックしたときにユーザーフォームが閉じないようにするために、UserForm_QueryCloseイベントを使用します。

人気の記事：

link：/ keyboard-formula-shortcuts-50-excel-shortcuts-to-increase-your-productivity [生産性を高めるための50のExcelショートカット] |あなたの仕事をより速くしてください。これらの50のショートカットにより、Excelでの作業がさらに高速になります。

link：/ forms-and-functions-introduction-of-vlookup-function [ExcelのVLOOKUP関数] |これは、さまざまな範囲やシートから値を検索するために使用される、Excelの最も使用され人気のある関数の1つです。

link：/ Tips-countif-in-microsoft-excel [COUNTIF in Excel 2016] |この驚くべき関数を使用して、条件付きの値をカウントします。特定の値をカウントするためにデータをフィルタリングする必要はありません。

ダッシュボードを準備するには、Countif関数が不可欠です。

link：/ excel-formula-and-function-excel-sumif-function [ExcelでSUMIF関数を使用する方法] |これは、ダッシュボードのもう1つの重要な機能です。これは、特定の条件で値を合計するのに役立ちます。