Lm th? no ? truy c?p vo nhi?u c s? d? li?u trong m?t truy v?n SQL trong VB 3,0

D?ch tiu ? D?ch tiu ?
ID c?a bi: 113701
Bung t?t c? | Thu g?n t?t c?

? Trang ny

TM T?T

Khi truy c?p vo d? li?u t? nhi?u hn m?t ngu?n d? li?u cng m?t lc trong m?t truy v?n duy nh?t, b?n s? r?t c th? mu?n s? d?ng nhanh nh?t v h?u h?t ni chung phng php - cch g?n cc b?ng t? cc ngu?n d? li?u khc nhau vo m?t ?a n Microsoft Access c s? d? li?u. Vo th?i i?m , truy v?n m span thao c s? d? li?u c th? ?c xy d?ng nh th? t?t c? cc b?ng, nh km ho?c ?a phng, ?a phng ? c s? d? li?u Microsoft Access.
G?n bn c hi?u su?t m?nh m? v qu?n tr? l?i th?-- ?c bi?t l n?u b?n ang th?c hi?n truy v?n lin t?c. Tuy nhin, i khi b?n c th? mu?n i theo con ?ng ch?m hn ?c m t? trong bi vi?t ny khi b?n c?n ? s? d?ng phi th? th?c truy v?n bao g?m hai ho?c nhi?u c s? d? li?u. Bi vi?t ny gi?i thch lm th? no ? xy d?ng cc truy v?n c s? d? li?u qua ch?m hn.

THNG TIN THM

C hai phng php b?n c th? s? d?ng ? xc ?nh m?t c s? d? li?u bn ngoi l m?t l hi?n ang m?.

Phng php One

Microsoft Access SQL cung c?p m?t kho?n IN cho php b?n k?t n?i v?i m?t s? d? li?u bn ngoi (c s? d? li?u khc v?i c s? d? li?u hi?n nay). Phng php ny gi?i, tuy nhin, h?n b?n ch? c m?t c s? d? li?u bn ngoi m?t lc.

M?nh ? IN c hai ph?n, c s? d? li?u tn v k?t n?i chu?i. C s? d? li?u tn l m?t ?ng d?n ? i?u ki?n ? cc t?p tin ho?c th m?c c ch?a cc c s? d? li?u t?p tin v k?t n?i chu?i ch?a c s? d? li?u ki?u v khc cc thng s? khi c?n thi?t.

? ch? ?nh m?t c s? d? li?u bn ngoi, g?n thm m?t d?u ch?m ph?y (;) ? k?t n?i m?t ph?n, v km theo n v?i m?t ho?c hai d?u ngo?c kp. Sau y V d? s? d?ng cc kho?n IN ? ch? ?nh m?t b?ng (khch hng) trong m?t dBASE IV c s? d? li?u (bn hng):
SELECT Customers.CustomerID
FROM Customers
IN "C:\DBASE\DATA\SALES" "dBASE IV;"
WHERE Customers.CustomerID Like "A*"
				
Trong Visual Basic, b?n c th? t?o ra m?t dynaset t? v d? trn b?i b?ng cch s? d?ng Visual Basic m? sau y:
Dim db As Database
Dim ds As Dynaset
Dim sql As String

' Open a database:
Set db = OpenDatabase("C:\VB\BIBLIO.MDB")

' Build the select statement, referencing the external dBASE IV file:
sql = "SELECT Customers.CustomerID, FROM Customers"
sql = sql & " IN 'C:\DBASE\DATA\SALES' 'dBASE IV;'"
sql = sql & " WHERE Customers.CustomerID Like 'A*'"

' Create the recordset:
Set ds = db.CreateDynaset(sql)

' Loop through and display the records:
While Not ds.EOF
   For i = 0 To ds.Fields.Count - 1
      Print ds(i); "  ";
   Next i
   Print
   ds.MoveNext
Wend
				

Phng php hai

