Running Multiple Queries from an ERP dbase

[NBVC]

Hi,

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( _
        "A1"))
        .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.

 

[NBVC]

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.