locked
Connection String @ runtime RRS feed

  • Question

  • hi,

    I'm looking to use Access Pass-Through queries as a Record Source for reports. There's no Where clause in query.
    Now in Docmd.OpenReport Where condition, using the query above, I want to search for records only for specific date.
    I've created this query in order to avoid passing SQL from VBA code to keep it clean. Back-end is SQL Server.

    Query runs fine if i run the report without Where condition, but displays nothing as soon as I put in this condition.

    I'm pasting below a part from procedure calling the function, and function itself. What do I need to do please?

    Thanks,
    K

    ---------------Part of calling SP

        'Call PTConnStr("qryEmp_w/o_WP")
        
        If IsNull(Me.cbxDate) Then
            DoCmd.OpenReport "rptEmp_w/o_WP", acViewReport
        Else
            DoCmd.OpenReport "rptEmp_w/o_WP", acViewReport, "DateComparison = '" & Me.cbxDate & "'"
        End If

    -------------Function

    Function PTConnStr(queryName As String)

        Dim qdef As QueryDef
        
        Set qdef = CurrentDb.QueryDefs(queryName)
        
        qdef.Connect = TempVars("ConnectionString").Value
        qdef.ReturnsRecords = True
        qdef.Close
        
        'DoCmd.OpenQuery queryName

    End Function


    • Edited by KhurramKZ Saturday, February 4, 2017 1:43 PM
    Saturday, February 4, 2017 1:41 PM

