Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 32
  1. #16
    kwooten is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    255
    yeah IT has to load the entire subset. They dont simply run updates.. maybe they should

    Quote Originally Posted by ssanfu View Post
    Not sure what you mean..

    You want EVERY record in tables "GroupSharedUser" and "ResponsibleUser" in the CSV file?




    Well, one way or another, you have to create and open the text file, write to it, then close it.


    I wrote the code to create the CSV files "Old school" creating the queries in line.

    Here is another version using saved queries (saves about 20 lines with the way I wrote the SQL):
    Code:
    Private Sub cmdCreateCSV_Click()  ' old school using saved queries
        Dim d As DAO.Database
        Dim r As DAO.Recordset
        Dim sSQL As String
        Dim TF As Integer
        Dim FileToWrite As String
    
        Set d = CurrentDb
    
        'GroupSharedUserMap
        Set r = d.OpenRecordset("qryGroupSharedUserToCSV")
        If Not r.BOF And Not r.EOF Then
            r.MoveLast
            r.MoveFirst
    
            'open CSV file for writing
            TF = FreeFile
            FileToWrite = CurrentProject.Path & "\" & "GroupSharedUserMap.CSV"
            Open FileToWrite For Output As #TF
            Print #TF, "UserID,Adapter,UserGroup"
            Do While Not r.EOF
                Print #TF, r!UserID & "," & r!Adapter & "," & r!AribaGroup
                r.MoveNext
            Loop
            Close #TF
            r.Close
        End If
    
        ' ResponsibleUser
        Set r = d.OpenRecordset("qryResponsibleUserToCSV")
        If Not r.BOF And Not r.EOF Then
            r.MoveLast
            r.MoveFirst
    
            'open CSV file for writing
            TF = FreeFile
            FileToWrite = CurrentProject.Path & "\" & "ResponsibleUser.CSV"
            Open FileToWrite For Output As #TF
            Print #TF, "Group,UniqueName,PurchasingUnit,PasswordAdapter"
            Do While Not r.EOF
                Print #TF, r!AribaGroup & "," & r!UserID & "," & r!PurchasingUnit & "," & r!Adapter
                r.MoveNext
            Loop
            Close #TF
            r.Close
        End If
    
       'clean up
        Set r = Nothing
        Set d = Nothing
    
        MsgBox "Done!" & vbNewLine & vbNewLine & "Saved in folder " & CurrentProject.Path
    End Sub
    And here is another version using the File System Object (approx the same number of lines as the version above):
    Code:
    Private Sub cmdCreateCSV_Click()   'using File Systen Object
        Dim d As DAO.Database
        Dim fs As Object
        Dim r As DAO.Recordset
        Dim a As Object
    
        Set d = CurrentDb
        Set fs = CreateObject("Scripting.FileSystemObject")
    
        'GroupSharedUserMap
        Set r = d.OpenRecordset("qryGroupSharedUserToCSV")
        If Not r.BOF And Not r.EOF Then
            r.MoveLast
            r.MoveFirst
    
            'open CSV file for writing
            Set a = fs.CreateTextFile(CurrentProject.Path & "\" & "GroupSharedUserMap.CSV", True)
            a.WriteLine ("UserID,Adapter,UserGroup")
            Do While Not r.EOF
                a.WriteLine r.Fields(0)
                r.MoveNext
            Loop
            a.Close
            r.Close
        End If
    
        ' ResponsibleUser
        Set r = d.OpenRecordset("qryResponsibleUserToCSV")
        If Not r.BOF And Not r.EOF Then
            r.MoveLast
            r.MoveFirst
    
            'open CSV file for writing
            Set a = fs.CreateTextFile(CurrentProject.Path & "\" & "ResponsibleUser.CSV", True)
            a.WriteLine ("Group,UniqueName,PurchasingUnit,PasswordAdapter")
            Do While Not r.EOF
                a.WriteLine r.Fields(0)
                r.MoveNext
            Loop
            a.Close
            r.Close
        End If
    
        'clean up
        On Error Resume Next
        Set a = Nothing
        Set r = Nothing
        Set fs = Nothing
        Set d = Nothing
    
        MsgBox "Done!" & vbNewLine & vbNewLine & "CSV files saved in folder " & CurrentProject.Path
    
    End Sub


    This version prints EVERY record to the CSV files..........

  2. #17
    kwooten is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    255
    @ssanfu, if I need to do a mass delete then would a delete query be the best way to accomplish that? We are about to our re-certification review which mean quite a few users will loose some of their group access.

  3. #18
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Yes, providing the table you are deleting the records is not the one side of a relationship.

    For example, if you want to delete ALL records from table "GroupSharedUser", you could use
    Code:
    CurrentDb.Execute "DELETE * FROM GroupSharedUser;", dbFailOnError
    or you could create a saved query "DELETE * FROM GroupSharedUser;" and execute it.

    If you wanted to delete all records in table "PasswordAdapter" (the one side), you would first have to delete the records from table "GroupSharedUser" (the many side) where there are related records.

  4. #19
    kwooten is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    255
    the only records I will be deleting is information from the GroupSharedUser table.. None of the Purchasing Units, Groups, or PasswordAdapter is being touched.. This is simply a removal of users from groups process so needed a way to delete these user from the GroupSharedUser.. I was considering taking a table of removals and setting the relationship to the GroupSharedUser to identify those records in the GroupSharedUser table to remove and use a delete query to remove only those records... hope that makes sense

  5. #20
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If you want to selectively delete records in table "GroupSharedUser", you could have a "table of removals", then have a sub to

    open a record set on the "table of removals"
    loop through the record set
    execute a delete query using criteria (could be by userID, or if a whole group by AribaGroupID_FK)


    Of if there are only a few (<20??), you could build a list and use the "IN" syntax in the criteria.

  6. #21
    kwooten is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    255
    Last quarter we removed about 50 users from specific groups. The criteria to remove would have to be userID and GroupID.

  7. #22
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My choice would be:
    If it is easy to get the data for the two fields into a table (table of removals), I would use the VBA looping, using both fields in the criteria clause of the delete query.


    In VBA, something like
    Code:
    open recordset on removals table
    start loop
    sSQL = "DELETE *"
    sSQL = sSQL & " FROM GroupSharedUser"
    sSQL = sSQL & " WHERE GroupSharedUser.AribaGroupID_FK = " & r!GroupID_FK &  " AND GroupSharedUser.UserID = '" & r!UserID & "';"
    Currentdb.Execute sSQL, dBFailOnError
    .
    .

  8. #23
    kwooten is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    255
    yes it is very easy to get that data into a removal table. Would I create a new module? How can I get the Form_Form2 code to run after the delete so the updated GroupUser is exported?

  9. #24
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Tell me the steps you envision you need.

    IIRC, you will
    1) delete some/all users from two tables
    2) add users to the two tables
    3) export the data from two tables to two CSV files

    What form(s) would you use?
    This all depends on what you want and the requirements......

  10. #25
    kwooten is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    255
    I would create a table of removals as suggested, based on UserID and Group, then have some mechanism to remove them from the current GroupUser table then export those the same way the Form exports. Also, based on the same removal criteria the data would be removed from ResponsibleUser and exported.

  11. #26
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    There you go.

    Create the removal table (do not use "Group" as a field name - it is a reserved word).
    Create a form to put the button on.
    Write the code to create a record set and loop through it to delete the records from the GroupUser table, then do the same thing to delete the records from the ResponsibleUse table.

    If you are going to export ALL of the records from the two tables to CSV files, I would create a module and code for the sub "cmdCreateCSV' in it.
    When finished deleting records, call the "cmdCreateCSV' code. Or have a button to execute the "cmdCreateCSV' code.

    Easy-pesy.......

  12. #27
    kwooten is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    255
    I think it would be easier to have the query do the delete since we can copy large data into the removal table then run the delete query.. Is there a macro I could then run that would export these files for me?

  13. #28
    kwooten is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    255
    I've tried using IN and EXISTS and cannot get this delete query to work.. Right now I am just trying to get the data in the removal query (had to create a query to pull in group #, that way if the group # is null then at least I know the group wasnt entered correctly and it needs to be fixed) to delete records in the GroupSharedUser tbl based on UserID matching and AribaGroupID matching.

  14. #29
    kwooten is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    255
    Finally got the IN to work..Created a Copy to run test...

    DELETE * FROM CopyOf_GROUPSHAREDUSER
    WHERE
    UserID IN (SELECT UserID FROM RemovalData) AND AribaGroupID_FK IN (SELECT AribaGroupID_FK FROM RemovalData)

    Now having trouble getting both pieces to run together.. I can get either/or to run but not the Delete then the CreateCSV..

    Code:
    Option Compare Database
    Code:
    
    Function Copy_Of_Delete_GroupUser()
    On Error GoTo Copy_Of_Delete_GroupUser_Err
    
    
        DoCmd.OpenQuery "Removal Query", acViewNormal, acEdit
        DoCmd.OpenQuery "Delete GroupUser", acViewNormal, acEdit
    
    
    
    
    Copy_Of_Delete_GroupUser_Exit:
        Exit Function
    
    
    Copy_Of_Delete_GroupUser_Err:
        MsgBox Error$
        Resume Copy_Of_Delete_GroupUser_Exit
    
    
    End Function
    
    
    Private Sub cmdCreateCSV_Click()
        Dim d As DAO.Database
        Dim fs As Object
        Dim r As DAO.Recordset
        Dim a As Object
    
    
        Set d = CurrentDb
        Set fs = CreateObject("Scripting.FileSystemObject")
    
    
        'GroupSharedUserMap
        Set r = d.OpenRecordset("qryGroupSharedUserToCSV")
        If Not r.BOF And Not r.EOF Then
            r.MoveLast
            r.MoveFirst
    
    
            'open CSV file for writing
            Set a = fs.CreateTextFile(CurrentProject.Path & "\" & "GroupSharedUserMap.addendum.CSV", True)
            Do While Not r.EOF
                a.WriteLine r.Fields(0)
                r.MoveNext
            Loop
            a.Close
            r.Close
        End If
    
    
        ' ResponsibleUser
        Set r = d.OpenRecordset("qryResponsibleUserToCSV")
        If Not r.BOF And Not r.EOF Then
            r.MoveLast
            r.MoveFirst
    
    
            'open CSV file for writing
            Set a = fs.CreateTextFile(CurrentProject.Path & "\" & "ResponsibleUser.addendum.CSV", True)
            Do While Not r.EOF
                a.WriteLine r.Fields(0)
                r.MoveNext
            Loop
            a.Close
            r.Close
        End If
    
    
        'clean up
        On Error Resume Next
        Set a = Nothing
        Set r = Nothing
        Set fs = Nothing
        Set d = Nothing
    
    
        MsgBox "Done!" & vbNewLine & vbNewLine & "CSV files saved in folder " & CurrentProject.Path
    
    
    End Sub


  15. #30
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Progress!!


    Why do you have 2 dots in both of the text file names? ("GroupSharedUserMap.addendum.CSV" and "ResponsibleUser.addendum.CSV")
    Better would be "GroupSharedUserMap_addendum.CSV" and "ResponsibleUser_addendum.CSV".



    Oops... I just noticed there is an error (small) in the code.
    Code:
            Loop
            a.Close
            r.Close
        End If
    should be
    Code:
            Loop
            a.Close
        End If
        r.Close
    Both places in the "cmdCreateCSV_Click" code should be changed.

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Form to Create Multiple Records
    By panza1370 in forum Forms
    Replies: 1
    Last Post: 06-11-2012, 02:48 PM
  2. Replies: 4
    Last Post: 03-26-2012, 08:36 AM
  3. Create multiple records with 1 form?
    By bergjes in forum Forms
    Replies: 4
    Last Post: 04-14-2010, 06:16 AM
  4. Trying to create multiple records from a form
    By ed_hollywood in forum Forms
    Replies: 4
    Last Post: 04-02-2010, 10:57 PM
  5. Replies: 3
    Last Post: 06-01-2009, 01:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums