如何在excel環境下 載入access裡面的資料

請問

如果我要在excel 使用click控制項物件

我按下去之後 可以抓到而且列出在access資料表裡面的資料

這樣的vba 要如何寫

感謝

Update:

能不能再說詳細一點, 感謝

1 Answer

Rating
  • 2 decades ago
    Favorite Answer

    請參閱"桂思強著ACCESS2000實戰問答集"9-6如何在OFFICE軟體之VBA內取用MDB檔之紀錄?

    有詳盡解說

    資料庫D:\test.mdb

    Sub click()

    With ActiveSheet.QueryTables.Add(Connection:=Array( _

    "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=D:\test.mdb;Mode=ReadWrite;Extended Properties="""";Jet OLE" _

    , _

    "DB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locki" _

    , _

    "ng Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:" _

    , _

    "Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Wit" _

    , "hout Replica Repair=False;Jet OLEDB:SFP=False"), Destination:=Range("A1"))

    .CommandType = xlCmdTable

    .CommandText = Array("Sheet1")

    .Name = "test_1"

    .FieldNames = True

    .RowNumbers = False

    .FillAdjacentFormulas = False

    .PreserveFormatting = True

    .RefreshOnFileOpen = False

    .BackgroundQuery = True

    .RefreshStyle = xlInsertDeleteCells

    .SavePassword = False

    .SaveData = True

    .AdjustColumnWidth = True

    .RefreshPeriod = 0

    .PreserveColumnInfo = True

    .SourceDataFile = "D:\test.mdb"

    .Refresh BackgroundQuery:=False

    End With

    End Sub

    Source(s): ACCESS2000實戰問答集
Still have questions? Get your answers by asking now.