Hi everybody, I have been programming in access + VBA for some time

[Trinak]

Hi everybody,<br> I have been programming in access + VBA for some time now, but came across problems<br>with .Seek in multiuser applications. I now know to SQL instead and this works fine.<br> My question is : Which method of multi-user programming would now be considered as<br>standard. ie: Split databases with access front-end or Access database with a VB6 front-end ???<br> My next project is quite a big one and I would like to know your thoughts as to which way to proceed.<br><br><br>All replies gratefully accepted………..<br><br>

 

[Quehay]

I'll let the experts weigh in for more definitive answers, but I guess the question boils down to whether you need explicit control of functionality or are happy with the Access behind-the-scenes handling of recordsets, etc.&nbsp;&nbsp;The Access forms are so much faster to get going but explicit coding of VB allows you to know exactly what is going on and why--and of course change these things.&nbsp;&nbsp;Access can be so funky when you change names, objects, etc. and you can't get at code behind the forms.

 

[MichaelRed]

Trinak,<br><br>The current popular persapective is that MS Access databases do not perform well in &quot;large&quot; situations, where large may refer to the number of users or the quantity/number of records.&nbsp;&nbsp;I have used - sucessfully - MS Access databases with well over 100 users, although I never found more than 70 on-line at the same time.&nbsp;&nbsp;This database system generated in the area of 50 to 100 records per day and maintained these on line - and dynamically updated on request/demand by users for a period of more than one-year.&nbsp;&nbsp;It also included the security requirements (UserName and Password) for ALL Users, and maintained an audit trail of each record (RecordId; username; oldvalue; newvalue; date/time).&nbsp;&nbsp;While the system did exhibit some slowing after being in use for three or four months, this was traced to the &quot;AuditTrail&quot; recordset and the issue resolved by transfering the 'Old' AuditTrail records to a seperate database.<br><br>Then only real issue in this process was the complaint - form the Network administration - over the ammount of network traffic generated.&nbsp;&nbsp;When I left the operation, the process was to be converted to a module of a larger system, so I do not know if this approach would be pratical for larger operations or for longer periods.<br><br>As I left the system, it included approximatly 30 forms and over 50 reports.&nbsp;&nbsp;The reports generally included User selectable criteria such as date ranges, department selection, grouping by clauses, etc.)<br><br>So, imho, other than the network traffic, there is no reason for the normal database system to NOT just use MS Access - if the designer is careful about the implementation.&nbsp;&nbsp;After the experience discussed above, the only part of the design I would change would be to have the &quot;fron-end&quot; be resident on the user's system, with a mechanisim to assure that any changes to the &quot;Front End&quot; were copied to the user's system each time they started the program, as this would relieve a part of the network traffic.<br><br>Of course, when the &quot;Front End&quot; and &quot;Back End&quot; are seperated, You cna then easily change the Database engine fairly easily, so IF Ms Access datasets do become a problem, you can just go get the &quot;Better&quot; database engine and move all of your records to that platform.<br><br>On a seperate thread, I would caution all database programmers to have extensive discussions with the Network administrators regarding the expected network traffic and the backup polilcies.&nbsp;&nbsp;I have already touched on the network traffic issue and will not go into additional detail.&nbsp;&nbsp;Re the Backup process, most network administrators do some type of backup on a regular basis - however, most backup system will not backup (copy) an open file.&nbsp;&nbsp;Further, without explilcit instructions from you (the database administrator) they will not auto-disconnect a user to accomplish the back-up.&nbsp;&nbsp;Worse yet, it is NOT common practice for these network administrators to notify you (the database administrator) of the failure of the backup.&nbsp;&nbsp;Thus ... a single user who does not log out of you system, or who is doing some work from home (in the dark of the night) on a regular basis WILL CAUSE THE BACKUP TO NOT OCCUR - AND YOU WILL NOT KNOW THIS!!!!!.<br><br>A final issue on multiuser database operation is the time stamp.&nbsp;&nbsp;Again, this is really the responsibility of hte network administrators, however it is also an issue which is vital to the database administrator.&nbsp;&nbsp;All user system - which are adding/deleting/modifying the data set or getting time stamped reports (ALL Reports ARE TimeStamped? Right?) must have their system clocks synchronized to the network/database server.&nbsp;&nbsp;MS Access and VB will use the local system clock for date/time information!&nbsp;&nbsp;If the clocks are not in Synch, you will get different results from different machines doing the same operation at (more or less) the same time!!&nbsp;&nbsp;I have seen instances where a user system clock was several DAYS in error.<br><br>Sorry about the soap-box length of this response, I just got carried away.<br><br>MichaelRed<br>There is never time to do it right but there is always time to do it over

 

