Is this possible???

[mks123]

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

[RuralGuy]

Yes, if it properly designed. You may want to read this: http://www.accessmvp.com/TWickerath/.../multiuser.htm

[mks123]

thanks ! for your quick reply, I will take look.

[mks123]

“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

[RuralGuy]

Perhaps this link will be helpful: http://allenbrowne.com/ser-61.html

[mks123]

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.

[RuralGuy]

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.

[mks123]

this is my query....If you want to suggest any other query most welcome

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

[RuralGuy]

Now we wait for someone with some ADO experience. I'll go look for someone.

[mks123]

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.

[orange]

I just ran the code from your post 8 on my computer.
I added a debug.print strSQL to your 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

Here is the result of the debug.print.

INSERT INTO LENOVO-PC ([Time],ID,ComputerName ) VALUES(NOW(),1,'LENOVO-PC')

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.

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.

[mks123]

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.

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

[orange]

I have looked at some adodb example

added some code to your subroutine

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

I did create the table, and took out the "-" and added code in the sub to remove the "-"

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.

[AccessPower]

Impressive. Going to save this one.

[mks123]

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