Microsoft Access c tch h?p vo Visual Basic Phin b?n 3.0 c th? phn tch c php truy v?n SQL ? bao g?m cc chu?i k?t n?i ?c s? d?ng ? m? m?t c s? d? li?u ?i t?ng. Kho?n t? cc l?nh SQL ch?p nh?n hon ton ? i?u ki?n b?ng tn, cho php cc v? tr c?a chu?i k?t n?i t?i qu?ng tr?ng khung tr?c tn b?ng. K?t n?i chu?i ?c tch ra t? cc b?ng tn b?i m?t kho?ng th?i gian. Phng php ny cho php b?n k?t n?i v?i nhi?u bn ngoi c s? d? li?u cng m?t lc.

B?n c th? truy c?p b?t k? b?ng trong m?t trong hai c s? d? li?u bn trong m?t l?a ch?n duy nh?t n?i dung b?ng cch s? d?ng c php ny:
SELECT tbl1.fld1,tbl2.fld1
FROM [odbc;dsn=datasource;database=pubs;Username= <username>;PWD=<strong password>].tbl1,
[dbase iii;database=C:\DBASE3].tbl2
WHERE tbl1.fld1 = tbl2.fld1
				
V d? ny tham gia hai bn t? hai databases khc nhau, m?t trong m?t ODBC d? li?u ngu?n v khc m?t dBASE III trong b?ng th m?c C:\DBASE3.

Ni chung, chu?i k?t n?i ?c s? d?ng ? y trong d?u ngo?c vung l gi?ng h?t nhau ? thu?c tnh k?t n?i c?a m?t TableDef khi g?n ho?c tham s? th? t tuyn b? OpenDatabase. N s? ? m?t trong ba h?nh th?c ty thu?c vo trn c s? d? li?u (ODBC, ISAM ho?c Microsoft Access).

Cho ODBC c s? d? li?u:
   [odbc;dsn=datasource;database=thedatabasename;Username=<username>;
PWD=<strong password>]
				
Cho ISAM c s? d? li?u:
   [paradox 3.X;database=C:\DATABASE\PARADOX3]
   [foxpro 2.5;database=C:\DATABASE\FOX25]
   [dbase iv;database=C:\DATABASE\DBASEIV]
   [btrieve;database=C:\DATABASE\BTRIEVE\FILE.DDF]
				
?i v?i c s? d? li?u Microsoft Access:
   [;database=C:\VB\BIBLIO.MDB]
				
Chu y d?u ch?m ph?y hng ?u cho c s? d? li?u Microsoft Access l quan tr?ng. i?u ny l chnh xc cng m?t chu?i c?n thi?t ? i?n vo cc ti s?n k?t n?i c?a m?t TableDef ?i t?ng tr?c khi g?n b?ng v?i m?t ?nh d?ng Microsoft Access c s? d? li?u. D?u ch?m ph?y hng ?u l m?t gi? ch? cho c s? d? li?u khng c?n thi?t ?nh d?ng ?c i?m k? thu?t v cho php cc "c s? d? li?u =" kho?n ? lm theo.

V d? m? c?a m?t truy v?n c s? d? li?u nhi?u

V d? ny t?o ra m?t dynaset gia nh?p hai bn t? hai d? li?u ngu?n, m?t trong nh?ng my ch? SQL v cc c s? d? li?u Microsoft Access. TestTab b?ng l ngy SQL Server v b?ng T1 l trong Microsoft Access c s? d? li?u.

Chu y B?n ph?i thay ?i <username> v <strong password=""> v?i cc gi tr? ng tr?c khi b?n ch?y m? ny. ?m b?o r?ng tn ng?i dng c quy?n thch h?p ? th?c hi?n thao tc ny trn c s? d? li?u. </strong></username>
Dim db As database
Dim ds As dynaset
Dim sql As String, Uid$, Pwd$

Set db = OpenDatabase("C:\VB\BIBLIO.MDB")
' This obtains a valid database object. It does not have to be a Microsoft
' Access database; the following works equally as well:
' Set db = OpenDatabase("C:\FOXPRO25\", 0, 0, "foxpro 2.5")

