Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2006

    Unanswered: Printing Reports Dynamically

    I am new to programming VBA Access Reports. In the past, I have programmed with SQL Server and Oracle and have made use of temporary tables in SQL Server and Ref Cursors within Oracle. I need to have many users be able to use my VBA program and I basically create a subset of my data based on a start date, an end date and one other criteria. I have a VBA form for my parameters and I need to know whether or not I can create some sort of data object that the Access Reporting Feature can use for each user, which could be a different query with a different number of rows.
    I may have several users logged in at the same time and hence I cannot store the information in a table by using an object like the recordset object and dump the results to a table.

    With Oracle,SQL Server, and Crystal Reports, I could use a Temporary table or a Ref Cursor within a stored procedure to pass back to Crystal Report.

    Could someone please help?


  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    have you split your db into a frontend (on each user's machine and holding the user interface) and a backend (on a server somewhere, holding only data)?

    once split, each user can have as many private temporary tables & queries etc as they need in their own front end.

    currently using SS 2008R2

  3. #3
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    I've done something similar in access using a local xml file to store user preferences for given reports. It keeps things nice and simple...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Feb 2004
    Chicago, IL
    I would go the route Izyrider is recommending. However if that is not possible, you can add another field to your table for the current user. If you have security setup use the CurrentUser() function to get the name of the current user and store it in the table, then query using the current user.

    If you don't have security turned on, then you can use the a function to get the current windows user, or the current windows system and store that in the table.

    Here is code to get the Current Windows user:

    'API Call to get the Username from the system
    Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
        "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    Function WindowsUserName() As String
        ' Returns the network login name
        Dim lngLen As Long, lngX As Long
        Dim strUserName As String
            strUserName = String$(254, 0)
            lngLen = 255
            lngX = apiGetUserName(strUserName, lngLen)
            If lngX <> 0 Then
                WindowsUserName = Left$(strUserName, lngLen - 1)
                WindowsUserName = ""
            End If
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts