+ Reply to Thread
Results 1 to 3 of 3

Importing from Access

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    Importing from Access

    I am importing an Access database table with the code below. During the importing process two dialog boxes (Data Link Properties & DB Initialization Information) appear. I click OK (or just hit the enter key) for both. How can I automate the "OK" so that no clicking is required? Thanks Sandy.

    Range("H2").Select
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
                                                                "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=S:\Time_Sheets-Database\TimeSheets.accde;Mode=Share Deny" _
                                                                , _
                                         " Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:E" _
                                         , _
                                         "ngine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLED" _
                                         , _
                                         "B:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale o" _
                                         , _
                                         "n Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False" _
                                         ), Destination:=Range("$H$2")).QueryTable
            .CommandType = xlCmdTable
            .CommandText = Array("tblProject")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .SourceDataFile = "S:\Time_Sheets-Database\TimeSheets.accde"
            .ListObject.DisplayName = "Table_TimeSheets.accde_15"
            .Refresh Backg

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

    Re: Importing from Access

    Why use code when the Data Ribbon offers "From Access" and Data -> Other Sources offers MS-Query? Both of these can be set using Data -> Connections to refresh automatically on open.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    Re: Importing from Access

    Thanks dflak, will give it a go. Cheers Sandy

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Access VBA to add a primary key (first field) after importing to access
    By ewong in forum Access Programming / VBA / Macros
    Replies: 5
    Last Post: 09-14-2012, 08:57 PM
  2. Generating an Excel sheet and importing it into Access with VBA triggered in Access
    By JungleJme in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-29-2012, 07:43 AM
  3. HELP PLEASE - Importing from Access
    By jwr in forum Excel General
    Replies: 4
    Last Post: 02-22-2012, 06:33 AM
  4. importing from access
    By heramiah in forum Excel General
    Replies: 4
    Last Post: 06-19-2008, 05:46 AM
  5. Importing from access
    By ajkgds in forum Excel General
    Replies: 0
    Last Post: 03-12-2008, 12:05 PM
  6. [SOLVED] Importing data from Access to Excel, but I need to vary the table from Access
    By Liz L. in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-05-2006, 09:15 PM
  7. importing data from access-database access file
    By amrezzat in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-22-2005, 04:25 PM
  8. [SOLVED] Importing from Access
    By Importing from Access in forum Excel General
    Replies: 0
    Last Post: 06-21-2005, 07:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1