Question for Pat

[doulostheou]

I was looking through some old posts to see if I could figure out where I am going wrong when I ran across this quote from you:

Stored querydefs are the most efficient way to use SQL. When the querydef is saved, Jet validates the code and creates the most efficient access plan for executing the query. This information is stored for use whenever the query is run. When you use SQL in code or as the recordsource for forms or reports, this calculation and optimization must be done each and every time the query is run.

I recall reading something very similar to this before and from what I understand your opinion is one to be valued. I was just wondering if this would apply to what I am trying to accomplish (described in the thread at the bottom of this post). I don't know much about querydefs, but in my code I am looping through 200 users and running an append and delete query for each. Is there anyway to write a dynamic querydef? Where the table would change each time the code ran the query (I imagine I would still have to loop it through code to change the variables). I have nearly got the code below working, but I ran into a dead end trying to detect if a table was null and I am not satisfied with the time efficiency of the procedure as a whole (albeit greatly improved from running it from a macro).
http://www.access-programmers.co.uk/ubb/Forum7/HTML/003455.html

 

[simongallop]

From what I can see, your master table is in a seperate database from all the other tables. If this is the case, then why not use a common name to link the table that is being used, carry out the append and delete and then change the table.

so:

link table JoeBloggs to master database as User
append
delete
delete link
link table MikeSmith to master database as User.
etc...

This way you do not need to change the query defs.

 

[doulostheou]

Thank you for your replies. Unfortunately, I don't go back into work until Thursday to actually test any of the suggestions given me (here and in the other post).

Pat, it all seems really inefficient to me, but I can't think of any way around it. The reason I am not working with one table is because my users make entries too frequently at the same time. As people submitted entries, they kept getting error messages stating they were trying to update the same record as someone else. Then they would lose the entry they just typed. From what I was told by our network people the data is colliding at a network level. I understand if I use something like MySQL for the tables it will solve the problem, but experimenting with this is not an option at my work place. My solution was to give everyone their own database to enter their data and pull all the information to a master table one record at a time by linking to each of their individual databases. I cannot think of any way around this, because of the conflicts I was getting with 50 users trying to make new entries for each incoming call.

I'm trying to figure out how to include a step in the loop that will evaluate whether the table isnull before running the queries. This should at least save time as it is even more inefficient to run an append and delete query on an empty table.

Harry, I didn't know that you could create links through code. This will save me one more step when setting someone up with a logsheet and reduce the clutter in my tables (you can imagine). And if I'm understanding your suggestion, this should allow me to store the querydefs, thus making my database a tad more efficient. I have no idea how to create and delete links in code, so any help there would be appreciated. I can't wait to get back and try it.

Thanks again to both of you.

 

[simongallop]

What version of Access are you using? Asking as there are different ways to link tables by code depending on Acc97 or 2000

 

[doulostheou]

I am using access 97