Multi User Application with Access database
-
Hello experts, I am really stumped on this one. I have a VB6 front end tool to log in customer queris and the corresponding resolutions with start and end timestamps, customer name,email, phone number etc. Each customer interation is logged into the database via the application into the database and needs to be tagged with a unique ID. The requirement is that I need to generate the ID every time a new case is logged in the program and save the information to a temporary table first and then push it to the main table. this is ecause there are multiple users for the application and in order to avoid dupliation of the Case ID I have used the "idReturn" sub procedure to find the last updated ID and increment it to log a new case in the table. The problem is that the new case ID does not enerate in spite of the procedure I added , because the query in subprocedure "cmdUpdate_Click()" does not update the main table form the temporary table. Easch user would run an instance of the application from his or her own systema nd according to the a parameter set from the login form, another field called "RepID" will be updated in the table whe they log a case by clicking on the "Update" Command button after which the next Case Id should be generated. i guess I am kinda looking for a Token system to make sure that when a record has been updated with a unique case ID, the next user to press the update button to log a case should e given the next new unique ID dynamically created to store in the database. Any suggestions? Or could someone please direct me to some examples of Multi_user database programs in VB6 with similar record lock criteria? I can send anyone the source code if they wish to analyse it further. The database record would look like this ID Date Month Weekday Time Originator CustName.. etc 1 27.05.2008 December Tuesday 9:00:00AM Distributor Vicky West.etc . . . Please see the code for the form below. Sorry if it is toooooo looooooong!
Option Explicit
Public strword, intplace, idlast
Dim temp As Long
Dim currID As StringPrivate Sub cmdAdd_Click()
Data1.Recordset.AddNew Dim temp As Long date.Text = DateValue(Now) Dim m m = Month(Now) monthnam.Text = MonthName(m, False) weekd.Text = WeekdayName(Weekday(Now)) time.Text = TimeValue(Now) starttime = Now orig.Text = "" Text1.Text = "" copname.Text = "" contactno.Text = "" contemail.Text = "" qrytype.Text = "" prodco -
Hello experts, I am really stumped on this one. I have a VB6 front end tool to log in customer queris and the corresponding resolutions with start and end timestamps, customer name,email, phone number etc. Each customer interation is logged into the database via the application into the database and needs to be tagged with a unique ID. The requirement is that I need to generate the ID every time a new case is logged in the program and save the information to a temporary table first and then push it to the main table. this is ecause there are multiple users for the application and in order to avoid dupliation of the Case ID I have used the "idReturn" sub procedure to find the last updated ID and increment it to log a new case in the table. The problem is that the new case ID does not enerate in spite of the procedure I added , because the query in subprocedure "cmdUpdate_Click()" does not update the main table form the temporary table. Easch user would run an instance of the application from his or her own systema nd according to the a parameter set from the login form, another field called "RepID" will be updated in the table whe they log a case by clicking on the "Update" Command button after which the next Case Id should be generated. i guess I am kinda looking for a Token system to make sure that when a record has been updated with a unique case ID, the next user to press the update button to log a case should e given the next new unique ID dynamically created to store in the database. Any suggestions? Or could someone please direct me to some examples of Multi_user database programs in VB6 with similar record lock criteria? I can send anyone the source code if they wish to analyse it further. The database record would look like this ID Date Month Weekday Time Originator CustName.. etc 1 27.05.2008 December Tuesday 9:00:00AM Distributor Vicky West.etc . . . Please see the code for the form below. Sorry if it is toooooo looooooong!
Option Explicit
Public strword, intplace, idlast
Dim temp As Long
Dim currID As StringPrivate Sub cmdAdd_Click()
Data1.Recordset.AddNew Dim temp As Long date.Text = DateValue(Now) Dim m m = Month(Now) monthnam.Text = MonthName(m, False) weekd.Text = WeekdayName(Weekday(Now)) time.Text = TimeValue(Now) starttime = Now orig.Text = "" Text1.Text = "" copname.Text = "" contactno.Text = "" contemail.Text = "" qrytype.Text = "" prodcoThe problem with generating the case ID first is that two users can get caseIDs before either one is written to the database. Your primary key shouldn't be generated and should NOT be calculated until the record using it is written to the database. You don't checkout a case ID first, you get it last, after certain minimum data is written to the database. Also, Access is NOT a good candidate for a multiuser database. Yes, there are people around here who will argue that point, but I've supported too many of these and had to fix too many problems with them to given these people any credit.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008 -
The problem with generating the case ID first is that two users can get caseIDs before either one is written to the database. Your primary key shouldn't be generated and should NOT be calculated until the record using it is written to the database. You don't checkout a case ID first, you get it last, after certain minimum data is written to the database. Also, Access is NOT a good candidate for a multiuser database. Yes, there are people around here who will argue that point, but I've supported too many of these and had to fix too many problems with them to given these people any credit.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008Hello Dave, Thank you very much for your input. I tried it. First, I had the user form update the temporary table via ADODC control and then wrote a query to insert the record to the main table from the temporary table. Then I used the data1.recordset.delete method to delete the temporary table. The problem is the data is not transferrin from the temporary table to the main table but the temporary table is deleted. The result is that there is no addition to the main table. Could you help me with this please? Regards, Kumar kshincsk@gmail.com
-
Hello Dave, Thank you very much for your input. I tried it. First, I had the user form update the temporary table via ADODC control and then wrote a query to insert the record to the main table from the temporary table. Then I used the data1.recordset.delete method to delete the temporary table. The problem is the data is not transferrin from the temporary table to the main table but the temporary table is deleted. The result is that there is no addition to the main table. Could you help me with this please? Regards, Kumar kshincsk@gmail.com
Simple, don't use a temporary table. You're just needlessly making more "round trips" to the database and doing stuff that isn't really buying you anything.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008 -
Simple, don't use a temporary table. You're just needlessly making more "round trips" to the database and doing stuff that isn't really buying you anything.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008Hi Dave, Thanks a lot for the advice. I just created a record off the fly and used it to retrieve the ID with minimum value and "Flag=N" meaning it has ot been used for ay update. Then , I insert the data based on the text fields in my form. then I change the "Flag" field to 'Y' to say that this particular record has been used and use the followind method. Then I used the "idreturn" method to return the lowest value of ID with Flag=N. Worked like a charm!!! Absolutely no multi-user conflicts since each user would be opening a different record at the same time.
Dim strsql Dim conn As New ADODB.Connection Dim rs As New ADODB.Recordset strsql = "SELECT * " strsql = strsql & "FROM main " strsql = strsql & "WHERE Flag ='N' AND ID=" & temp With conn 'this reliably opens a connection .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\\10.192.16.21\global\uktech\db\uktech.mdb" .Open End With With rs .Open strsql, conn, adOpenStatic, adLockOptimistic End With 'Insert code here to update the Recordset with the txt box values . . . . . . . rs.update set rs= nothing End Sub . . . Public Sub idReturn() Dim CONNECT_STRING As String CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\\10.192.16.21\global\uktech\db\uktech.mdb" Dim rs As Recordset Set rs = New Recordset 'Generate a SQL Dim strsql As String strsql = "select MIN(ID) from main WHERE Flag='N'" With rs .Open strsql, CONNECT_STRING End With temp = rs(0) id.Text = temp rs.Requery rs.Close Set rs = Nothing End SubI got promoted to System Analyst on Friday a couple of hours after I showed my manager this program. This makes it the 7th app I have developed since I joined work October last year. Thanks for your support. Best regards, Kumar -
Hi Dave, Thanks a lot for the advice. I just created a record off the fly and used it to retrieve the ID with minimum value and "Flag=N" meaning it has ot been used for ay update. Then , I insert the data based on the text fields in my form. then I change the "Flag" field to 'Y' to say that this particular record has been used and use the followind method. Then I used the "idreturn" method to return the lowest value of ID with Flag=N. Worked like a charm!!! Absolutely no multi-user conflicts since each user would be opening a different record at the same time.
Dim strsql Dim conn As New ADODB.Connection Dim rs As New ADODB.Recordset strsql = "SELECT * " strsql = strsql & "FROM main " strsql = strsql & "WHERE Flag ='N' AND ID=" & temp With conn 'this reliably opens a connection .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\\10.192.16.21\global\uktech\db\uktech.mdb" .Open End With With rs .Open strsql, conn, adOpenStatic, adLockOptimistic End With 'Insert code here to update the Recordset with the txt box values . . . . . . . rs.update set rs= nothing End Sub . . . Public Sub idReturn() Dim CONNECT_STRING As String CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\\10.192.16.21\global\uktech\db\uktech.mdb" Dim rs As Recordset Set rs = New Recordset 'Generate a SQL Dim strsql As String strsql = "select MIN(ID) from main WHERE Flag='N'" With rs .Open strsql, CONNECT_STRING End With temp = rs(0) id.Text = temp rs.Requery rs.Close Set rs = Nothing End SubI got promoted to System Analyst on Friday a couple of hours after I showed my manager this program. This makes it the 7th app I have developed since I joined work October last year. Thanks for your support. Best regards, KumarThat's all well and good, but what if a second user runs the same query and it shows up exactly between the time user number 1 runs that query, and then runs the query to change the flag to 'N'? Trust me, it IS going to happen. Flags like this just don't work in a multiuser environment. If the operation requires more than a single step (query) to "checkout" an ID, you will end up with this problem. Maybe not today, maybe not next week, but even a year from now, it's bound to show up. The chances of it happening also increase with the inevitable increase in the number of users using this database.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008