Running Multiple Queries from an ERP dbase

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

Forum statistics

Threads
1,141,757
Messages
5,708,346
Members
421,566
Latest member
7Nabisco

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 MrExcel.com.
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 "mrexcel.com".
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
Top