Wow... belum ketemu saya file database yang mencapai ratusan dilink jadi satu.
Ini sekedar ide saja, yaitu refresh link-nya pakai VBA.
Caranya:
- buat table yang mengandung field: nama table yang akan dilink, nama database asalnya
- dari vba, refresh link berdasarkan informasi dari table tsb di atas.
Dim dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Set dbs = CurrentDb
Set Tdfs = dbs.TableDefs
For Each Tdf In Tdfs
If ((Tdf.Attributes And dbSystemObject) = 0) And (Tdf.Connect <> vbNullString) And Not (Tdf.Name Like "~*") Then
NewPathname = dlookup("NamaDatabase","tbl_DaftarTable","Namatable='" & Tdf.Name & "'")
Tdf.Connect = ";DATABASE=" & NewPathname 'Set the new source
Tdf.RefreshLink 'Refresh the link
End If
Next 'Goto next table
Set Tdfs = Nothing
Set dbs = Nothing
Saya belum coba script ini, silahkan dicoba sendiri.
HAER
Makasih Pak infonya. Masalahnya, dalam satu folder nama database bisa berubah2 dan jumlahnya bisa ratusan database. Kalo harus link satu persatu kelamaan ya, bisa tidak kalo link ke semua database yg ada di folder tsb secara otomatis. Karena tiap week akan create folder dan dalam folder tsb akan otomatis create database dengan nama yang berbeda-beda.
From: Sumiyanto Surabaya <sumiyanto@yahoo.com>
Subject: Re: [belajar-access] Merge Data dari banyak Database [1 Attachment]
To: "belajar-access@yahoogroups.com" <belajar-access@yahoogroups.com>
Date: Saturday, September 22, 2012, 6:34 PM
Bisa di coba Pertama buat database baru, kemudian link table arahkan ke data-1, pilih table 1form dan 2form, ulangi sampai ke data-2,3,4 sehingga terbentuk link table ke 4 database tersebut.kedua buat query union all;
SELECT [1Form1].ID, [1Form1].HHCODE, [1Form1].SEQNO, [1Form1].Pago, [1Form1].ITEMCODE, [1Form1].QUANTITY, [1Form1].UNITPRICE, [1Form1].TOTALPAID, [1Form1].PROMO, [1Form1].REMARKS, [1Form1].CategoryCode, [1Form1].Incomplete
FROM 1Form1;
union all SELECT [1Form2].ID, [1Form2].HHCODE, [1Form2].SEQNO, [1Form2].Pago, [1Form2].ITEMCODE, [1Form2].QUANTITY, [1Form2].UNITPRICE, [1Form2].TOTALPAID, [1Form2].PROMO, [1Form2].REMARKS, [1Form2].CategoryCode, [1Form2].Incomplete
FROM 1Form2;
union all SELECT [1Form3].ID, [1Form3].HHCODE, [1Form3].SEQNO, [1Form3].Pago, [1Form3].ITEMCODE, [1Form3].QUANTITY, [1Form3].UNITPRICE, [1Form3].TOTALPAID, [1Form3].PROMO, [1Form3].REMARKS, [1Form3].CategoryCode, [1Form3].Incomplete
FROM 1Form3;
UNION ALL SELECT [1Form4].ID, [1Form4].HHCODE, [1Form4].SEQNO, [1Form4].Pago, [1Form4].ITEMCODE, [1Form4].QUANTITY, [1Form4].UNITPRICE, [1Form4].TOTALPAID, [1Form4].PROMO, [1Form4].REMARKS, [1Form4].CategoryCode, [1Form4].IncompleteFROM 1Form4;danSELECT [2Form1].Filenama, [2Form1].PAGo, [2Form1].HHCODE, [2Form1].SEQNO, [2Form1].PRIMARYBUYER, [2Form1].PURCHASEDATE, [2Form1].PURCHASETIME, [2Form1].STORETYPE, [2Form1].STORENAME, [2Form1].ACV, [2Form1].COMPANION1, [2Form1].COMPANION2, [2Form1].COMPANION3, [2Form1].COMPANION4, [2Form1].COMPANION5, [2Form1].COMPANION6, [2Form1].COMPANION7, [2Form1].COMPANION8, [2Form1].COMPANION9, [2Form1].COMPANION10, [2Form1].REMARKS, [2Form1].IDENTRY, [2Form1].TypeOfPurchase, [2Form1].TotalNoOfTrip1, [2Form1].entrydate, [2Form1].statushh, [2Form1].TotalNoOfTrip2, [2Form1].Fol
FROM 2Form1;
UNION ALL SELECT [2Form2].Filenama, [2Form2].PAGo, [2Form2].HHCODE, [2Form2].SEQNO, [2Form2].PRIMARYBUYER, [2Form2].PURCHASEDATE, [2Form2].PURCHASETIME, [2Form2].STORETYPE, [2Form2].STORENAME, [2Form2].ACV, [2Form2].COMPANION1, [2Form2].COMPANION2, [2Form2].COMPANION3, [2Form2].COMPANION4, [2Form2].COMPANION5, [2Form2].COMPANION6, [2Form2].COMPANION7, [2Form2].COMPANION8, [2Form2].COMPANION9, [2Form2].COMPANION10, [2Form2].REMARKS, [2Form2].IDENTRY, [2Form2].TypeOfPurchase, [2Form2].TotalNoOfTrip1, [2Form2].entrydate, [2Form2].statushh, [2Form2].TotalNoOfTrip2, [2Form2].Fol
FROM 2Form2;
UNION ALL SELECT [2Form3].Filenama, [2Form3].PAGo, [2Form3].HHCODE, [2Form3].SEQNO, [2Form3].PRIMARYBUYER, [2Form3].PURCHASEDATE, [2Form3].PURCHASETIME, [2Form3].STORETYPE, [2Form3].STORENAME, [2Form3].ACV, [2Form3].COMPANION1, [2Form3].COMPANION2, [2Form3].COMPANION3, [2Form3].COMPANION4, [2Form3].COMPANION5, [2Form3].COMPANION6, [2Form3].COMPANION7, [2Form3].COMPANION8, [2Form3].COMPANION9, [2Form3].COMPANION10, [2Form3].REMARKS, [2Form3].IDENTRY, [2Form3].TypeOfPurchase, [2Form3].TotalNoOfTrip1, [2Form3].entrydate, [2Form3].statushh, [2Form3].TotalNoOfTrip2, [2Form3].Fol
FROM 2Form3;
UNION ALL SELECT [2Form4].Filenama, [2Form4].PAGo, [2Form4].HHCODE, [2Form4].SEQNO, [2Form4].PRIMARYBUYER, [2Form4].PURCHASEDATE, [2Form4].PURCHASETIME, [2Form4].STORETYPE, [2Form4].STORENAME, [2Form4].ACV, [2Form4].COMPANION1, [2Form4].COMPANION2, [2Form4].COMPANION4, [2Form4].COMPANION4, [2Form4].COMPANION5, [2Form4].COMPANION6, [2Form4].COMPANION7, [2Form4].COMPANION8, [2Form4].COMPANION9, [2Form4].COMPANION10, [2Form4].REMARKS, [2Form4].IDENTRY, [2Form4].TypeOfPurchase, [2Form4].TotalNoOfTrip1, [2Form4].entrydate, [2Form4].statushh, [2Form4].TotalNoOfTrip2, [2Form4].Fol
FROM 2Form4;atau lihat attachment. queryUnion1 dan queryUnion2.Thank you | Terima Kasih | Matur Suksema
Sumiyanto
From: scan smg <scan_smg@yahoo.com>
To: belajar-access@yahoogroups.com
Sent: Saturday, 22 September 2012 2:09 PM
Subject: [belajar-access] Merge Data dari banyak Database [1 Attachment]
Ass.wr.wbMohon info ilmunya, bagaimana cara menggabungkan data dari all database yang ada dalam 1 folder. Contoh database terlampir. Terima kasih sebelumnya.Wass.wr.wb
--
RumahAccess Indonesia
Tidak ada komentar:
Posting Komentar