How do I create a copy of a table in VBA

[rpochoda]

I have a master contacts table for a firm and want to allow each individual at the firm to choose a subset of these contacts as his/her personal contact list. I've created a detail table with two fields, ContactID and EmployeeID to contain the personal list data. I added a Y/N field to the master table where an individual can check off the contacts he wants on his list and then wrote a module which loops through master table recordset, adding records to the detail table based on the Y/N field, and at the end, clears the checks from the master Y/N field. But it occurs to me that with this procedure, if two users try to create personal lists at the same time, they will alter each other checks in the Y/N field. I don't want to require a user to have to open the db exclusively when creating his list. I was thinking that it should be possible to write code to create a copy of the master table on the fly with a unique name, say a time stamp, and delete it at the end of the process. Then simultaneous users couldn't interfere with each other.

But I can't figure out how to create a copy of a table in VBA.

Two questions:
(1)To begin with, is this the best way to get data for the detail table.
(2)If so, how do I create the copy of the master table in VBA.

 

[Golom]

Making a copy is fairy straight-forward

Select * [COLOR=red]INTO tblCopyOfContracts[/color]

From Contracts

You could address your issue by attaching a Userid field instead of a Y/N field and sticking a userid into the field when the user clicks on it. Your post processing then just adds the contract numbers that have the current user's userid on them instead of all records that have "Y". In that way multiple users can select contracts without confusion about who gets which one.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[CautionMP]

There is no need to duplicate the master contact table, with the correct relationships the database will take care of managing the records in the detail table for you.[ol]
[li]Move the Yes/No field from the MasterContactTable to the DetailTable.[/li]
[li]Set the default value of DetailTable.EmployeeID to the Employee Id ([tt]=ENVIRON$("USERNAME")[/tt]?)[/li]
[li]Create a One-to-many relationship between MasterContactTable.ContactID and DetailTable.ContactID[/li]
[li]Create a query for maintaining "MyContacts" with the following attributes:[ul]
[li]All fields from MasterContactTable, and the flag field from Detail.Table[/li]
[li]Include ALL records from MasterContactTable and only those records from DetailTable where joined fields are equal (OUTER join).[/li][/ul][/li]
[li]Bind your form to this new query.[/li][/ol]

This will cause the database to add a new record to DetailTable when the user selects the Yes/No box (if necessary). If the user un-checks the Yes/No box, the record will still remain in the DetailTable, but is easily hidden with a filter (see below).

If the form is opened with no filter, all contacts are displayed, if you filter WHERE Yes/No = [navy]True[/navy] the user will only see their contacts.

Hope this helps,
CMP


(GMT-07:00) Mountain Time (US & Canada)

 

[rpochoda]

Thanks for the suggestions. It took me while to get back to this, I was involved in other parts of the project. Thought you might want to hear how things worked out..

1) Golem's suggestions:

Select…Into -- I had tried this but hadn't set up the whole QueryDef procedure correctly, hence my question. I've since found my mistake.

Using UserID instead of Y/N -- If that's in a Contacts Table copy, it wouldn't really save any processing. If it's in the original table to obviate the need for a copy, there's still the possibilty of a 2nd user overwriting the ID of the 1st before the processing can take place.

2) CautionMP's suggestion

As described, it seems to work if only one employee can claim a contact, which is not the case here. If a previous employee has already been picked a particular contact, no new record is written when another employee also picks that contact.

When I tried setting a condition on the the Detail Table in the join "where EmployeeID = [current user]", I only got that employee's subset of records despite the outer join. I don't understand why. Any thoughts?

However, when I replaced the Detail Table in the join with a saved query on the Detail Table where EmployeeID = current user, the full contact list was returned with the Y/N's appropiriately checked, and records were added correctly.

On closing the form on which the user can pick his contacts, I'm running a delete query to remove records from the detail table where the check has been removed. I don't think this can lead to trouble, but wonder what might happen if 2 users are deleting at the same time. The documentation on record locking doesn't talk about delete queries. Again, any thoughts?

 

[Zion7]

rpochoda,
I think you'll always have concurrency conflicts,
If you simply have one chkBox per contact, for each user.

just a thought, at the top of my head,
Your Detail table, should include a record, for EVERY contact,
per User.
So evety time a User selects a Contact,
A record should be added or deleted, to/from the details table.


________________________________________________
Private Sub chkContact_Click()
Dim SQL as String
if chkContact = -1 Then
SQL = "INSERT INTO tblDetail(ContactID,EmployeeID)"
"VALUES(" & Me.ContactID & "," & CurrentUser() & ")"
Else
SQL = "DELETE FROM tblDetail WHERE ContactID =" & Me.ContatID & _
" And EmployeeID =" & CurrentUser
End If

