Multiple users using form causes problems 2

[RamziSaab]

i have the following code in a form, basically when the user picks a table from a combo box it will populate a list box with the fields present...now if more than one person is using this form it will delete the other persons table (and hence he cant continue using the form without errors)...

can anyone help on a method to solve this.

Private Sub cboTable_afterupdate()
On Error GoTo Err_cboTable_AfterUpdate

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim fld As DAO.Field, rst As DAO.Recordset
Dim tbl As String, k As Integer

tbl = Forms![frm Advanced Search]!cboTable
' Return reference to current database.
Set dbs = CurrentDb
' Return reference to Employees table.
Set tdf = dbs.TableDefs(tbl)
DoCmd.SetWarnings False
DoCmd.RunSQL ("Delete * from usystablefields&quot
DoCmd.SetWarnings True
Set rst = dbs.OpenRecordset("usystablefields", dbOpenDynaset)
' Enumerate all fields in Fields collection of TableDef object.
For Each fld In tdf.Fields
If fld.Type >= 1 And fld.Type <= 8 Or fld.Type = 10 Then
rst.AddNew
rst!FieldName = fld.Name
rst!FieldType = fld.Type
rst.Update
End If
Next fld
Set dbs = Nothing

'reset
lstField.Requery
Me.Controls(&quot;lstField&quot.Visible = True

For k = 1 To lstField.ListCount - 1
lstField.Selected(k) = True
Next k

For k = 3 To lstField.ListCount - 1
lstField.Selected(k) = False
Next k
lstField.Requery

 

[GingerR]

do you have the db split into backend-frontend configuration? if not perhaps it would be best to do that, and put the front ends on the users' machines. then the table that gets its records deleted and appended will be local to each user instead of shared by all.

 

[MichaelRed]

but only if that specific table is part of the &quot;Front End&quot;, which is (generally) inconsistient with the common usage oif a split db. In a more general process, you could generate a query to abstract the fields of interest. This would place the recordset local (to the user's instance of the application) and avoid the conflict. A further advantage would be to have less growth / bloat in your application / date as there would be less building, loading and destroying of table type recordsets.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RamziSaab]

if i split the database will it work...i mainly designed this for one person, but i am wondering if when i leave work (placement) more people will want to use it as the database gets larger and has more useful info...if i split the database will this slove the problem...(i might try it have to try it on a backup copy to see )

 

[MichaelRed]

It will work on a split db - IF (and only IF) the temporary table is in the FRONT End -AND- the front end is installed seperatly on each users workstation. That, however will not stop the individual &quot;front ends&quot; from growing (increasing in size) for each instantation of the procedure. Further, the splitting (and seperate installation) will somewhat complicate the maintenance of the front end. Although the subject has been discussed in several threads in these fora, I suspect that you would find the implementation beyound your present capability. I, again, suggest that you reconsider the overall process and design it in some other manner (perhaps using a query -or even a few of them- generated in your code. these would always return local (to the specific instantation of your application) recordsets, and not result in (useless) growth of the application.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RamziSaab]

would dbopensnapshot (instead of dbopendynaset) fix the error?

About reconsidering the design (i just finished it and to be honest want a quick fix solution) but if i am not wrong what u are saying is to do the following set the lsit box row source type to field list...and put the following code?

Private Sub cboFieldName_AfterUpdate()
On Error GoTo Err_cboFieldName_AfterUpdate


Dim strsql As String
Me!List2.RowSource = Me!cboTable
Me!List2.Requery
end sub

insteaad of that junk i have above (in my first thread)?


Will this solve my problem?

 

[MichaelRed]

It is a bit more elaborate than that. You would ned to &quot;build&quot; a complete and correct sql statement in code, create a recordset based on the (execution of) the sql statement ane assign some object's recordsource (or controlsource) to the recordset.

If you use 'advanced' search in these fora, there are NUMEROUS samples / examples of each of the various steps in the sequence, and probably a few where a more-or-less complete soloution is shown within a procedure. You would, of course, need to modify any example(s) found to suit your specific object names, but it is (in my opinion) the more common approach to what you appear to be attempting.

I, personally, prefer the 'structure' in building asn sql statement where several seperate substatements are declared and 'populated' with the relevant (key word) clause of the final statement and a final string variable simply concatenated the several segments. That, however, is a purely personal 'style'.

There are so many variations on the overall process that -beyond the above- I cannlt offer much advice on the details.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RamziSaab]

The point is i have already finished my query builder, tried it by changing the to the above code and using

'create the IN string by looping thru the listbox
For i = 0 To lstField.ListCount - 1
If lstField.Selected(i) Then
strIN = strIN & &quot;[&quot; & Me!cboTable & &quot;].&quot; & &quot;[&quot; & lstField.Column(0, i) & &quot;] ,&quot;
End If
Next i

instead of


rst.MoveFirst
For x = 0 To lstFieldK.ListCount - 1
If lstFieldK.Selected(x) Then
strink = strink & &quot;[&quot; & Me!cboTable & &quot;].&quot; & &quot;[&quot; & lstField.Column(0, x) & &quot;] ,&quot;
rst.MoveNext
Else
End If
Next x


and it seems to give the same result so far need to finish changing the other ones to see! Any reason why it is bad to do this? By the way MichaelRed your help is much appreciated!

 

[MichaelRed]

As I mentioned, the specific manner you choose to implement eah part of the process is subject to variation, according to your your style / choices. My 'point' is that by having a persistant recordset (identified table) you will cause the database to grow each time you add a record to the table. The location of such a persistant table is not relevant to the fact of the process engendering hte growth. It is pertinent to WHERE the increase in size will occur, as THE .MDB (or .MDE) which 'holds the table will be the one which continues to grow.

&quot;Splitting&quot; the app into a traditional front end / back end leaves the persistent table in the (common) 'back end' and does nothing to relieve the user conflict, as both users would still remove and add records to that sam persistent table. Non-Traditional splitting would permit the process to function w/o user-to-user interferance, by having hte table created in EACH local copy of the front end, however the growth would continue to occur, it would just be distributited across the seperate front ends, as the same record deletion and addition occurs.

Use of a query type of recordset -within the split database archicture- relieves bothe the concern over user-to-user interferance AND the continuing growth of the application engendered by the on-going process of (first deleting) adding records on each iteration / instantation of the process.

Again, the individual steps of the process are amenable to variations and choices which you will make throughout the development - it is only the use of the persistient (table type) of recordset which 'must' be eliminated from your process.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[GingerR]

I have done this--with some local tables in the front-end which is on the users' machines. the tables' records get deleted and more appended regularly, for each user. this is because people choose different groups or items they want to put into charts, and the calculations were very slow, so i did it this way. the entire company - we have 20G hard drives. i put out new versions (new code and of course compacted) sometimes not for months. i've never had a problem.

 

[MichaelRed]

Well! Good for You -and your users. They obviously have 'generous' allotments of storage and do not traffic in the process as extensively as they might.

That said (and meant). It does not necessarily apply in any universal sense. MANY factors will contribute to the (size of the) 'problem' - but to categorically say it doesn't happen is not a safe approach. One of my 'rules-of-thumd' regardiing databawe programming is that possability = probability = certaintity. e.g. if is CAN happen it WILL happen. Murphy's Ith Corralary adds &quot;WHEN 'it' happens - it will be at the worst possible time&quot;. In the conjunction of these, I find little solace in anecdotal experience from a single source.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[GingerR]

i never said it doesnt happen. i just said it works for me. i weigh consequences of everything i do and normally take a conservative approach (re: probability = certainty). for now this method is fine. for me. only me. right now. the end.

 

[MichaelRed]

Per the previous, i did say 'good for you ...'. On hte other hand, posting here can only be viewed as advice or recommendation. Those who are 'listening see these postings as related to the topic underdiscussion and expect the information to be relevant to the thread. So when you say ~~ 'it works for me', they take it as advice to do the same. IF you qualify the statement with some specifics of circumstance, perhaps (but ONLY perhaps) some will read these as cautions and either follow up with additional questions re those specifics or take care in their implementations to be sure their circumstances are alt least close. More generally, either the unwary will simply assume what you post to be the 'gospel from on high' and proceed.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RamziSaab]

Michael
Ok finished my changing my design, all list boxes and combo boxes are now populated using an sql (built in the code)and it seems to work fine for both users...it also cut my code down by a few hundred line hehe!!

Just to clarify i originally (when i started designing the form, with very little vb) i downloaded a generic form from here

http://www.rogersaccesslibrary.com/download2k.asp?SampleName='CreateQueryFields2k.mdb'

Though my form is much more complicated (6 tables, 2 criteria and an expression builder) i am wondering what is the using temp tables instead of SQL? does this affect performance?

 

[MichaelRed]

To ask the &quot;Why&quot; of an individual's choice is not much less than asking the cosmic &quot;WHY&quot; of life itself, so I'll not even attempt.

There are individuals who 'practice' programming and are even paid to do so without either the formal education or the 'thoughtful' process of considering the impact of their (programming) actions.

In general, public 'bulletin boards' and private web sites (including these fora @ Tek-Tips) are not 'policed' - or only policed for specific 'infractions' of site policy (e.g. &quot;foul language&quot; or &quot;off topic&quot; subjects, or &quot;Students&quot; requesting homework help).

So you can find numerous instances, 'soloutions' to issues implemented by 'professional' programmers which are perhaps workable for the specifics of THEIR situation, which THEY worked quite dillegently in developing and feel sufficiently &quot;proud&quot; of to post either as a boast of their capability or as an answer to others' questions along the same lines.

I think the overall process is more-or-less 'universal' and somewhat related to the education/experience level. A quite simplistic example is seen in many (most?) homes where scribbled drawings of young childern are &quot;proudly posted&quot; around the house. The &quot;art work&quot; is clearly just the 'work' of small childern, BUT it IS their best effort and both the child and parent are proud to display it -when the child is still young (not educated and little experience). Years later, the child is (usually) embarrased if the parent continues to display these efforts. We, as &quot;professionals&quot;, likewise pass through various stages of development, and -reasonably- are proud of our accomplishments at the time of their occurance. We all were proud of our first &quot;Hello World&quot; program, but generally would be a bit embarassed to have anyone point it out as an 'accomplishment' after a few years of involvement in commercial programming.

We are mostly 'on the journey' of learning skills, wheather the skill is 'scribbling' in kindergarden, writing reports in secondary schools, graduating from the last stage of school we attend or working at our craft, the process is one of learning, practicing and maturing. The posting you found may be either a current 'proud accomplishment' or a forgotten relic of long ago which would be hastily hidden if the 'author' realized it was still posted or just posted in the specific manner as a 'learning exercise'. It doesn't realy matter. What DOES matter is simply that you were able to recognize that there was an issue in implementing the 'soloution' in YOUR situation, and -more importantly- persue the 'learning' process to improve YOUR ability.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RamziSaab]

reading my previous post i dont acutally see the word why anywhere, i am just wondering what are the benifits of using recordsets instead of the other methods! but its ok more curiousity then anything else as long as it works i am fine...

u wrote:
What DOES matter is simply that you were able to recognize that there was an issue in implementing the 'soloution' in YOUR situation, and -more importantly- persue the 'learning' process to improve YOUR ability.

answer...the way to learn and get better results is to find different methods and their respectives advantages and disadvantages in order to build experience of ur own for the next time!!!!

Though thank you for that leasson about life

 

[substitute]

Did anyone think of making the row source for the list box a value list and using the tabledef to populate the value list? No creating and deleting of tables or anything, the list is a property of the user's instance of the form so is unique to each user.

 

[RamziSaab]

all i have done is used the field list and SQLs as the rowsource and it seems to do the job fine with very little code needed