Running Multiple Queries from an ERP dbase


Well-known Member
Aug 31, 2005

I am running into some difficulty with queries from an ERP database.

I have a workbook where each sheet has a specific query table from the same database. On the main sheet I have a bunch of buttons, each linked to its own query table. When a button is pressed the following routine is called upon to refresh the specific query.


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ProcessButton()
    <SPAN style="color:#007F00">'This subroutine is called everytime an audit button is clicked and the corresponding query is performed</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    Range("C" & ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row).Select <SPAN style="color:#007F00">'Triggers hyperlink located in Column C of Row where the button is located</SPAN>
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=<SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#007F00">'Activates Hyperlink to appropriate sheet</SPAN>
    Range("A2").Select <SPAN style="color:#007F00">'Activates a cell within query in order to refresh the query</SPAN>
    Selection.QueryTable.Refresh BackgroundQuery:=<SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#007F00">'refresh query</SPAN>
    <SPAN style="color:#00007F">Call</SPAN> ButtonHome <SPAN style="color:#007F00">'goes back to home page</SPAN>
    Range("D" & ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row).Select <SPAN style="color:#007F00">'activates formula cell in main sheet to return desired results</SPAN>
    <SPAN style="color:#007F00">'Displays result in large red font</SPAN>
    <SPAN style="color:#00007F">With</SPAN> Selection.Font
        .Name = "Arial"
        .Size = 20
        .Strikethrough = <SPAN style="color:#00007F">False</SPAN>
        .Superscript = <SPAN style="color:#00007F">False</SPAN>
        .Subscript = <SPAN style="color:#00007F">False</SPAN>
        .OutlineFont = <SPAN style="color:#00007F">False</SPAN>
        .Shadow = <SPAN style="color:#00007F">False</SPAN>
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 3
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    Range("F" & ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row).Select <SPAN style="color:#007F00">'activates time stamp cell</SPAN>
    Selection.Font.ColorIndex = 1 <SPAN style="color:#007F00">'sets font to normal</SPAN>
    Range("F" & ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row) = <SPAN style="color:#00007F">Date</SPAN> & " " & Time <SPAN style="color:#007F00">'date and time stamps the query</SPAN>
    Range("D" & ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row).Select <SPAN style="color:#007F00">'pauses on results cell</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


The only problem is that for each query, I get asked for a username and password to connect to the database.

I want to avoid that by getting the user to input that information at the beginning and then referencing that info when each query is run.

Can anyone suggest anything?

Here is the code I've got so far for getting the inputs from the user for one query. I just can't figure out how to make it apply to any and all queries.


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> GetAllQueries()

<SPAN style="color:#00007F">Dim</SPAN> User <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, Password <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, database <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>

database = InputBox("Database?", "Database")
ODBC = database & "_ODBC"
User = InputBox("User ID?", "UserId")
Password = InputBox("Password", "Password")

    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=" & ODBC & ";UID=" & User & ";Pwd=" & Password & ";DBQ=" & database & ";DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccess" _
        ), Array("ful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;")), Destination:=Range( _
        .CommandText = Array( _
        "SELECT PART.ID, PART.FABRICATED" & Chr(13) & "" & Chr(10) & "FROM SYSADM.PART PART" & Chr(13) & "" & Chr(10) & "WHERE (PART.FABRICATED='Y') AND (PART.QTY_ON_HAND>100)" _
        .Name = "Query from" & " &ODBC& "
        .FieldNames = <SPAN style="color:#00007F">True</SPAN>
        .RowNumbers = <SPAN style="color:#00007F">False</SPAN>
        .FillAdjacentFormulas = <SPAN style="color:#00007F">False</SPAN>
        .PreserveFormatting = <SPAN style="color:#00007F">True</SPAN>
        .RefreshOnFileOpen = <SPAN style="color:#00007F">False</SPAN>
        .BackgroundQuery = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#007F00">'.RefreshStyle = xlInsertDeleteCells</SPAN>
        .RefreshStyle = xlDeleteCells
        .SavePassword = <SPAN style="color:#00007F">False</SPAN>
        .SaveData = <SPAN style="color:#00007F">True</SPAN>
        .AdjustColumnWidth = <SPAN style="color:#00007F">True</SPAN>
        .RefreshPeriod = 0
        .PreserveColumnInfo = <SPAN style="color:#00007F">True</SPAN>
        .Refresh BackgroundQuery:=<SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Thanks again.

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Perhaps this cannot be done?

Anybody have suggestions?

Mainly I want to enter password once at the beginning and bipass entering it everytime I press another button to access the database again.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back