Unanswered: Update does not save Recordset to table in Access
I set up an Activity Table (TestTable) which logs various details as I open/close the database by calling the Function below. This worked ok until I wanted to copy the database to a laptop for remote use and then back to the office PC with this added to the Activity Table. I read that you cannot copy easily an open database so my solution was to produce a command button which opens another database (CopyDatabase) then close itself (MainDatabase). This CopyDatabase copies the MainDatabase(now closed)to a networked laptop then re-opens the MainDatabase and closes itself. After the evening on the laptop the same button in the MainDatabase is used (with flags in a table to show which way to copy)to now copy the MainDatabase from the laptop to the PC with the similar procedure of opening the CopyDatabase, closing the MainDatabase, copying the MainDatabase from laptop to PC, re-opening the MainDatabase and closing the CopyDatabase.
The procedure works and the Activity table is updated to show that the copy to the laptop is completed but there is no more entries in the Activity Table to show it copied back to the PC or even opening and closing.
For testing I added the Message Boxes which show the activities being added to the table at every step but they are all lost when the table is read at the end of the procedure.
Any ideas?? I have been Googling for a week now with no success.
Dim dbs As Database
Dim rstTest As Recordset
Set dbs = CurrentDb()
Set rstTest = dbs.OpenRecordset("TestTable", dbOpenTable)
MsgBox "Data in recordset before with = " & rstTest!Activity & vbCrLf & _
"In Prog" & CurrentDb.Name
let me admit that I'm not really clear, as your post has quite a bit of back and forth; but I definitely get the jist;
first one can copy/paste a db that is open. in the simple sense. i.e. rt click , copy, and then paste somewhere else... I copy actively open BE files sitting at a server all the time so that I can work in a copy and not in the real db.
but I think your issue is self made replication. one can not 'copy' data from an exterior db into the primary db when it is open. what you do, generically speaking, is do an AppendQuery. the exterior db can link to the primary db tables - at any time with no interruption. gather the new records via a query of those that are flagged as new so you have a recordset of just new records in the exterior db; and then make an appendquery of this query that appends to the primary db's table. this can be done while the db is actively open ....
I trust we are talking about a properly split db; if the db is unsplit - and there is a user in it then this should not be done. the db must be split.
Yes when I read over that I thought it was complicated and I knew what I meant!!!
The actual Copy Database Procedure did work. I even built into the CopyDatabase a test to see if MainDatabase.ldb exists in which case someone else is atached to the MainDatabase, I then give a warning to close any other user or a Cancel choice. There are two people in the Office and they use MailMerge which uses the .ldb file. I do want to make the process automatic as that may have trouble copying the Database manually.
My problem is the the Activity Table (TestTable) does not get the new records added (including the activity - strAct) even though the MsgBox lines I added to test appear to show the records being added.
This Activity Table works fine for normal Open / Close the Database but fails when I use the CopyDatabase Routine.
This is how I Open / Close the 2 Databases in case it helps.
RealCurDir is the directory where MainDatabase (really TT_Marshal) and CopyDatabase (really DatabaseTransfer) sit.
This is in the MainDatabase
DatabaseTransfer = RealCurDir & "DatabaseTransfer.mdb"
MainDatabase = RealCurDir & "TT_Marshal.mdb"
' Create new instance of Microsoft Access.
Set AppAccess = CreateObject("Access.Application")
' Open database in Microsoft Access window.
Then the CopyDatabase includes the code :-
FileCopy RealCurDir & "TT_Marshal.mdb", LaptopPath & "TT_Marshal.mdb"
Then similar code as at the top to Close the CopyDatabase and re-open the MainDatabase
That sounds just as bad. . . . Remember that the problem is the the Activity_TestTable(strAct) Fuction fails to update the Activity Table (TestTable) when I use the Copy Database routine from a Cmd Button.