PDA

View Full Version : Prompt message box for importing access table data



blanchard306
03-24-2016, 10:53 AM
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

p45cal
03-24-2016, 01:03 PM
what was on this line before you changed it:

.CommandText = Array("NEED PROMPT HERE")
?

blanchard306
03-24-2016, 01:37 PM
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

p45cal
03-24-2016, 04:26 PM
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. ThanksCould you copy/paste the orginal line?

blanchard306
03-24-2016, 04:57 PM
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

p45cal
03-24-2016, 05:36 PM
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!

blanchard306
03-25-2016, 07:04 AM
Thank you I'm going to test it out today!!!!