Cách kết nối Excel với cơ sở dữ liệu Access bằng VBA

Posted on: 2020年11月12日 2020年11月13日
Tags: import-and-export-in-vba

Cơ sở dữ liệu Access là một hệ quản trị cơ sở dữ liệu quan hệ có hiệu quả lưu một lượng lớn dữ liệu một cách có tổ chức. Trong đó Excel là một công cụ mạnh mẽ để thu thập dữ liệu thành thông tin có ý nghĩa. Tuy nhiên, Excel không thể lưu trữ quá nhiều dữ liệu. Nhưng khi chúng ta sử dụng Excel và Access cùng nhau, sức mạnh của những công cụ này sẽ tăng lên theo cấp số nhân. Vì vậy, chúng ta hãy tìm hiểu cách kết nối cơ sở dữ liệu Access dưới dạng nguồn dữ liệu với Excel thông qua VBA.

Kết nối Cơ sở dữ liệu Access dưới dạng Nguồn Dữ liệu Excel

1: Thêm tham chiếu vào đối tượng dữ liệu AcitveX

Chúng tôi sẽ sử dụng ADO để kết nối để truy cập cơ sở dữ liệu. Vì vậy, đầu tiên chúng ta cần thêm tham chiếu đến đối tượng ADO.

Thêm một mô-đun vào dự án VBA của bạn và nhấp vào các công cụ. Ở đây bấm vào các tài liệu tham khảo.

Bây giờ hãy tìm Thư viện đối tượng dữ liệu ActiveX của Microsoft. Kiểm tra phiên bản mới nhất mà bạn có. Tôi có 6,1. Nhấp vào nút OK và nó đã được thực hiện. Bây giờ chúng ta đã sẵn sàng để tạo một liên kết đến Cơ sở dữ liệu Access.

2. Viết mã VBA để thiết lập kết nối đến cơ sở dữ liệu truy cập

Để kết nối Excel với cơ sở dữ liệu Access, bạn cần có cơ sở dữ liệu Access. Tên cơ sở dữ liệu của tôi là “Test Database.accdb”. Nó được lưu tại vị trí “C: \ Users \ Manish Singh \ Desktop”. Hai biến này rất quan trọng. Bạn sẽ cần phải thay đổi chúng theo nhu cầu của bạn. Mã phần còn lại có thể được giữ nguyên.

Sao chép mã bên dưới để tạo mô-đun VBA trong Excel của bạn và thực hiện các thay đổi theo yêu cầu của bạn. Tôi đã giải thích từng dòng mã dưới đây:

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

Sao chép mã trên hoặc tải xuống tệp bên dưới và thực hiện các thay đổi đối với tệp cho phù hợp với yêu cầu của bạn.

Khi bạn chạy mã VBA này, Excel sẽ thiết lập kết nối với cơ sở dữ liệu. Sau đó, nó sẽ chạy truy vấn đã thiết kế. Nó sẽ xóa mọi nội dung cũ trên trang tính và sẽ điền vào cột A với các giá trị của Trường 1 (trường thứ hai) của cơ sở dữ liệu.

Kết nối cơ sở dữ liệu truy cập VBA này hoạt động như thế nào?

Dim conn As New Connection, rec As New Recordset

Trong dòng trên, chúng ta không chỉ khai báo các biến Connection và recordet mà còn khởi tạo trực tiếp nó bằng cách sử dụng từ khóa New.

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

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

Hai dòng này là thí sinh. DBPATH sẽ chỉ thay đổi với cơ sở dữ liệu của bạn. PRVD đang kết nối nhà cung cấp OLE DB.

conn.Open connString

Dòng này mở kết nối với cơ sở dữ liệu. Mở là chức năng của đối tượng kết nối nhận một số đối số. Đối số đầu tiên và cần thiết là ConnectingString. Chuỗi này chứa nhà cung cấp OLE DB (ở đây là PRVD) và nguồn dữ liệu (ở đây là DBPATH). Nó cũng có thể lấy quản trị viên và mật khẩu làm đối số tùy chọn cho cơ sở dữ liệu được bảo vệ.

Cú pháp của Connection.Open là:

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

Vì tôi không có bất kỳ ID và Mật khẩu nào trên cơ sở dữ liệu của mình, tôi chỉ sử dụng ConnectionString. Định dạng của ConnectionString là “Nhà cung cấp = provider_bạn muốn sử dụng; _ Nguồn dữ liệu = _ tên đủ điều kiện của cơ sở dữ liệu“. Chúng tôi đã tạo và lưu biến chuỗi này.

query = "SELECT * from customerT;"

Đây là truy vấn tôi muốn chạy trên cơ sở dữ liệu. Bạn có thể có bất kỳ truy vấn nào bạn muốn.

rec.Open query, conn

Câu lệnh này chạy truy vấn đã xác định trong kết nối đã xác định. Ở đây chúng tôi đang sử dụng phương thức Mở của đối tượng tập bản ghi. Tất cả đầu ra được lưu trong đối tượng tập bản ghi. Bạn có thể truy xuất các giá trị thao tác hoặc xóa khỏi đối tượng tập bản ghi.

Cells.ClearContents

Dòng này xóa nội dung của trang tính. Nói cách khác, xóa mọi thứ khỏi các ô của trang tính.

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

Tập hợp các dòng trên kiểm tra xem tập bản ghi có trống hay không. Nếu tập bản ghi không trống (có nghĩa là truy vấn trả về một số bản ghi) thì vòng lặp bắt đầu và bắt đầu in từng giá trị của trường 1 (trường thứ hai, tên đầu tiên trong trường hợp này) trong // ô-dãy-hàng-và-cột-trong- vba / 3-best-way-to-find-last-non-blank-row-and-column-using-vba.html [ô không sử dụng cuối cùng trong cột] `.

(Điều này được sử dụng chỉ cần giải thích. Bạn có thể không có những dòng này. Nếu bạn chỉ muốn mở một kết nối đến cơ sở dữ liệu thì mã VBA ở trên những dòng này là đủ.)

Chúng tôi đã sử dụng rec.EOF để chạy vòng lặp cho đến khi kết thúc tập ghi. Rec.MoveNext được sử dụng để chuyển sang tập bản ghi tiếp theo. rec.Fields (1) được sử dụng để lấy giá trị từ trường 1 (là trường thứ hai vì lập chỉ mục trường của nó bắt đầu từ 0. Trong cơ sở dữ liệu của tôi, trường thứ hai là Tên của khách hàng).

rec.Close

conn.Close

Cuối cùng, khi tất cả công việc chúng tôi muốn từ rec và conn đã hoàn thành, chúng tôi đóng chúng.

Bạn có thể có những dòng này trong chương trình con riêng biệt nếu bạn muốn mở và đóng các kết nối cụ thể một cách riêng biệt.

Vì vậy, các bạn, đây là cách bạn thiết lập kết nối với cơ sở dữ liệu ACCESS bằng ADO. Ngoài ra còn có các phương pháp khác, nhưng đây là cách dễ nhất để kết nối với nguồn truy cập dữ liệu thông qua VBA. Tôi đã giải thích nó càng chi tiết càng tốt. Hãy cho tôi biết nếu điều này hữu ích trong phần bình luận bên dưới.

Bài viết liên quan:

===

Bài viết phổ biến:

Chức năng Countif là cần thiết để chuẩn bị bảng điều khiển của bạn.