[harryrich]

As far as I can tell from reading the fine print in the Seek method, it works on a directly open table only.&nbsp;&nbsp;It will give run time errors on an attached table or query.<br><br>If you still want to access tables programmatically as Recordsets you can use .FindFirst and .FindNext on any query and any table opened as a dynaset.&nbsp;&nbsp;These will make any selection you can make using an index and many others.&nbsp;&nbsp;<br><br>Of course the clause in the .FindXxxx needs to be compiled for each accesss, but I'm running though muliple accesses on 50,000 transaction batches ok.<br><br>It will help if you optimize your indices just as you would for a query with the same criterion in the WHERE clause.<br><br>Best,<br><br>Harry

 

[mdavis1]

&quot;&quot;&quot;&quot;with a mechanisim to assure that any changes to the &quot;Front End&quot; were copied to the user's system each time they started the program, as this would relieve a part of the network traffic.&quot;&quot;&quot;&quot;


ok..how might one achieve this?I have the FE on everyone's desktop and the BE on the server. I want to know how to update everyone without physically copying the FE to their desktop everytime i make changes.
thanks in advance

 

[MichaelRed]

Ouch. Wow. Blast Past. Hmmmmmmmmmm.

I have discussed and outlined this process in a few discussion threads, but I'm NOT sure how to find them (what KEY WORD(S) to use. Let me think on it a bit. IF I can't resurect the existing thread(s), I'll do it again.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

mdavis1,

O.K. Here are three links to previous threads / discussions:

[tab]thread181-84496
[tab]thread181-83809
[tab]thread 181-30072

Please review all three, as there are some variations.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[shyamsundar]

micahel,

Great...experience...thanx a lot..I am convinced to stick back to access yet.



Shyam
cwizshyam@yahoo.com

 

[oharab]

Shyam,
I'll just add my thoughts to Reds. I totally agree with him and will generally push the use of Access over creating a VB app. Why do all the work of binding records etc when Access does it for you.
I too have used it successfully with 100+ users, though without the full audit trail. Issues only started to appear when the db hit a million records, and was getting close the the 2gig file limit. A VB app will also hit those limits too with an Access back end.
I've never had any problems with backups, though I have not worked in a 24hr environment, all my db's have been closed at night so the routines worked fine. I will keep an eye out for this in the future though.
Perhaps your question shouldn't be Access or VB, but Access or another engine to store the data. As I mentioned, Access is limited to 2gig file size. If you are processing a vast amount of records then maybe (and it is only maybe) a bigger backend database such as SQL server or Oracle would be better. You can still use Access as your frontend though.
All these points are only really relevant for really big databases though. If you are planning on creating a small to medium sized system without an exceptional amount of concurrent users then Access every time!

Just my 2p's worth!!

B

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

&quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;
NoFX-The Decline
----------------------------------------------

 

[autocutter]

.Seek does not work with Front/Back DAO/ADO projects.

From the help file:

&quot;You can't use the Seek method on a linked table because you can't open linked tables as table-type Recordset objects. However, if you use the OpenDatabase method to directly open an installable ISAM (non-ODBC) database, you can use Seek on tables in that database.&quot;



Spliting into a backend data point with a seperate front application is the way to go-Access or otherwise. The trouble with Access is you get all the Access's problems-that said, it is a quick RAD.

The other gentleman was correct in stating the Find/FindFirst was your option. I believe the NoMatch function works, but since your working with a recordset that you would want to move to testing by BOF/EOF

 

[MichaelRed]

autocutter

&quot;.Seek does not work with Front/Back DAO/ADO projects.&quot; Is only partially correct. While the statement from the help file is correct, you need to realize that it appllies to -as plainly stated- to LINKED tables. Tables opened via recordset instantiation in code (dbengine) are perfectly ammenable to the use of the seek method.







MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[autocutter]

Yes it can be done, but is the juice worth the squeeze? Actually I understand that there are a couple of ways to retain .seek without wading through DAO and Find/Find First is not the fastest way to find something in any case. However, I realized that he was indeed looking in to spliting the app in to a front end app & linked table backend and had already moved away from the .seek method.

Ciao