ACCESS FEED: Re: [belajar-access] Merge Data dari banyak Database

Senin, 24 September 2012

Re: [belajar-access] Merge Data dari banyak Database

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

On Sun, Sep 23, 2012 at 5:09 PM, scan smg <scan_smg@yahoo.com> wrote:

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.

Thanks sebelumnya.

--- On Sat, 9/22/12, Sumiyanto Surabaya <sumiyanto@yahoo.com> wrote:

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].Incomplete

FROM 1Form4;

dan

SELECT [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

http://cakyanto.wordpress.com/
http://mugi.or.id/blogs/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.wb

Mohon info ilmunya, bagaimana cara menggabungkan data dari all database yang ada dalam 1 folder. Contoh database terlampir. Terima kasih sebelumnya.

Wass.wr.wb