currentProject.Connection.Execute SQL,,129
End Sub
____________________________________________________

Now your query's recordset would look like this,


"SELECT * FROM tblContact " & _
"WHERE pkContactID IN(" & _
"SELECT fkContactID FROM tblDetail " & _
"WHERE (((txtEmployee) =(CurrentUser()))))"

...something along those lines.

 

[VicRauch]

During the time of the selection, either (or both) using a DoubleClick on each field in the listing of Contacts, or having a button that has "Include". Then the code will take the active record and add it right now to the users list of Contacts. No interference with other users, because it will not be a batch process, but done right now with the current record.
HTH,
Vic

 

[CautionMP]

As described, it seems to work if only one employee can claim a contact

If the Yes/No field is in the Detail Table you can have a hundred concurrent users with no conflicts, this is becasue the Yes/No is transient with the user and NOT related to the Master Contact record.

When I tried setting a condition on the the Detail Table in the join "where EmployeeID = [current user]"...

Yes, the filter will override the outer join if the filter is placed on the combined recordset. Sorry for the mis-information on this, the filter will need to be applied to the details table BEFORE it is joined to the master table. It sounds like you worked around this, something like:

SELECT Details.Flag, Master.*, Details.Employee
FROM Contacts AS Master 
LEFT JOIN [b](SELECT * FROM MyContacts [highlight]WHERE EmployeeID=[current user][/highlight])[/b] AS Details 
ON Master.ID = Details.ContactID;

On the delete, if the Yes/No field is in the Details table there should not be record locking issues. Since deleting the items is not real critical you might consider doing this as a seperate periodic maintenance process.

CMP


(GMT-07:00) Mountain Time (US & Canada)

 

[VicRauch]

If the Yes/No field is to identify which contact to add to the user's list of contacts, what good is this Yes/No field in the user's list of contacts? A Yes/No delete field could be used, but why not delete a contact from the user's list right now instead of in a batch process?

I thought batch processing for interactive work went out a long time ago.

 

[CautionMP]

If you put the field in the Master table, you have conccurent user issues. By placing it in the details table you can rely on the database engine to create the child record (if the relationship between the two fields is setup correctly) regardless of the number of current users, the same as your concept.

If it was me I wouldn't even delete the record. The overhead is going to minimal, deleting the reocord won't decrease the size of the database (without compacting), and if the Yes/No field resides in the detail table I can filter around it.

Since [navy]rpochoda[/navy] started down the road with the Yes/No field, I felt it was important to stick to this concept, after all, [navy]rpochoda[/navy] knows her/his user base much better than you or I.

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[VicRauch]

If the Yes/No field is important, then allow the user to click the Yes/No field on the form, and then in the AfterUpdate event, reset the field to No, put go ahead and process the Add at that moment, rather than it being a batch process.

 

[rpochoda]

Guys, thanks for this fascinating discussion. Unfortunately, I have no way of evaluating any of the arguments in the long run, just whether they accomplish the task in the short run. I'm a self-taught programmer who's been working with Access, VBA and SQL for a few years and can generally figure stuff out, but am never sure if my solutions are optimal.

I liked CautionMP's method because records are added and edited automatically w/o the need for any event processing, though that would be easy enough to do. I decided to run the delete query for purely cosmetic reasons: so that when an employee goes to edit his list the next time, the Y/N field for any contacts he unchecked a previous time will display NULL (grayed) like all the records he had never selected, rather than NO (clear). (CautionMP is right when he talks about my user base -- I'm sure they'd complain that a check mark means Include but that both grayed and clear mean Exclude.) I suspect there are lots of ways to accomplish this programatically, or better yet to turn all the NULL's to NO's, on form open, but then I didn't know that "batch processing for interactive work went out a long time ago." Notice the batch is just for the delete -- adding and editing records in the detail table happens automatically on record update.

FMI, what's the reason to avoid batching in this situation?

Thanks again for any of your thoughts.

 

[VicRauch]

I see the inclusion of event processing the most straight forward for those individuals that will come behind you and maintain the code after you are gone. Although CautionMP's method works, and is very clever, I have tried to stay with the straight forward whenever possible. And I try to stay away from any batch processing for the very reason that was brought up in this discussion: Conflict with multiple users. Please note, that is only batch processing during an interactive session. There are many reasons to do batch processing in the background, after hours, report generation, etc. Just not usually an easy, or in my option, a good thing to do during an interactive session.