Lm th? no ? l?y m?t b?ng t? truy c?p vo Excel s? d?ng DAO

D?ch tiu ? D?ch tiu ?
ID c?a bi: 146406 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?

TM T?T

Trong cc phin b?n c?a Microsoft Excel ?c li?t k ? trn, b?n c th? s? d?ng truy c?p d? li?u Cc ?i t?ng (DAO) trong Visual Basic cho cc ?ng d?ng ? l?y m?t b?ng t? Microsoft Access.

? cung c?p m?t v d? v? cch b?n c th? s? d?ng DAO ? l?y m?t b?ng t? Microsoft Access, v? m ?c m t? trong bi vi?t ny s? d?ng Northwind c s? d? li?u ?c v?n chuy?n v?i c? Microsoft Office Professional cho Windows 95, phin b?n 7.0, v Microsoft vn ph?ng 97 Professional for Windows. N?u b?n ch?n ty ch?n m?c ?nh khi b?n ci ?t Microsoft Office Chuyn nghi?p cho Windows 95, phin b?n 7.0, c s? d? li?u n?m ?:
   \MSOffice\Access\Samples\Northwind.mdb
				
N?u b?n ch?n ty ch?n m?c ?nh khi b?n ci ?t Microsoft Office 97 Chuyn nghi?p ?i v?i Windows, c s? d? li?u n?m ?:
   \Program Files\Microsoft Office\Office\Samples\Northwind.mdb
				
N?u c s? d? li?u Northwind n?m trong m?t c?p khc c?a b?n my tnh, b?n s? c?n ph?i ch?nh s?a m? ?c cung c?p d?i y tr?c khi b?n ch?y n.

S? d?ng DAO ? v? m c?a b?n, b?n ph?i tham kh?o ?i t?ng DAO Microsoft Th vi?n ho?c b?n c th? nh?n ?c bo l?i "User-defined lo?i khng xc ?nh". ? tham kh?o ny th vi?n trong Microsoft Excel Phin b?n 7.0, kch ho?t m?t m-un t?m, Click vo tham kh?o trn tr?nh n cng c?, v ki?m tra "Microsoft DAO 3,0 ?i t?ng th vi?n"ty ch?n. ? tham kh?o ny th vi?n trong Microsoft Excel Phin b?n 97, nh?p tham kh?o vo tr?nh n Tools trong Visual Basic Editor v ki?m tra cc "Microsoft DAO 3.5 ?i t?ng Library".

THNG TIN THM

Microsoft cung c?p l?p tr?nh v d? ? minh ho? ch?, khng c b?o hnh ho?c th? hi?n hay ng? ?. i?u ny bao g?m, nhng khng gi?i h?n, b?o ?m ng? ? kh? nng bn hng ho?c cho m?t m?c ch c? th?. Bi vi?t ny gi? ?nh r?ng b?n ? quen thu?c v?i ngn ng? l?p tr?nh m ang ?c ch?ng minh v v?i nh?ng cng c? ?c s? d?ng ? t?o ra v g? l?i th? t?c. Microsoft h? tr? cc k? s c th? gip gi?i thch cc ch?c nng c?a m?t th? t?c c? th?, nhng h? s? khng s?a ?i cc v d? ? cung c?p thm ch?c nng ho?c xy d?ng quy tr?nh ? p ?ng cc yu c?u c? th? c?a b?n. ? l?y m?t b?ng t? Microsoft Access, h?y lm theo cc b?c sau:

  1. Thi?t l?p m?t ?i t?ng c s? d? li?u.
  2. Thi?t l?p m?t ?i t?ng Recordset.
  3. L?y cc tiu ? (n?u mu?n).
  4. Truy xu?t d? li?u t? cc b?ng.
Sau khi d? li?u ?c l?y ra, b?n nn ng t?t c? cc ?i t?ng m b?n ? m? b?i pht hnh.Close l?nh.