' The values here are hard-coded, but you could prompt the user for their
' user id and password.
Uid$ = <username>
Pwd$ = <strong password>

' Build the select statement, concatenating the user's id and password:
sql = "SELECT T1.F2, TestTab.F2, TestTab.F3"
sql = sql & " FROM [;database=C:\ACCESS\DB1.MDB].T1 , "
sql = sql & " [odbc;dsn=texas;database=playpen;Username=" & Uid$
sql = sql & ";PWD=" & Pwd$ & "].TestTab"
sql = sql & " WHERE T1.F1 = TestTab.F1"

' Execute the select query:
Set ds = db.CreateDynaset(sql)

' Loop through and display the records:
While Not ds.EOF
   For i = 0 To ds.Fields.Count - 1
      Print ds(i); "  ";
   Next i
   Print
   ds.MoveNext
Wend
				

?c bi?t lu ? lin quan ?n b?o v? c s? d? li?u Microsoft Access

N?u c s? d? li?u Microsoft Access b?o ?m, ?ng d?ng Visual Basic ph?i th?c thi cc l?nh SetDataAccessOption v SetDefaultWorkspace tr?c khi th?c hi?n b?t k? d? li?u truy c?p lin quan ?n m?. i?u ny l c?n thi?t cho m?t thnh cng ng nh?p v? Microsoft Access khng s? d?ng cc "tn ng?i dng =" v "PWD =" ph?n c?a chu?i k?t n?i. Vi du:
   ' Establish the location of the SYSTEM.MDA files if in another
   ' directory other than the \WINDOWS directory:
   SetDataAccessOption 1, "C:\MYDIR\MYAPP.INI"
   ' Log on to a valid account:
   SetDefaultWorkspace "admin", "<password>"
				
Khi i?u ny ?c th?c hi?n, truy v?n c s? d? li?u Microsoft Access b?o ?m s? thnh cng. Tuy nhin, lu ? v? c?a qu tr?nh ny, l c m?t built-in gi?i h?n cho k? thu?t phi th? th?c ny; ch? c m?t b?o ?m Microsoft Access c s? d? li?u c th? ?c truy c?p v?i m?t truy v?n qu?ng co hoc. i?u ny l b?i v? m?t khi cc Microsoft Access c ?c kh?i t?o trong m?t phin lm vi?c, v?i m?t ng?i dng c? th? t? h?p tn v m?t kh?u, nh?ng gi tr? ?c gi? l?i cho ?n khi phin h?p (Visual Basic chng tr?nh ch?y ?c ho?c phin h?p c?a mi tr?ng, VB.EXE) k?t thc.

Tuy nhin, n?u nhi?u hn hai b?o ?m Microsoft Access c s? d? li?u c?n ph?i truy c?p cho m?t truy v?n, phng php t?t nh?t l ? di chuy?n cc b?ng th?c t? t? b?o ?m c s? d? li?u vo m?t c s? d? li?u b?o ?m. ? th?c hi?n vi?c ny, b?n c?n ph?i thay ?i m?t kh?u cho ti kho?n qu?n tr? ? "" t?m th?i trong vi?c chuy?n giao ho?t ?ng kinh doanh. Sau b?n c th? s? d?ng Visual Basic m?, ch?ng h?n nh trong d? li?u truy c?p m?u Visdata, ? sao chp cc b?ng.

THAM KH?O

? c thm thng tin v? an ninh Microsoft Access, b?m s? bi vi?t sau ? xem bi vi?t trong c s? ki?n th?c Microsoft:
105990Thng tin: Lm th? no Visual Basic 3,0 x? l? b?o m?t thi?t l?p b?i Microsoft Access

Thu?c tnh

ID c?a bi: 113701 - L?n xem xt sau cng: 17 Thang Tam 2011 - Xem xt l?i: 2.0
T? kha:
kbhowto kbsqlprog kbmt KB113701 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:113701
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