Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Server Login

Status
Not open for further replies.
G12Consult

G12Consult

Programmer
May 12, 2016
77
AU
I have the following code to refresh a query table in excel using vba.

However, I keep receiving a SQL Sever Login box even though I have specified a username and password?

Code: 
Private Function RefreshDataTab()

    'Update status bar information
    Application.StatusBar = "Refreshing Data Tab"
    
              
                    
            Dim sConn As String
            sConn = "ODBC;DRIVER=SQL Server;SERVER=xxxx;UID=xxx;Password=xxxx;Trusted_Connection=no"
            
          
    'Build select query
                             
            strSQL = strSQL & "SELECT"
            strSQL = strSQL & vbLf
            strSQL = strSQL & " WD.MATTER_CODE"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",CASE WHEN M.SECURITY_ID<>0 THEN 'Restricted Matter' ELSE WD.MATTER_NAME END AS MATTER_NAME"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",WD.CLIENT_CODE"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",WD.CLIENT_NAME"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",WD.RESPONSIBLE_NAME"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",WD.BILLING_EMP_NAME"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",SUM(WD.[CURRENT]) AS 'CURRENT'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",SUM(WD.[30-59_DAYS]) AS '30 DAYS'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",SUM(WD.[60-89_DAYS]) AS '60 DAYS'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",SUM(WD.[90-179_DAYS]) AS '90 DAYS'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",SUM(WD.[180+_DAYS]) AS '180 DAYS'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",SUM(WD.TOTAL) AS 'TOTAL'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",'' AS 'CURRENT PROVISION'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",'' AS 'DOUBTFUL'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",'' AS 'WRITE OFF'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",'' AS 'WRITE OFF APPROVAL'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",'' AS 'DATE PAYMENT EXPECTED BY'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",CASE WHEN SUM(WIP.[CURRENT]) IS NULL THEN '' ELSE SUM(WIP.[CURRENT]) END AS 'WIP CURRENT'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",CASE WHEN SUM(WIP.[30-59_DAYS]) IS NULL THEN '' ELSE SUM(WIP.[30-59_DAYS]) END AS 'WIP 30 DAYS'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",CASE WHEN SUM(WIP.[60-89_DAYS]) IS NULL THEN '' ELSE SUM(WIP.[60-89_DAYS]) END AS 'WIP 60 DAYS'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",CASE WHEN SUM(WIP.[90-179_DAYS]) IS NULL THEN '' ELSE SUM(WIP.[90-179_DAYS]) END AS 'WIP 90 DAYS'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",CASE WHEN SUM(WIP.[180+_DAYS]) IS NULL THEN '' ELSE SUM(WIP.[180+_DAYS]) END AS 'WIP 180 DAYS'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",CASE WHEN SUM(WIP.[TOTAL_WIP_VALUE]) IS NULL THEN '' ELSE SUM(WIP.[TOTAL_WIP_VALUE]) END AS 'WIP TOTAL'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",'' AS 'WIP BILL DATE'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",'' AS 'WIP COLLECTIBLE'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",'' AS 'WIP DOUBTFUL'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",CASE WHEN SUM(DISB.[CURRENT]) IS NULL THEN '' ELSE SUM(DISB.[CURRENT]) END AS 'DISB CURRENT'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",CASE WHEN SUM(DISB.[30-59_DAYS]) IS NULL THEN '' ELSE SUM(DISB.[30-59_DAYS]) END AS 'DISB 30 DAYS'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",CASE WHEN SUM(DISB.[60-89_DAYS]) IS NULL THEN '' ELSE SUM(DISB.[60-89_DAYS]) END AS 'DISB 60 DAYS'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",CASE WHEN SUM(DISB.[90-179_DAYS]) IS NULL THEN '' ELSE SUM(DISB.[90-179_DAYS]) END AS 'DISB 90 DAYS'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",CASE WHEN SUM(DISB.[180+_DAYS]) IS NULL THEN '' ELSE SUM(DISB.[180+_DAYS]) END AS 'DISB 180 DAYS'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",CASE WHEN SUM(DISB.[Total_DISBS]) IS NULL THEN '' ELSE SUM(DISB.[Total_DISBS]) END AS 'DISB TOTAL'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",'' AS 'DISB BILL DATE'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",'' AS 'DISB COLLECTIBLE'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",'' AS 'DISB DOUBTFUL'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",'' AS 'DISB WRITE OFF'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",'' AS 'NOTES'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "FROM CMSIntranet.dbo.AgedDebtDetail WD"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "INNER JOIN CMSOPEN.DBO.HBM_MATTER M ON WD.MATTER_CODE=M.MATTER_CODE"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "LEFT JOIN  "
            strSQL = strSQL & vbLf
            strSQL = strSQL & " ("
            strSQL = strSQL & vbLf
            strSQL = strSQL & "  SELECT *"
            strSQL = strSQL & "   FROM ("
            strSQL = strSQL & vbLf
            strSQL = strSQL & "         SELECT  MATTER_CODE,"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "                 SUM([CURRENT]) AS 'CURRENT',"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "                 SUM([30-59_DAYS]) AS '30-59_DAYS',"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "                 SUM([60-89_DAYS]) AS '60-89_DAYS',"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "                 SUM([90-179_DAYS]) AS '90-179_DAYS',"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "                 SUM([180+_DAYS]) AS '180+_DAYS',"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "                 SUM([TOTAL_WIP_VALUE]) AS 'TOTAL_WIP_VALUE'"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "              FROM CMSIntranet.dbo.AgedWIPDetail"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "              WHERE TRAN_DATE <= DATEADD(day, -90, '" & Format([FROMDATE], "yyyymmdd") & "')"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "              GROUP BY MATTER_CODE"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "        ) WIP"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "        )"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "        WIP on WIP.MATTER_CODE = WD.MATTER_CODE"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "      LEFT JOIN CMSIntranet.dbo.MATTER_AGED_DISBS_DETAIL DISB ON WD.MATTER_CODE=DISB.MATTER_CODE"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "INNER JOIN ("
            strSQL = strSQL & vbLf
            strSQL = strSQL & "              SELECT *"
            strSQL = strSQL & "              FROM ("
            strSQL = strSQL & vbLf
            strSQL = strSQL & "                        SELECT  MATTER_UNO,"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "                        TRAN_DATE,"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "                        ROW_NUMBER() OVER(PARTITION BY MATTER_UNO ORDER BY TRAN_DATE DESC) as RN"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "                        FROM CMSOPEN.DBO.TAT_TIME ) TIM"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "              WHERE TIM.rn = 1 )"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "              TIM on TIM.MATTER_UNO = M.MATTER_UNO and TIM.rn = 1"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "GROUP BY"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "WD.CLIENT_CODE"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",WD.CLIENT_NAME"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",WD.MATTER_CODE"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",CASE WHEN M.SECURITY_ID<>0 THEN 'Restricted Matter' ELSE WD.MATTER_NAME END"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",WD.RESPONSIBLE_CODE"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",WD.RESPONSIBLE_NAME"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",WD.BILLING_EMP_NAME"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",WD.BU_NAME"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",WD.SORT"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",WD.SUB_UNIT_CODE"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",WD.SUB_UNIT_NAME"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",TIM.TRAN_DATE"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "HAVING"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "(SUM(WD.[90-179_DAYS])>0) OR (SUM(WD.[180+_DAYS])>0) OR (SUM(WIP.[90-179_DAYS])>0) OR (SUM(WIP.[180+_DAYS])>0) OR"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "(SUM(DISB.[90-179_DAYS])>0) OR (SUM(DISB.[180+_DAYS])>0)"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "ORDER BY"
            strSQL = strSQL & vbLf
            strSQL = strSQL & "WD.RESPONSIBLE_NAME"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",WD.MATTER_CODE"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",WD.CLIENT_NAME"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",SUM(WD.[90-179_Days]) +SUM(WD.[180+_Days]) DESC"
            strSQL = strSQL & vbLf
            strSQL = strSQL & ",SUM(WD.TOTAL) DESC"
            
         
            
            With Worksheets("Data").ListObjects(1).QueryTable
           
                .Connection = sConn
                .Sql = strSQL
                .Refresh BackgroundQuery:=False
                
            End With
        
        Application.StatusBar = False