Answers

  • Actually, when you re-link you don’t need (nor want) to delete the table links. If your code deletes the links, then such code becomes more complex without benefit.

    You just need to loop to change existing links, and THEN do the same for Pass though query.

    Eg this:

    Public Function SqlReLink(strCon As String)
      
       Dim dbCurrent           As DAO.Database
       Dim tdfcurrent          As DAO.TableDef
       Dim qryPass             As DAO.QueryDef

       Set dbCurrent = CurrentDb

       For Each tdfcurrent In dbCurrent.TableDefs
          If Len(tdfcurrent.connect) > 0 Then
             If Left$(tdfcurrent.connect, 5) = "ODBC;" Then
                tdfcurrent.connect = strCon
                tdfcurrent.RefreshLink
             End If
          End If
       Next tdfcurrent
       dbCurrent.TableDefs.Refresh
      
       ' link pass though
       For Each qryPass In dbCurrent.QueryDefs
          If Len(qryPass.connect) > 0 Then
             If Left$(qryPass.connect, 5) = "ODBC;" Then
                qryPass.connect = strCon
             End If
          End If
       Next qryPass
      
    End Sub


    So just add to your required and existing table re-link the near “same” code that re-links PT query(s). That way you can launch reports, forms etc. and NOT have to set the connection string each time. If you have to set connection strings each time for every report  etc., then things can get rather messy. As noted, since you HAVE to re-link the tables, then use the above approach. (and it save coding to delete the table links and include pt re-link).

    I also suggest you don’t include the Userid/password in those connection strings – it not required. Right before you re-link, you execute a logon – once you do that, then you can re-link all tables without including the UID/Password. And if you allow “different” users, then you don’t even have to re-link at all! (and this includes your PT queries!)

    How to make tables work without including the UID/password is outlined here:

    Power Tip: Improve the security of database connections

    http://blogs.office.com/b/microsoft-access/archive/2011/04/08/power-tip-improve-the-security-of-database-connections.aspx

    >>Ok, now i've tried to run the query as provided by you with all the formatting, but it still displays nothing as in first case. Where should I head for now please?

    This is a perfect example as to why we don’t want to mess with connection strings in code. We here trying to solve an issue and problem that should not exist.

    The first question is after setting the connection string and you code fails, then the query “does” have the correct connection string set, and should work.

    In other words, after the report does not work, close all code, forms, reports etc. Now simply click on the PT query – does it work? If a simple click to open the PT query does not work, then the report not going to work.

    So FIRST:
    Ensure that you can double click on the PT query. Get that working.

    Next, try running the report based in that PT query by simply clicking on that report. If the PT query works, then the report should work.

    Now, once and ONLY after the above two are working, then comment out the connection change call in your code and try running your code that sets and passes the “where” clause.

    As you can well see, if the PT connection query is “already” set, then it VERY easy to test the PT query is working without any code. And then next step of simply launching the report is ALSO again very easy to test and again occurs without any code. Note how easy the debugging process here is - we not even testing nor running code at this point.

    Once the PT query works, and the report works, then and ONLY then we can start testing our code.

    So does the PT query work when you double click on it? (even if your code failed – it would have set the connection string – so from that point you should be able to exit all your code and simply try + test the PT query by clicking on it. If the PT query works, close that and then next try a double click on your report – does it work?

    If the above two don’t work, then it is a wild goose chase to start playing with your VBA code that not working. You ONLY want to start looking at and testing your code to set the “where” clause when you are 100% sure the PT query is working and then ensure that the report works.

    If both the PT query and report works, but you code STILL fails, then I would modify the code to set a strWhere clause as per my example. You then right before the OpenReport place a debug.print of the strWhere value to see what the string looks like.

    It is possible that the datetime from SQL server is a datetime2 data type – and that will appear as a string in Access, and fail. To test this theory, open any linked table from Access to that table the PT query is based on in DESIGN MODE!!! (you have to answer “yes” to the read only message you get). Take a look at the date column in question – does it appear as string(text) or does it appear as datetime?

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Sunday, February 5, 2017 7:17 PM
  • Thanks Albert, for detailed explanation and procedure for RefreshLink. I though we could not refresh link!! I'll apply the procedure instead of re-linking every time.

    Ok, i've figure out after long battle what the problem is. It is because back-end SQL Server returning dates with - as a separation mark instead of Access /... I've to compare it as a string in Where clause of report!!!

            strDateWhere = " DateComparison = '" & Format(Me.cbxDate, "MM/DD/YYYY") & "'"
            DoCmd.OpenReport "rptEmp_w/o_WP", acViewReport, , strDateWhere

    So, now i've changed the query as below and compare it as a string in Where clause of string. Now it works!!
    Possible to advice if this is the efficient way please? Isn't it better to create a View at back-end and link as a table?
    I'm asking this because I'm not sure if Access treats queries as SQL Server view and keeps stored result during background processing, without a need to run the query at run-time!

    Thanks for help all.

    SELECT
        tblEmployee.FullName,
    FORMAT([SignedIn],'MM/dd/yyyy hh:mm tt') AS [Signed In],
        FORMAT([SignedOut],'MM/dd/yyyy hh:mm tt') AS [Signed Out],
        IIf(Not (IsDate([tblEmp_WP_Clocked_Time].[SignedOut]) = 1),0,(DateDiff("n",[tblEmp_WP_Clocked_Time].[SignedIn],[tblEmp_WP_Clocked_Time].[SignedOut])/60)-(IIf(DateDiff("hh",[SignedIn],[SignedOut])>6,1,0))) AS Availability,
        FORMAT([SignedIn],'MM/dd/yyyy') AS DateComparison, tblShift.Next_Day
    FROM (tblEmployee INNER JOIN tblEmp_WP_Clocked_Time ON tblEmployee.ID = tblEmp_WP_Clocked_Time.EmployeeID) INNER JOIN tblShift ON tblEmployee.Shift_ID = tblShift.ID
    ORDER BY [SignedIn] DESC


    • Edited by KhurramKZ Monday, February 6, 2017 12:27 PM
    • Marked as answer by KhurramKZ Tuesday, February 7, 2017 6:14 AM
    Monday, February 6, 2017 12:23 PM

All replies

  •     If IsNull(Me.cbxDate) Then
            DoCmd.OpenReport "rptEmp_w/o_WP", acViewReport
        Else
            DoCmd.OpenReport "rptEmp_w/o_WP", acViewReport, "DateComparison = '" & Me.cbxDate & "'"
        End If

    Hi Khurram,

    And if you use:    "DateComparison = #" & Me.cbxDate & "#"  ?

    Imb.

    Saturday, February 4, 2017 1:56 PM
  • A few things.

    If you saved the PT query with a connection string, then really no need to set the connection with a tempvar at runtime.

    You are going to have linked tables in this application, so why not ensure that you re-link any PT query at that time when you linked the tables?

    Thus no need for the PTConStr routine at all (just dump it).

    As for your where clause, you using the WRONG parameter, and it is the 3rd parameter.

    Also, because you passing a date, you should format the date string correctly, and format it for Access, not T-SQL. And you need to ALWAYS use USA format – regardless of the users region and date format setting (if you don’t do this, then you find your code breaks with different date format settings from workstation to workstation).

    You thus need this:

    Dim strDate   as string

    strDateWhere = " DateComparsion = #" format(me.cbxdate, "MM/DD/YYYY") & "#"


    DoCmd.PenReprot “rptEmp_w//WP”,acViewReport,,,strDateWhere

    So don’t use or attempt to pass a “filter” to the report (the 2nd parameter), but use the 3rd parameter that is the “where” clause.

    And as noted, you don’t want to bother with the connection string routine – the PT queries should be setup at the SAME time you setup all of the linked tables. It also means you don't need to use a TempVar either. Note that any global variable will NEVER lose its value if you deploy compiled accDE. If you using a accDB, then global vars can lose their values and TempVars may be perferable - but in this case the TempVar and connection string is not needed nor required if you have any linked tables - you simply setup the PT query(s) at the same time.


    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada


    Saturday, February 4, 2017 7:27 PM
  • Correction: since the BE is SQL Server, a passthrough query should wrap date values in single-quotes, not hash marks.

    strDateWhere = " DateComparsion = '" format(me.cbxdate, "MM/DD/YYYY") & "'"


    -Tom. Microsoft Access MVP

    Saturday, February 4, 2017 8:54 PM
  • Actually, you must use Access format. The query is a PT, but the "where" clause in Access is not. So you use Access date format in this case.

    #MM/DD/YYYY#

    Often in the where clause we don't include the "#", but one should.

    Regards,

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Saturday, February 4, 2017 9:14 PM
  • Ah, you are correct. This was not the WHERE part of a PT query, but of DoCmd.OpenReport. Sorry about that.

    -Tom. Microsoft Access MVP

    Saturday, February 4, 2017 9:28 PM
  • If the restriction on the Date Comparison column had been in the pass through query's WHERE clause, aren't date literals in SQL Server in the format YYYY/MM/DD ?

    Ken Sheridan, Stafford, England

    Saturday, February 4, 2017 11:52 PM
  • The rules are more complicated than that - see https://msdn.microsoft.com/en-us/library/bb630352.aspx, which among other things says:

    Default string literal format = YYYY-MM-DD


    -Tom. Microsoft Access MVP

    Sunday, February 5, 2017 12:11 AM
  • Just a name of column.
    Sunday, February 5, 2017 8:22 AM
  • Thank you all for the feedback. Actually Access front-end is such that it could connect to multiple set of servers at different regions, so if someone from the management wants to connect to different region then what he only has to do is to write the server name in the space provided on logon screen and press connect button. I've then written a procedure which then drops all the linked tables and connect to similar database for another region on another server. When I didn't need this, it was all working fine using static Connect string for each query.

    This answers few things, why i am changing Connect string at run-time and this is why PTConnStr function was required. There isn't is only one PT query in program, so it means that if i change Connect for all queries at start up then I've to mention each query separately and change its connection string, or some kind of QueryDefs loop on which a special naming convention to select only PT queries for filtering .

    Yes the parameter in query I pasted is wrong, it's a filter and not a Where clause. Reason is that I've tried with Where but it hasn't worked, so I tried filter. Sorry about the confusion.

    Thanks about USA format, i know Access sticks to that but you further cleared it up for me.

    Ok, now i've tried to run the query as provided by you with all the formatting, but it still displays nothing as in first case. Where should I head for now please?

    Sunday, February 5, 2017 10:30 AM
  • Actually, when you re-link you don’t need (nor want) to delete the table links. If your code deletes the links, then such code becomes more complex without benefit.

    You just need to loop to change existing links, and THEN do the same for Pass though query.

    Eg this:

    Public Function SqlReLink(strCon As String)
      
       Dim dbCurrent           As DAO.Database
       Dim tdfcurrent          As DAO.TableDef
       Dim qryPass             As DAO.QueryDef

       Set dbCurrent = CurrentDb

       For Each tdfcurrent In dbCurrent.TableDefs
          If Len(tdfcurrent.connect) > 0 Then
             If Left$(tdfcurrent.connect, 5) = "ODBC;" Then
                tdfcurrent.connect = strCon
                tdfcurrent.RefreshLink
             End If
          End If
       Next tdfcurrent
       dbCurrent.TableDefs.Refresh
      
       ' link pass though
       For Each qryPass In dbCurrent.QueryDefs
          If Len(qryPass.connect) > 0 Then
             If Left$(qryPass.connect, 5) = "ODBC;" Then
                qryPass.connect = strCon
             End If
          End If
       Next qryPass
      
    End Sub


    So just add to your required and existing table re-link the near “same” code that re-links PT query(s). That way you can launch reports, forms etc. and NOT have to set the connection string each time. If you have to set connection strings each time for every report  etc., then things can get rather messy. As noted, since you HAVE to re-link the tables, then use the above approach. (and it save coding to delete the table links and include pt re-link).

    I also suggest you don’t include the Userid/password in those connection strings – it not required. Right before you re-link, you execute a logon – once you do that, then you can re-link all tables without including the UID/Password. And if you allow “different” users, then you don’t even have to re-link at all! (and this includes your PT queries!)

    How to make tables work without including the UID/password is outlined here:

    Power Tip: Improve the security of database connections

    http://blogs.office.com/b/microsoft-access/archive/2011/04/08/power-tip-improve-the-security-of-database-connections.aspx

    >>Ok, now i've tried to run the query as provided by you with all the formatting, but it still displays nothing as in first case. Where should I head for now please?

    This is a perfect example as to why we don’t want to mess with connection strings in code. We here trying to solve an issue and problem that should not exist.

    The first question is after setting the connection string and you code fails, then the query “does” have the correct connection string set, and should work.

    In other words, after the report does not work, close all code, forms, reports etc. Now simply click on the PT query – does it work? If a simple click to open the PT query does not work, then the report not going to work.

    So FIRST:
    Ensure that you can double click on the PT query. Get that working.

    Next, try running the report based in that PT query by simply clicking on that report. If the PT query works, then the report should work.

    Now, once and ONLY after the above two are working, then comment out the connection change call in your code and try running your code that sets and passes the “where” clause.

    As you can well see, if the PT connection query is “already” set, then it VERY easy to test the PT query is working without any code. And then next step of simply launching the report is ALSO again very easy to test and again occurs without any code. Note how easy the debugging process here is - we not even testing nor running code at this point.

    Once the PT query works, and the report works, then and ONLY then we can start testing our code.

    So does the PT query work when you double click on it? (even if your code failed – it would have set the connection string – so from that point you should be able to exit all your code and simply try + test the PT query by clicking on it. If the PT query works, close that and then next try a double click on your report – does it work?

    If the above two don’t work, then it is a wild goose chase to start playing with your VBA code that not working. You ONLY want to start looking at and testing your code to set the “where” clause when you are 100% sure the PT query is working and then ensure that the report works.

    If both the PT query and report works, but you code STILL fails, then I would modify the code to set a strWhere clause as per my example. You then right before the OpenReport place a debug.print of the strWhere value to see what the string looks like.

    It is possible that the datetime from SQL server is a datetime2 data type – and that will appear as a string in Access, and fail. To test this theory, open any linked table from Access to that table the PT query is based on in DESIGN MODE!!! (you have to answer “yes” to the read only message you get). Take a look at the date column in question – does it appear as string(text) or does it appear as datetime?

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Sunday, February 5, 2017 7:17 PM
  • Thanks Albert, for detailed explanation and procedure for RefreshLink. I though we could not refresh link!! I'll apply the procedure instead of re-linking every time.

    Ok, i've figure out after long battle what the problem is. It is because back-end SQL Server returning dates with - as a separation mark instead of Access /... I've to compare it as a string in Where clause of report!!!

            strDateWhere = " DateComparison = '" & Format(Me.cbxDate, "MM/DD/YYYY") & "'"
            DoCmd.OpenReport "rptEmp_w/o_WP", acViewReport, , strDateWhere

    So, now i've changed the query as below and compare it as a string in Where clause of string. Now it works!!
    Possible to advice if this is the efficient way please? Isn't it better to create a View at back-end and link as a table?
    I'm asking this because I'm not sure if Access treats queries as SQL Server view and keeps stored result during background processing, without a need to run the query at run-time!

    Thanks for help all.

    SELECT
        tblEmployee.FullName,
    FORMAT([SignedIn],'MM/dd/yyyy hh:mm tt') AS [Signed In],
        FORMAT([SignedOut],'MM/dd/yyyy hh:mm tt') AS [Signed Out],
        IIf(Not (IsDate([tblEmp_WP_Clocked_Time].[SignedOut]) = 1),0,(DateDiff("n",[tblEmp_WP_Clocked_Time].[SignedIn],[tblEmp_WP_Clocked_Time].[SignedOut])/60)-(IIf(DateDiff("hh",[SignedIn],[SignedOut])>6,1,0))) AS Availability,
        FORMAT([SignedIn],'MM/dd/yyyy') AS DateComparison, tblShift.Next_Day
    FROM (tblEmployee INNER JOIN tblEmp_WP_Clocked_Time ON tblEmployee.ID = tblEmp_WP_Clocked_Time.EmployeeID) INNER JOIN tblShift ON tblEmployee.Shift_ID = tblShift.ID
    ORDER BY [SignedIn] DESC


    • Edited by KhurramKZ Monday, February 6, 2017 12:27 PM
    • Marked as answer by KhurramKZ Tuesday, February 7, 2017 6:14 AM
    Monday, February 6, 2017 12:23 PM
  • Before you do a bunch of unneeded work:

    ?IsDate("2017-02-06")
    True


    -Tom. Microsoft Access MVP

    Monday, February 6, 2017 2:35 PM
  • Thanks Tom, will look at it.
    Tuesday, February 7, 2017 6:15 AM