INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL Server Login

SQL Server Login

(OP)
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?

RE: SQL Server Login

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. www.connectionstrings.com 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 

RE: SQL Server Login

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close