Hello.

From what I can gather this should be possible but I really cannot see what is wrong here.

I've used the Reverse Engineer option in DBYSY to create a table in memory. It is called MemDevExEvents.  I have a DBISAMTTable connected to it, tblDevExEventsMem.  The Dastabase setting on tblDevExEventsMem is Memory.  I have tried with a local Session attached and without any Session.

tblDevExEventsMem is attached via a Datasource to a DevEx Scheduler and the events are correctly displayed.

I need to update the remote table if a new record is added to the local table.

I have code in the AfterInsert event of tblDevExEventsMem as follows.

 if DMMain.tblDevExEventsMem.Exists then
 begin
   ShowMessage(tblDevExEventsMem.TableName); // <--- MemDevExEvents
   ShowMessage('tblDevExEventsMem Count = ' + inttostr(tblDevExEventsMem.Recordcount));  //<--- 35
 end;
 //Need to insert a record back to the table, get the resulting autoinc and store it here in ID
 //Set the flag to stop OnPost running as well
 bNewEvent := True;
 with qryUpdateRemote do
 begin
   SQL.Clear;
   SQL.Add('INSERT INTO "tblDevExEvents"');
   SQL.Add('(ActualFinish, ActualStart, Start, Finish, Options, Caption, RecurrenceIndex, ' +
     'RecurrenceInfo, USER_ID, Location, Message, ReminderDate, ReminderMinutes, State, ' +
     'LabelColor, MainAutoInc, CreatedBy, CreatedOn, TypeofEvent, Priority, Outlook_ID, ' +
     'ResourceID)');
   SQL.Add('SELECT ActualFinish,');
   SQL.Add('ActualStart,');
   SQL.Add('Start,');
   SQL.Add('Finish,');
   SQL.Add('Options');
   SQL.Add('Caption,');
   SQL.Add('RecurrenceIndex,');
   SQL.Add('RecurrenceInfo,');
   SQL.Add('USER_ID,');
   SQL.Add('Location,');
   SQL.Add('Message,');
   SQL.Add('ReminderDate,');
   SQL.Add('ReminderMinutes,');
   SQL.Add('State,');
   SQL.Add('LabelColor,');
   SQL.Add('MainAutoInc,');
   SQL.Add('CreatedBy,');
   SQL.Add('CreatedOn,');
   SQL.Add('TypeOfEvent,');
   SQL.Add('Priority,');
   SQL.Add('Outlook_ID,');
   SQL.Add('ResourceID');
   SQL.Add('FROM "Memory\MemDevExEvents"');
 end;
 qryUPDATERemote.ExecSQL;

qryUpdateRemote has a Session that refers to the remote table.  I have inserted ShowMessage at the start of the routine to make sure everything is as I expected.

When I run the ExecSQL command I get the following error:
DBISAM Engine Error # 11010 Table or backup file 'MemDevExEvents' does not exist.

I really cannot see why this happens.

I also wonder if it is possible to return the AutoInc field that is created in the remote table using a sub-query.

I have not included the first field which is an AutoInc in the remote table and an integer in the Memory table.

The fields in Memory\MemDevExEvents and the remote table are identical but I'm not too sure if I can add a null to the first field and then say copy the rest from Memory\MemDevExEvents except for the first field.

My biggest problem is the table not existing.

Thanks.

Robert


IIRC you're trying to mix a local table Memory\MemDevExEvents with a remote table tblDevExEvents and this is not allowed. Either you'll have to get the information from the memory table and build up a string to use in VALUES or you'll have to use TDBISAMTables.

Roy Lambert [Team Elevate]

Your "Memory\MemDevExEvents" is in a local session.
The  "tblDevExEvents" is connected to a remote session.
Your can not cross Session type boundaries in a single SQL
DBISAM Engine Error # 11010 Table or backup file 'MemDevExEvents' does not exist simply indicate that there is no 'Memory\MemDevExEvents' in the remote session.

Or may someone else knows better.

Do it a different way.

for example

1-Have a "tblDevExEvents" DBISAMTable connected to the remote table and open. DO NOT refer to this table with any visual control (Except for checking what is happening)
2-tblDevExEvents.Append
3-Copy all field values from your 'MemDevExEvents' new record to matching tblDevExEvents Fields except any AutoInc Fields in the remote "DevExEvents" table.
4-tblDevExEvents.Post -> tblDevExEvents.AfterPost should have the remote autoinc correctly set.
5-Retrieve the value of the AutoInc field from tblDevExEvents and update the corresponding (ftInteger) field in the local "Memory\MemDevExEvents"

and there are so many other ways I can imagine... Well you'll have to do a bit of research on your own.

The important point as Roy has written is
 
 DO NOT MIX LOCAL AND REMOTE DATASETS IN A QUERY.

I'll print it and stick it to my screen...

Bruno

Thanks again Bruno & Roy.

For some reason I'd thought one of the benefits of using memory tables was to make them available across Sessions.  No idea where I got this from....

I will try Bruno's solution this evening which looks hopeful.