Tables:Creating Temporary Tables for Local Use to Upload to the Network

Newsgroup Thread

I need some help setting up a temporary table in my application. What I need is a table on the user's local workstation that will store the data that they enter in. After they are done (and verify the data) I need to copy the data to the "master" table on the network server. Then the local table can be cleared (as long as it's empty, I don't need to delete it). Also, when the user answers the first question in the program (social security #) how can I have Access check that value for duplicate values on the "master" table? Thanks for any help, I've gotten ALOT of help from people on this group over the last few weeks, and I can't say how much I appreciate it.

                                        Greg Stritmater
                                        senip@j51.com

Message 2 in thread From: Greg Stritmater (senip@j51.com) Subject: Re: Please help with temp tables Newsgroups: comp.databases.ms-access Date: 1997/04/13

In article <E8L2us.D05@nonexistent.com>, Greg Stritmater wrote:

------------------------snip------------------

Well, I've gotten half of my problem solved. I've managed to clear the temp table at the end of the program, but I still need help transfering the record to the master table. Can someone show me what's required to open the 2 tables (with DAO i imagine) then transfer the record from the temp table to the master? I can't imagine this is very complicated, but dealing with recordsets is still pretty confusing to me right now. I'm almost there, please help me out! Thanks

                                Greg Stritmater
                                senip@j51.com

Message 3 in thread From: Chuck Lyle (chucklyle@earthlink.net) Subject: Re: Please help with temp tables Newsgroups: comp.databases.ms-access Date: 1997/04/14

Hi Greg,
The simplest way would be to create an append query. Assuming (yes I'm still doing that) you only have one record in your temp table and it's the one you want to transfer. You can call the query from code or a macro.

Hope it helps. ------
Chuck Lyle
Robertshaw Controls
The New Grayson
chucklyle@robertshaw-grayson.com
chucklyle@earthlink.net

The sight has been updated, Visit our web site at
http://www.robertshaw-grayson.com

Message 4 in thread From: Greg Stritmater (senip@j51.com) Subject: Re: Please help with temp tables Newsgroups: comp.databases.ms-access Date: 1997/04/16

In article <335278EA.479@earthlink.net>, Chuck Lyle wrote: >Hi Greg,
>The simplest way would be to create an append query. Assuming (yes I'm >still doing that) you only have one record in your temp table and it's >the one you want to transfer. You can call the query from code or a >macro.
>
>Hope it helps.
>

That did the trick. Thanks alot. If you would help me out one more time, I'd greatly appreciate it. I need to check for duplicate Social Security # entries on the master table immediately after the user enters it in on the local table. This way, it'll insure a successful transfer at the end of the program. Again, thanks for the help.

                                Greg Stritmater
                                senip@j51.com

Message 5 in thread From: Tom (tom.wolfenden@nOsPaMstate.mn.us) Subject: Re: Please help with temp tables Newsgroups: comp.databases.ms-access Date: 1997/04/16

Hi Greg,

Sub SSN_AfterUpdate() Dim DB as Database Dim rs as Recordset Set db = DBEngine(0)(0) Set rs = db.OpenRecordset("tblMasterTable", DB_OPEN_TABLE) rs.index = "PrimaryKey" rs.Seek "=", Me![SSN] If rs.NoMatch then ' This SSN is a new one Else ' This SSN is already in master End If rs.Close End Sub

This is the sub for the after update property of the Social Security number on a form. It assumes that SSN is the name of the Social Security Number field and that SSN is the primary key of the table. It also assumes that the name of the master table is tblMaster. If any of these assumptions are wrong (most likely the table name) then just change them. There are other ways of doing this, if this code does not work for you,(possible problem = attached tables) let me know.

Tom.

Remove the nOsPaM from my address to reply. Sorry its a pain but its easier than me dealing with the endless assult from the spambots.

Message 6 in thread From: Chuck Lyle (chucklyle@earthlink.net) Subject: Re: Please help with temp tables Newsgroups: comp.databases.ms-access Date: 1997/04/14

Hi Greg,
What I am using is a bound form that I unbind for adding a new record to prevent updating the Table until all neccessary fields are entered and data is checked. This allows the cleint to cancel at any time during data entry without updating the Table. Following is what I am using for verification on the unbound form. When the client enters the CARNum and tries to exit the field, this code is in the OnExit event so the client can't get around it (I hope :-))

Dim mydb As Database, myworkspace As WorkSpace Dim headerinf As Recordset Dim caridinf As String, carnuminf As String, response As Variant DgDef2 = MB_OKCANCEL + MB_ICONQUESTION + MB_DEFBUTTON1 caridinf = Me!Field1 carnuminf = Me!Field2 Set myworkspace = dbengine.workspaces(0) Set mydb = myworkspace.databases(0) Set headerinf = mydb.OpenRecordset("Table1", DB_OPEN_DYNASET) param1 = "CARID = " & Chr$(34) & caridinf & Chr$(34) & " And CANum = " & Chr$(34) & carnuminf & Chr$(34) headerinf.MoveFirst headerinf.FindFirst param1 If headerinf.nomatch Then 'Code to proceed here Else response = MsgBox("CAR Number " & carnuminf & " has already been used for " & caridinf & Chr$(13) & Chr$(10) & "Choose OK to edit this record or CANCEL to discard and return to Form", DgDef2, "Record Exists") If response = 1 Then 'Code sending client back to the field Else 'Code to cancel entry and rebind form End If End If

When all data entry is complete and verified, I use code to update the table and re-bind the form and go back into browse/edit mode. You could use an Append query to append your temp table to the master, and a delete query to delete the record prior to doing any more data entry.

Hope this helps.