? l?y m?t b?ng t? Microsoft Access l?p tr?nh, s? d?ng cc Theo Visual Basic cho cc ?ng d?ng m?:
Sub GetTable()
'This sub will retrieve all the data in the "Customers" table in
'Northwind

   'Declare variables
   Dim Db As Database
   Dim Rs As Recordset
   Dim Ws As Object
   Dim i As Integer
   Dim Path as String

   'This line will define the Object "Ws" as Sheets("Sheet1")
   'The purpose of this is to save typing Sheets("Sheet1")
   'over and over again
   Set Ws = Sheets("Sheet1")

   'Set the Path to the database. This line is useful because
   'if your database is in another location, you just need to change
   'it here and the Path Variable will be used throughout the code
   Path = "c:\msoffice\access\samples\northwind.mdb"

   'This set of code will activate Sheet1 and clear any existing data
   'After clearing the data it will select cell A1
   Ws.Activate
   Range("A1").Activate
   Selection.CurrentRegion.Select
   Selection.ClearContents
   Range("A1").Select

   'Set the Database, and RecordSet  This Table exists in the database
   Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:=True)

   'This will set the RecordSet to all records in the Customers table
   Set Rs = Db.OpenRecordset("Customers")

   'You could instead set the RecordSet to, for example, the records
   'where the Country Code is "UK", without quotes. To do this, replace
   'the line above: Set Rs = Db.OpenRecordset("Customers") with the
   'following:
   '
   'Set Rs = _
   'Db.OpenRecordset("SELECT * FROM Customers WHERE Country = 'UK';")


   'This loop will collect the field names and place them in the first
   'row starting at "A1"
   For i = 0 To Rs.Fields.Count - 1
      Ws.Cells(1, i + 1).Value = Rs.Fields(i).Name
   Next I

   'The next line simply formats the headers to bold font
   Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, Rs.Fields.Count)).Font.Bold=True

   'The next line will get the data from the recordset and copy it
   'into the Worksheet (Sheet1).
   Ws.Range("A2").CopyFromRecordset Rs

   'This next code set will just select the data region and
   'auto-fit the columns
   Sheets("Sheet1").Select
   Range("A1").Select
   Selection.CurrentRegion.Select
   Selection.Columns.AutoFit
   Range("A1").Select

   Rs.Close
   Db.Close
End Sub
				

THAM KH?O

? bi?t thm chi ti?t v? truy c?p d? li?u, nh?p vo Index tab Microsoft Excel gip, nh?p vn b?n sau y
truy c?p d? li?u trong DAO
v sau b?m p chu?t vo cc vn b?n ? ch?n ? i ?n cc "truy c?p ngoi C s? d? li?u v?i DAO"ch? ?.

Thu?c tnh

ID c?a bi: 146406 - L?n xem xt sau cng: 18 Thang Tam 2011 - Xem xt l?i: 2.0
p d?ng
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
T? kha:
kbdtacode kbhowto kbinterop kbprogramming kbmt KB146406 KbMtvi
My d?ch
QUAN TRONG: Bi vi?t ny ?c d?ch b?ng ph?n m?m d?ch my c?a Microsoft ch? khng ph?i do con ng?i d?ch. Microsoft cung c?p cc bi vi?t do con ng?i d?ch v c? cc bi vi?t do my d?ch ? b?n c th? truy c?p vo t?t c? cc bi vi?t trong C s? Ki?n th?c c?a chng ti b?ng ngn ng? c?a b?n. Tuy nhin, bi vi?t do my d?ch khng ph?i lc no c?ng hon h?o. Lo?i bi vi?t ny c th? ch?a cc sai st v? t? v?ng, c php ho?c ng? php, gi?ng nh m?t ng?i n?c ngoi c th? m?c sai st khi ni ngn ng? c?a b?n. Microsoft khng ch?u trch nhi?m v? b?t k? s? thi?u chnh xc, sai st ho?c thi?t h?i no do vi?c d?ch sai n?i dung ho?c do ho?t ?ng s? d?ng c?a khch hng gy ra. Microsoft c?ng th?ng xuyn c?p nh?t ph?n m?m d?ch my ny.
Nh?p chu?t vo y ? xem b?n ti?ng Anh c?a bi vi?t ny:146406
Khc t Ni dung trong C s Kin thc Khng con c h tr
Bi vi?t ny ni v? cc s?n ph?m m Microsoft khng c?n h? tr? n?a. Do , bi vi?t ny ?c cung c?p "nguyn b?n" v s? khng ?c c?p nh?t.

Cung cp Phan hi

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com