Consulting

Results 1 to 7 of 7

Thread: Prompt message box for importing access table data

  1. #1

    Prompt message box for importing access table data

    I have VBA that I use for importing Access queries into an Excel file but I am having trouble with making it prompt the user for the table name. Below is my code. Thanks

    Application.DisplayAlerts = False
    
    
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
            "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=\\Prdhilfs03\l&i-sales&mkt\WORKAREA\Agencyservices\Accou" _
            , _
            "nting\AGY\Databases\DatabaseAudit.mdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:" _
            , _
            "Registry Path="""";Jet OLEDB: Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB: Database Locking Mode=0;Jet OLEDB:Global Parti" _
            , _
            "al 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 Without Replica Repair=False;Jet OL" _
            , _
            "EDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False" _
            ), Destination:=Range("$A$1")).QueryTable
            .CommandType = xlCmdTable
            .CommandText = Array("NEED PROMPT HERE")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = False
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .SourceDataFile = _
            "\\Prdhilfs03\l&i-sales&mkt\WORKAREA\services\Accounting\AGY\Databases\DatabaseAudit.mdb"
            .ListObject.DisplayName = "Table_DatabaseAudit"
            .Refresh BackgroundQuery:=False
        End With
     Application.DisplayAlerts = True
    Last edited by SamT; 03-24-2016 at 11:10 AM.

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,777
    what was on this line before you changed it:
    .CommandText = Array("NEED PROMPT HERE")
    ?
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    It was the table name to be pulled from that database. The table name may change from week to week so this is why i'm trying to get it to prompt for the table name to pull. Thanks

  4. #4
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,777
    Quote Originally Posted by blanchard306 View Post
    It was the table name to be pulled from that database. The table name may change from week to week so this is why i'm trying to get it to prompt for the table name to pull. Thanks
    Could you copy/paste the orginal line?
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    This is the full code unaltered. The line of code where I'm hoping to get a prompt instead of a hard set table is .CommandText = Array("01a_FinalTable")



    Application.DisplayAlerts = False


    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
    "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=\\Prdhilfs03\l&i-sales&mkt\WORKAREA\Agencyservices\Accou" _
    , _
    "nting\AGY\Databases\DatabaseAudit.mdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:" _
    , _
    "Registry Path="""";Jet OLEDB: Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB: Database Locking Mode=0;Jet OLEDB:Global Parti" _
    , _
    "al 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 Without Replica Repair=False;Jet OL" _
    , _
    "EDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False" _
    ), Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdTable
    .CommandText = Array("01a_FinalTable")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = False
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .SourceDataFile = _
    "\\Prdhilfs03\l&i-sales&mkt\WORKAREA\services\Accounting\AGY\Databases\DatabaseAudit.mdb"
    .ListObject.DisplayName = "Table_DatabaseAudit"
    .Refresh BackgroundQuery:=False
    End With
    Application.DisplayAlerts = True

  6. #6
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,777
    If the user knows the table name then test:
    Static TName As String
    TName = InputBox("Enter table name", "Access Table name", TName)
    Application.DisplayAlerts = False
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array("OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=\\Prdhilfs03\l&i-sales&mkt\WORKAREA\Agencyservices\Accou", "nting\AGY\Databases\DatabaseAudit.mdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:", "Registry Path="""";Jet OLEDB: Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB: Database Locking Mode=0;Jet OLEDB:Global Parti", "al 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 Without Replica Repair=False;Jet OL", "EDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False"), Destination:=Range("$A$1")).QueryTable
      .CommandType = xlCmdTable
      .CommandText = TName
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = False
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .PreserveColumnInfo = True
      .SourceDataFile = "\\Prdhilfs03\l&i-sales&mkt\WORKAREA\services\Accounting\AGY\Databases\DatabaseAudit.mdb"
      .ListObject.DisplayName = "Table_DatabaseAudit"
      .Refresh BackgroundQuery:=False
    End With
    Application.DisplayAlerts = True
    Additional/Changed code in red:

    Static TName As String 'conventionally placed at top of sub.
    TName = InputBox("Enter table name", "Access Table name", TName)
    <snip>
    .CommandType = xlCmdTable
    .CommandText = TName
    .RowNumbers = False
    <snip>

    If however, the user doesn't know what the table name might be, do you?

    ps. Quite surprised by brevity of that .CommandText line, although .CommandType = xlCmdTable tends to explain it!
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    Thank you I'm going to test it out today!!!!

Tags for this Thread

Posting Permissions

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