Private Sub GetData_MFRating()
Dim strMyPath As String, strDBName As String, strDB As String, strMyPathDB1 As String
Dim strSQL As String
Dim Flag As Boolean
Dim ws As Worksheet
Dim i, fieldCount As Long
Dim rng1, rng2 As Range
Dim Data_Type As String
Dim UserName As String
'On Error GoTo ErrHandle
'ReportDate = ThisWorkbook.Sheets("EW Report").Range("EW_ReportDate").Value
'MsgBox Month(ReportDate)
'instantiate an ADOX Catalog object using Dim with the New keyword:
Dim adoxCat As New ADODB.Connection
Dim acc As New Access.Application
'set the Access File name - the MS Access Database Name:
Set adoRecSet = New ADODB.Recordset
strDBName = "Ten file access"
'set path / location of the database, to be in the same location as the host workbook:
strMyPath = "Duong dan den file" 'VD: C:\Data
'set the string variable to the Database:
strDB = strMyPath & "\" & strDBName
With adoxCat
.ConnectionTimeout = 0
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Data Source") = strDB
.Mode = adModeShareDenyNone
.Properties("Persist Security Info") = False
.Properties("User ID").Value = "Ten User
.Properties("Jet OLEDB:Database Password").Value = "Password"
.Open
End With
strSQL = "Cau lenh Query SQL"
adoRecSet.Open Source:=strSQL, ActiveConnection:=adoxCat, CursorType:=adOpenStatic, LockType:=adLockOptimistic
Set rng1 = ws.Range("A1")
fieldCount = adoRecSet.Fields.Count
For i = 0 To fieldCount - 1
'copy column names in first row of the worksheet:
rng1.Offset(0, i).Value = adoRecSet.Fields(i).Name
Next i
'copy record values starting from second row of the worksheet:
rng1.Offset(1, 0).CopyFromRecordset adoRecSet
'to copy 4 rows and 3 columns of the recordset to excel worksheet:
'rng.Offset(1, 0).CopyFromRecordset Data:=adoRecSet, MaxRows:=4, MaxColumns:=3
MsgBox ("Hoan thanh ! Du lieu ban can xem da duoc cap nhat ve tu CSDL")
'select a column range:
'Range(ws.Columns(1), ws.Columns(fieldCount)).EntireColumn.AutoFit
'worksheet columns are deleted because this code is only for demo:
adoRecSet.Close
'acc.OpenCurrentDatabase strDB
'adoxCat.Create ConnectString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strDB
End If
End Sub