Error_Handler:     MsgBox Err.Description
End Function

Could the connection be put into a const so I can use it multiple times?
 
Sort by date Sort by votes
Yes you can put the connection string as a constant.

You'll probably have to play with the connection string to find the settings/options that do not trigger the login box to appear. has a great reference for determining the different options to use. You might try adding in:
Code: 
Persist Security Info=True;

As an aside - you can shorten the height visual of your code like this:
Code: 
strSQL = strSQL & "SELECT" & vbLf
strSQL = strSQL & " WD.MATTER_CODE" & vbLf
 
Upvote 0 Downvote
You know, if you need to keep this in VBA, one thing you could do is import a one time query into Excel first, then grab the connection string info from it. Change what you need, and/or compare against what you have. Could be one option is missing or slightly off.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JasonEnsor
  • Locked
  • Question
Using ado/sql with excel vba
Replies
3
Views
116
JasonEnsor
JasonEnsor
lameid
  • Locked
  • Question
Outlook: Add User Property when Item added - reliably with exchange host server?
Replies
0
Views
37
lameid
lameid
Cpreston
  • Locked
  • Question
Compare Column In Excel to a Column in SQL table 2
2 3
Replies
48
Views
296
Andrzejek
Andrzejek
SnayJ
  • Locked
  • Question
Sql query array question 1
Replies
9
Views
54
SkipVought
S
Sparky1157
  • Locked
  • Question
Excel 2013 macro working erratically in AppSphere (VDI) platform running Server 2008
Replies
9
Views
90
Sparky1157
Sparky1157

Part and Inventory Search

Sponsor

Back
Top