Results 1 to 6 of 6
  1. #1
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568

    Requery the subform within multiuser environment - split database

    Hi,



    I am inserting into table tbl_Money range of dates choosen by user.

    Code:

    Code:
    Dim dteDate As DateDim dteDateCounter As Date
    Dim dteDateEnd As Date
    Dim sqlString As String
    Dim counter As Byte
    Dim Money_value As Currency
    
    
    dteDate = Format([TextBoxDfrom], "MM/YYYY")
    dteDateEnd = Format([TextBoxDTo], "MM/YYYY")
    
    
    If dteDateEnd < dteDate Then
        MsgBox "Data korekty DO powinna być większa niż Data korekty OD)"
    End If
    
    
    dteDateCounter = dteDate
    
    
    While dteDateCounter <= dteDateEnd
        counter = counter + 1
        dteDateCounter = DateAdd("m", 1, dteDateCounter)
    Wend
    
    
    Money_value = Textbox_kwota.Value / counter
    ''dteDateEnd = Month(Forms![Main].[txbDateTo]) & Year(Forms![Main].[txbDateTo])
    
    
    While dteDate <= dteDateEnd
        
         sqlString = "INSERT INTO tbl_Money(Money_FK, Money_value, Data_korekty) VALUES(" & ID.Value & ", " & Money_value & ", #" & dteDate & "#);"
         Debug.Print sqlString
         CurrentDb.Execute sqlString
         dteDate = DateAdd("m", 1, dteDate)
    Wend
    
    
    QryMoney.Requery
    my relationship:

    Click image for larger version. 

Name:	relation.png 
Views:	24 
Size:	8.3 KB 
ID:	30373

    I am afraid that working in split database (a lot of users at the same time) will blow up my code when users will be trying to input range of dates and sums into my table (requering in the same time).

    It is possible to workaround this?
    How to refresh form in the same record in order to see immiditiely inputed records?

    Please help,
    Best Wishes,
    Jacek

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    i doubt it. Not every user will be inserting data at the same time.
    And since every user has their own copy of the FE code, there should not be a 'blow up'.
    So SPILT db is the answer.

  3. #3
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Thank you,

    But what if 2 users will be inserting data in the same data using Access Runtime?
    There will be the error and runtime will be closed. Am i right ?

    Maybe i can create error handling and loop as long as userform will be available to requery ?

    Best Wishes
    Jacek

  4. #4
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Did you have ever situation like this ?

    Jacek

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by jaryszek View Post
    <snip>
    I am afraid that working in split database (a lot of users at the same time) will blow up my code when users will be trying to input range of dates and sums into my table (requering in the same time).
    <snip>
    If you have a split dB where the BE holds only the tables, the FE has everything else AND each user has a COPY of the FE on their computer, I don't think you will have a problem with updating.

    The BE only holds data in tables. You don't requery tables (unless you allow users to edit tables directly). You requery form/report/controls.

    In my dBs, I use queries (never just a table) for form/report record sources and for combo/list boxes row source. So the fact that a different user has not finished entering/editing data for a record shouldn't affect you. The record might not show up or might have the pre-edited data, but there shouldn't be a conflict.
    However, if two users are trying to edit/save the SAME record, you will have a conflict.

    Reading data (records) should never cause a conflict.

    And inserting new records should not cause a conflict........ only editing the same record at the same time...

  6. #6
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Thank you Steve for your answer, it is very helpful,

    My fears connected with it are smaller now,

    Best Wishes,
    Jacek

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 08-08-2017, 11:46 AM
  2. Replies: 1
    Last Post: 03-17-2014, 11:27 AM
  3. Split DB Question - Multi User Environment Launch
    By aussie92 in forum Database Design
    Replies: 6
    Last Post: 01-22-2014, 03:19 PM
  4. Replies: 6
    Last Post: 02-22-2013, 11:28 PM
  5. multiuser for access database
    By cupidleomanoj in forum Access
    Replies: 4
    Last Post: 09-28-2010, 10:49 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