Hi All,
I wanted to know that , Is this possible in MS Access that 30 users can add record (New record) at same time to same TABLE.
Thanks,
Mukesh Y
Hi All,
I wanted to know that , Is this possible in MS Access that 30 users can add record (New record) at same time to same TABLE.
Thanks,
Mukesh Y
Yes, if it properly designed. You may want to read this: http://www.accessmvp.com/TWickerath/.../multiuser.htm
thanks ! for your quick reply, I will take look.
“Operation must use an updateable query” error in MS Access"
after few minutes I get above error.
So far I have done:
-created database and added User table ( each user has separate table with three fields ..Time,ID,UserName and Time Field has primary key)
-using form Timer event (every 10 second )
-Splitted database into Back-End and Front-End
-changed default setting to (Edit Record,Shared mode)
-Given Front-End to User to save down the copy in local system to use.
-using ADO Conn.execute method to insert data.
first few minutes like (3-4 mintues) works fine, then I get above error.
Please give some suggestion to get over this problem.
Thanks!
Mukesh Y
Perhaps this link will be helpful: http://allenbrowne.com/ser-61.html
thanks for reply!
in entire process I used one query to insert data . (-using ADO Conn.execute method to insert data)
I have gone through each point but didn't resolve my problem. here is the list
- It has a GROUP BY clause. A Totals query is always read-only. ----- Not Using in the proecess
- It has a TRANSFORM clause. A Crosstab query is always read-only. ----- Not Using in the proecess
- It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause. Queries that aggregate records are read-only. ----- Not Using in the proecess
- It contains a DISTINCT predicate. Set Unique Values to No in the query's Properties. ----- Not Using in the proecess
- It involves a UNION. Union queries are always read-only. ----- Not Using in the proecess
- It has a subquery in the SELECT clause. Uncheck the Show box under your subquery, or use a domain aggregation function instead. ----- Not Using in the proecess
- It uses JOINs of different directions on multiple tables in the FROM clause. Remove some tables. ----- Not Using in the proecess
- The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields. ----- Not Using in the proecess
- The query's Recordset Type property is Snapshot. Set Recordset Type to "Dynaset" in the query's Properties. ----- Not Using in the proecess
- The query is based on another query that is read-only (stacked query.) ----- Not Using in the proecess
- Your permissions are read-only (Access security.)- checked full permission assign to every user
- The database is opened read-only, or the file attributes are read-only, or the database is on read-only media (e.g. CD-ROM, network drive without write privileges.) hecked full permission assign to every use
- The query calls a VBA function, but the database is not in a trusted location so the code cannot run. (See the yellow box at the top of this Access 2007 page.)
- The fields that the query outputs are Calcluated fields (Access 2010.)hecked full permission assign to every use
any other solution,most welcome.
I must admit I have no experience with ADO as I use DAO exclusively. Parhaps if you post the SQL for the query, someone will be able to see something.
this is my query....If you want to suggest any other query most welcome
Code:Sub AddRecord() Dim rs As ADODB.Recordset Dim Conn As ADODB.Connection Dim CompName As String Dim strSql As String CompName = VBA.Environ("ComputerName") Set Conn = CurrentProject.Connection '''Field [Time] has Primary Key strSql = "INSERT INTO " & CompName & " ([Time],ID,ComputerName ) VALUES(NOW(),1,'" & CompName & "')" ''' Id field is temp I will delete once testing come postive ID = 1 always Conn.Execute strSql, , adCmdText Conn.Close Set Conn = Nothing End Sub
Now we wait for someone with some ADO experience. I'll go look for someone.
ok..
same thread I have asked this form https://www.mrexcel.com/forum/micros...ml#post4748649
I wasn't aware of the forum post policy. I will post here once got resolve my problem.
I just ran the code from your post 8 on my computer.
I added a debug.print strSQL to your code.
Here is the result of the debug.print.Code:Sub AddRecord() Dim rs As ADODB.Recordset Dim Conn As ADODB.Connection Dim CompName As String Dim strSql As String CompName = VBA.Environ("ComputerName") Set Conn = CurrentProject.Connection '''Field [Time] has Primary Key strSql = "INSERT INTO " & CompName & " ([Time],ID,ComputerName ) VALUES(NOW(),1,'" & CompName & "')" ''' Id field is temp I will delete once testing come postive ID = 1 always Debug.Print strSql Conn.Execute strSql, , adCmdText Conn.Close Set Conn = Nothing End Sub
Lenovo-PC is the name of my computer. It is not a table name in my database. I also got an error on the INsert statement -which could be no such table.Code:INSERT INTO LENOVO-PC ([Time],ID,ComputerName ) VALUES(NOW(),1,'LENOVO-PC')
Did you define a table in your database? If so, show us the table design.
Can you tell us again what you did and any steps and the specific result.
Yes, I defined table.
as far I know table name shouldn't any special char, so I remove them first.
in your case you have to "-" replace this with blank and make sure that you have "LENOVOPC" as table.
Code:Code:Const SpecialCharacters As String = "-,!,@,#,$,%,^,&,*,(,),{,[,],}" 'modify as needed Function RemoveSpecialChar(txt As String) Dim myString As String Dim newString As String Dim char As Variant myString = txt newString = myString For Each char In Split(SpecialCharacters, ",") newString = Replace(newString, char, "") Next RemoveSpecialChar = newString Exit Function MsgBox Err.Number & " -" & Err.Description End Function
I have looked at some adodb example
added some code to your subroutine
I did create the table, and took out the "-" and added code in the sub to remove the "-"Code:Sub AddRecord() Dim rs As ADODB.Recordset Dim Conn As ADODB.Connection Dim CompName As String Dim strSql As String Dim errs1 As ADODB.Errors Dim Errloop As ADODB.Error Dim strTmp As String 10 On Error GoTo AddRecord_Error 20 CompName = VBA.Environ("ComputerName") 30 CompName = Replace(CompName, "-", "") 40 Set Conn = CurrentProject.Connection '''Field [Time] has Primary Key 50 strSql = "INSERT INTO " & CompName & " ([Time],ID,ComputerName ) VALUES(NOW(),1,'" & CompName & "');" ''' Id field is temp I will delete once testing come postive ID = 1 always 60 Debug.Print strSql 70 Conn.Execute strSql ', , adCmdText 80 Conn.Close 90 Set Conn = Nothing 100 On Error GoTo 0 110 Exit Sub AddRecord_Error: 120 Set errs1 = Conn.Errors 130 For Each Errloop In errs1 140 With Errloop 150 strTmp = strTmp & vbCrLf & "ADO Error # " & i & ":" 160 strTmp = strTmp & vbCrLf & " ADO Error # " & .number 170 strTmp = strTmp & vbCrLf & " Description " & .Description 180 strTmp = strTmp & vbCrLf & " Source " & .source 190 i = i + 1 200 End With 210 Next 220 Debug.Print strTmp 230 MsgBox strTmp 'my error handler from MZTools 240 MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure AddRecord of Module ModuleTesting_CanKill" End Sub
Table field info:
EntryID table_name table_description field_name field_description ordinal_position data_type length default 617 LenovoPC
time
0 Date 8
618 LenovoPC
id
1 Long 4
619 LenovoPC
computername
2 Text 50
LenovoPC data records:
time id computername 06-Feb-2017 2:42:02 PM 1 LENOVOPC 06-Feb-2017 2:42:45 PM 1 LENOVOPC 06-Feb-2017 2:43:05 PM 1 LENOVOPC 06-Feb-2017 2:43:06 PM 1 LENOVOPC
and it ran fine.
Impressive. Going to save this one.
thank so you much...I would love to test below part is totally new for me.
140 With Errloop
150 strTmp = strTmp & vbCrLf & "ADO Error # " & i & ":"
160 strTmp = strTmp & vbCrLf & " ADO Error # " & .number
170 strTmp = strTmp & vbCrLf & " Description " & .Description
180 strTmp = strTmp & vbCrLf & " Source " & .source
190 i = i + 1
200 End With
210 Next
220 Debug.Print strTmp
230 MsgBox strTmp
by the way I got solution.
https://www.mrexcel.com/forum/micros...ml#post4748932