Advantage/Disadvantage of using a Direct Update VS ADO connection (1 Viewer)

[Hudas]

Good day, I'm not even sure if I have the correct term for my post TITLE but I will try to explain it further.

1. When I am trying to copy a record in a table and insert it into a new table(another database) what I usually do is this:

Dim db as database
Dim strSQL as string
 Set db = currentdb
 strSQL = "Insert Into Table1 Select * From  [MS Access;DATABASE=" & Thepath and name of the database here & ";pwd=" & PasswordHere & "].[Table1] Where ID = 1"
 db.Execute strSQL, dbFailOnError
Set db = nothing

Question 1: Is there any issue/problem whith this? Especially the locking of the database when inserting.

2. And sometimes I do the Insert using this process:

Dim strConn As String
Dim conn as ADODB.Connection
Dim cmd as ADODB.Command
Dim strSQL as String
  strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Thepath and name of the database here & ";Jet OLEDB:Database Password=" & PasswordHere & ""
 conn.Open strConn
 
 strSQL = "Insert Into Table1 (Column1,Column2,Column3) Values ([Column1],[Column2],[Column3])"
 
 Set cmd = New ADODB.Command
             With cmd
                 .ActiveConnection = conn
               .CommandType = adCmdText
                 .CommandText = strSQL
                 .Parameters.Append .CreateParameter("Column1", adVarChar, adParamInput, 1000, Me!tbxColumn1)
                 .Parameters.Append .CreateParameter("Column1", adVarChar, adParamInput, 250, Me!tbxColumn2)
                 .Parameters.Append .CreateParameter("Column1", adVarChar, adParamInput, 250, Me!tbxColumn2)
                 .Execute , , adExecuteNoRecords
             End With
 
 conn.close

Question2: What is the best way/process to use?
Question3: What are the disadvantage/advantage?

Or should it really be compared?

I appreciate any support/advise/comments you can give

Thank you

Hudas

 

[Ranman256]

Both should work fine,
You didn't use a 3rd method, a query. This version uses Zero vb code. Zero vb errors. Faster development time.

All 3 will work tho.

 

[Hudas]

Thank you Ranman256....

I have a tool with a accde front end and accdb backend that is being used by almost 120 users but Since yesterday it already happened twice that they could not connect to backend and the error is could not find;file already in use. That's why Im asking it both approach has something to with opening the backend exclusively.

I thought initially that it is in coding issue but it seems not. They all have access to the folder.

Thank you
HUdas

 

[Hudas]

If I use this:

 strSQL = "Insert Into Table1 Select * From  [MS Access;DATABASE=" & Thepath and name of the database here & ";pwd=" & PasswordHere & "].[Table1] Where ID = 1"
 db.Execute strSQL, dbFailOnError

Would executing this code open your backend to openexclusive?

Thank you
Hudas

 

[The_Doc_Man]

When you have this problem, check that the person who has it opened exclusive-mode has correct permissions on the folder. While there are many causes for the symptom you names, a simple one is that because you don't have correct rights for the folder, the lock file is not usable in the intended way and this forces an exclusive open to occur - because the shared locking method won't work correctly. Fixing the permissions would fix the open-exclusive problem.

 

[Hudas]

Thank you The_Doc_man... I checked the permissions and it shows that they have full control/access.

With almost 100 users its possible that they're saving at the same time. Would access be able to handle saving of a record lets say 15-30 at the same time?

I have already research on MS access for multi user and have already made those changes. I just need more clarifications like the one above.

Thank you
Hudas

 

[CJ_London]

can users open the back end themselves, without going through the front end?

If so, and they are the only one in it and even though they did not open exclusively, by opening a table in design view, they automatically change the status to opened exclusive, thereby locking out subsequent users.

 

[Hudas]

Thank you CJ_London - The users only way to add data to the backend is thru the front end using the ADO connection I stated above or this code

[MS Access;DATABASE=" & Thepath and name of the database here & ";pwd=" & PasswordHere & "].[tablename]

Its really hard to set up MS Access for multiple users.

 

[static]

Is there a reason why you aren't just linking the tables?