paulsteigel > 10-05-16, 10:49 AM
(10-05-16, 09:30 AM)Minh Tiên Đã viết: ....
Sub UpdateHH()
Dim SqlTxt As String
' Tat canh bao tu CSDL
DoCmd.SetWarnings False
' Cap nhap nhung ma hang da co trong tblDanhsach_Hanghoa
SqlTxt = "UPDATE tblDanhsach_Hanghoa AS d INNER JOIN ( " & _
"SELECT b.Mahang, a.Dongianhap, a.Dongiabanle, a.Dongiabansy, [a].[Soluongton]+[b].[Soluongnhap] AS slTon " & _
"FROM strTableNameTam AS b INNER JOIN tblDanhsach_Hanghoa AS a ON b.Mahang = a.Mahang) AS c " & _
"ON d.Mahang = c.Mahang " & _
"SET d.Soluongton = [c].[slTon], d.Dongianhap = [c].[Dongianhap], d.Dongiabanle = [c].[Dongiabanle], d.Dongiabansy = [c].[Dongiabansy];"
' O day co 2 Query, Query1_1 de lay danh sach nhung ban ghi co Mahang giong nhau giua tblDanhsach_Hanghoa strTableNameTam
' "SELECT b.Mahang, a.Dongianhap, a.Dongiabanle, a.Dongiabansy, [a].[Soluongton]+[b].[Soluongnhap] AS slTon " & _
' "FROM strTableNameTam AS b INNER JOIN tblDanhsach_Hanghoa AS a ON b.Mahang = a.Mahang
' Va Query 2 thuc hien viec UPDATE
' "UPDATE tblDanhsach_Hanghoa AS d INNER JOIN Query1_1 AS c " & _
' "ON d.Mahang = c.Mahang " & _
' "SET d.Soluongton = [c].[slTon], d.Dongianhap = [c].[Dongianhap], d.Dongiabanle = [c].[Dongiabanle], d.Dongiabansy = [c].[Dongiabansy];"
' Thuc thi lenh
CurrentDb.Execute SqlTxt
' Them vao tblDanhsach_Hanghoa nhung cai khong co trong bang nay
SqlTxt = "INSERT INTO tblDanhsach_Hanghoa ( Mahang, Tenhang, Donvitinh, Nhomhang, Nganhhang, Soluongton, Dongianhap, Dongiabanle, Dongiabansy ) " & _
"SELECT a.Mahang, a.Tenhang, a.Donvitinh, a.Nhomhang, a.Nganhhang, a.Soluongnhap, a.Dongianhap, a.Dongiabanle, a.Dongiabansy " & _
"FROM (SELECT c.Mahang, c.Tenhang, c.Donvitinh, c.Nhomhang, c.Nganhhang, c.Soluongnhap, c.Dongianhap, c.Dongiabanle, c.Dongiabansy " & _
"FROM strTableNameTam AS c LEFT JOIN tblDanhsach_Hanghoa AS b ON c.Mahang = b.Mahang " & _
"WHERE (((b.Mahang) Is Null))) AS a;"
' O day co 2 Query tuong tu nhu tren
' Querry1_2 xac dinh danh sach ban ghi trong strTableNameTam ma khong co trong tblDanhsach_Hanghoa
' "SELECT c.Mahang, c.Tenhang, c.Donvitinh, c.Nhomhang, c.Nganhhang, c.Soluongnhap, c.Dongianhap, c.Dongiabanle, c.Dongiabansy " & _
' "FROM strTableNameTam AS c LEFT JOIN tblDanhsach_Hanghoa AS b ON c.Mahang = b.Mahang " & _
' "WHERE (((b.Mahang) Is Null))
' Query thuc hien lenh chen ban ghi INSERT INTO (dat ten Query truoc la Querry1_2
' "INSERT INTO tblDanhsach_Hanghoa ( Mahang, Tenhang, Donvitinh, Nhomhang, Nganhhang, Soluongton, Dongianhap, Dongiabanle, Dongiabansy ) " & _
' "SELECT a.Mahang, a.Tenhang, a.Donvitinh, a.Nhomhang, a.Nganhhang, a.Soluongnhap, a.Dongianhap, a.Dongiabanle, a.Dongiabansy " & _
' "FROM Querry1_2 AS a;"
CurrentDb.Execute SqlTxt
DoCmd.SetWarnings True
End Subpaulsteigel > 10-05-16, 01:49 PM
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;Minh Tiên > 10-05-16, 04:08 PM
paulsteigel > 10-05-16, 04:44 PM
(10-05-16, 04:08 PM)Minh Tiên Đã viết: ....Với trường hợp của Tiên thì cách làm thế này nhé...
Option Compare Database
Option Explicit
Sub UpdateHH()
Dim SqlTxt As String, dbPath As String
' Tat canh bao tu CSDL
DoCmd.SetWarnings False
'========================================
' TIEN SUA DUONG DAN FILE DB TAM NHE...
'========================================
dbPath = "E:\Users\Paulsteigel\Desktop\GPE\QbNHNhan\tmpDb.mdb"
'========================================
If Not CreateLink("tblDanhsach_Hanghoa", dbPath, "tblDanhsach_Hanghoa") Then Exit Sub
' Cap nhap nhung ma hang da co trong tblDanhsach_Hanghoa
SqlTxt = "UPDATE tblDanhsach_Hanghoa AS d INNER JOIN ( " & _
"SELECT b.Mahang, a.Dongianhap, a.Dongiabanle, a.Dongiabansy, [a].[Soluongton]+[b].[Soluongnhap] AS slTon " & _
"FROM strTableNameTam AS b INNER JOIN tblDanhsach_Hanghoa AS a ON b.Mahang = a.Mahang) AS c " & _
"ON d.Mahang = c.Mahang " & _
"SET d.Soluongton = [c].[slTon], d.Dongianhap = [c].[Dongianhap], d.Dongiabanle = [c].[Dongiabanle], d.Dongiabansy = [c].[Dongiabansy];"
' O day co 2 Query, Query1_1 de lay danh sach nhung ban ghi co Mahang giong nhau giua tblDanhsach_Hanghoa strTableNameTam
' "SELECT b.Mahang, a.Dongianhap, a.Dongiabanle, a.Dongiabansy, [a].[Soluongton]+[b].[Soluongnhap] AS slTon " & _
' "FROM strTableNameTam AS b INNER JOIN tblDanhsach_Hanghoa AS a ON b.Mahang = a.Mahang
' Va Query 2 thuc hien viec UPDATE
' "UPDATE tblDanhsach_Hanghoa AS d INNER JOIN Query1_1 AS c " & _
' "ON d.Mahang = c.Mahang " & _
' "SET d.Soluongton = [c].[slTon], d.Dongianhap = [c].[Dongianhap], d.Dongiabanle = [c].[Dongiabanle], d.Dongiabansy = [c].[Dongiabansy];"
' Thuc thi lenh
CurrentDb.Execute SqlTxt
' Them vao tblDanhsach_Hanghoa nhung cai khong co trong bang nay
SqlTxt = "INSERT INTO tblDanhsach_Hanghoa ( Mahang, Tenhang, Donvitinh, Nhomhang, Nganhhang, Soluongton, Dongianhap, Dongiabanle, Dongiabansy ) " & _
"SELECT a.Mahang, a.Tenhang, a.Donvitinh, a.Nhomhang, a.Nganhhang, a.Soluongnhap, a.Dongianhap, a.Dongiabanle, a.Dongiabansy " & _
"FROM (SELECT c.Mahang, c.Tenhang, c.Donvitinh, c.Nhomhang, c.Nganhhang, c.Soluongnhap, c.Dongianhap, c.Dongiabanle, c.Dongiabansy " & _
"FROM strTableNameTam AS c LEFT JOIN tblDanhsach_Hanghoa AS b ON c.Mahang = b.Mahang " & _
"WHERE (((b.Mahang) Is Null))) AS a;"
' O day co 2 Query tuong tu nhu tren
' Querry1_2 xac dinh danh sach ban ghi trong strTableNameTam ma khong co trong tblDanhsach_Hanghoa
' "SELECT c.Mahang, c.Tenhang, c.Donvitinh, c.Nhomhang, c.Nganhhang, c.Soluongnhap, c.Dongianhap, c.Dongiabanle, c.Dongiabansy " & _
' "FROM strTableNameTam AS c LEFT JOIN tblDanhsach_Hanghoa AS b ON c.Mahang = b.Mahang " & _
' "WHERE (((b.Mahang) Is Null))
' Query thuc hien lenh chen ban ghi INSERT INTO (dat ten Query truoc la Querry1_2
' "INSERT INTO tblDanhsach_Hanghoa ( Mahang, Tenhang, Donvitinh, Nhomhang, Nganhhang, Soluongton, Dongianhap, Dongiabanle, Dongiabansy ) " & _
' "SELECT a.Mahang, a.Tenhang, a.Donvitinh, a.Nhomhang, a.Nganhhang, a.Soluongnhap, a.Dongianhap, a.Dongiabanle, a.Dongiabansy " & _
' "FROM Querry1_2 AS a;"
CurrentDb.Execute SqlTxt
DoCmd.SetWarnings True
End Sub
Function CreateLink(strTable As String, strPath As String, strBaseTable As String) As Boolean
On Error GoTo CreateAttachedError
Dim tdf As TableDef
Dim strConnect As String
Dim fRetval As Boolean
Dim myDB As Database
' Kiem tra xem bang co ton tai khong?
If TableExists(strTable) Then CurrentDb.TableDefs.Delete strTable
Set myDB = CurrentDb
Set tdf = myDB.CreateTableDef(strTable)
With tdf
.Connect = ";DATABASE=" & strPath
.SourceTableName = strBaseTable
End With
myDB.TableDefs.Append tdf
fRetval = True
CreateAttachedExit:
CreateLink = fRetval
Exit Function
CreateAttachedError:
If Err = 3110 Then
Resume CreateAttachedExit
Else
If Err = 3011 Then
Resume Next
End If
End If
End Function
Function TableExists(tblName As String) As Boolean
On Error GoTo ErrHandler
Dim tdf As TableDef
Set tdf = CurrentDb.TableDefs(tblName)
Set tdf = Nothing
TableExists = True
ErrHandler:
End FunctionMinh Tiên > 11-05-16, 09:08 AM
Che_Guevara > 11-05-16, 09:40 AM
paulsteigel > 11-05-16, 09:48 AM
(11-05-16, 09:08 AM)Minh Tiên Đã viết: ....
Function RemapLinks(Optional PwdString As String = "", Optional tPath As String = "") As DatabaseErrors
' Thực hiện việc tái lập liên kết bảng trong Access (với các Link đã có sẵn)
Dim tdf As Object, tblName As String
Err.Clear
On Error GoTo ErrHandler
For Each tdf In CurrentDb.TableDefs
Debug.Print tdf.Name ' & " Type:" & tdf.Type
If tdf.Connect <> "" Then
tdf.Connect = "Ms Access;UID=Admin;PWD=" & PwdString & ";DATABASE=" & tPath
tblName = tdf.Connect
tdf.RefreshLink
End If
Next
Set tdf = Nothing
Exit Function
ErrHandler:
RemapLinks = Err.Number
If Err.Number <> 0 Then WriteLog Err.Description
End FunctionMinh Tiên > 11-05-16, 03:17 PM
paulsteigel > 11-05-16, 03:34 PM
Minh Tiên > 11-05-16, 04:55 PM
...
SqlTxt = "INSERT INTO tblNhaphang_Muavao_Chitiet (Stt,Mahang,Tenhang,Donvitinh,Nhomhang,Nganhhang,Soluongnhap,"
SqlTxt = SqlTxt & "Dongianhap,Dongiabanle,Dongiabansy,Thanhtiennhap,Tienchietkhau,Hansudung,Ngaykiemke)"
SqlTxt = SqlTxt & " SELECT Stt, Mahang,Tenhang,Donvitinh,Nhomhang,Nganhhang,Soluongnhap,Dongianhap,"
SqlTxt = SqlTxt & "Dongiabanle,Dongiabansy,Thanhtiennhap,Tienchietkhau,Hansudung,Ngaykiemke FROM tblNhaphang_Muavao_Chitiet_Tam;"
CurrentDb.Execute SqlTxt