I have done this many times in the past. What I recommend, depending on the Access version you are using, is to switch to ADO, it performs much better than ODBC. But the technique I use works either way.

Rather than using transferdatabase, Create a recordset object and loop throught it, for each record retreived create an ‘INSRT INTO’ statement and the use a command object for the AS400 (Database object under ODBC), and execute the SQL String. This performs rather well, although large recordsets will take some time. This allows you add in validations and filters in one place and the AS400 only has to issur the updates.

I haven’t done this in some time, but I can throw together an example if you need it.

Look into Client Access remote command services.
Here is what you basically will be doing.

1) Create a data transfer session to the AS/400
2) Save it on your pc
3) Create a .bat file invoking that transfer session
4) Save that on your pc
5) call the .bat file from the AS/400.

You must have the client access remote command service running on the pc. Look at the windows services and there is a client access remote command service that does not strart automatically, you must start that automatically to accept commands from the As/400.

YOu can then start the command to executer the .Bat file from the AS/400 job schduler on a nightly basis.

Thanks
Malik