X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Docmd.trasnfertext Needed Twice For One Access Record, Any Version    
post Sep 10 2019, 06:50 AM

Posts: 669
Joined: 31-August 12
From: London, England

Our legacy system handles tenancies with one tenant and tenancies with joint tenants. Apparently the joint tenant is a separate record. The replacement Access system will have one record for the tenancy with space for the main tenant's name (and other data) and also the Joint tenant's name (and other data).

The legacy system outputs a CSV file with all fields relating to the main tenant. The record's key field is part of this import. If this is a joint tenancy it outputs a second CSV file with just the data fields for the joint tenant. Note that this joint tenant's file contains no key data.

My code successfully imports the tenant. However, the import of the second fails with this error:
Attached File  Capture.PNG ( 11.82K )Number of downloads: 6
The error appears while executing the second DoCmd.TransferText

My code looks like this (edited for visibility)
'        Import the tenant's CSV file
            DoCmd.TransferText acImportDelim, "tblEvictionsInterfaceImport", "tblEvictionsInterface", txtEvictionsInterfaceCSV

            'Check if joint tenant by examining data already on the form
            If txtJointViewingID <> "" Then 'joint tenant's file must be found and imported

'        I have deleted a dozen lines of code which gets the filename of the joint tenant's CSV file

'            Import the Joint Tenant's CSV file
                DoCmd.TransferText acImportDelim, "tblEvictionsInterfaceImportJoint", "tblEvictionsInterface", txtJointEvictionsInterfaceCSV
        end if

After some deep thought I wondered if the first import had moved the file's cursor away from the new record. My reasoning was based on the fact that the second import did not involve any of the record's key data and the error message had neither deleted nor lost any record so I wrote a loop examining each record in tblEvictionsInterface until I found the one just imported and then I executed the DoCmd.TransferText. It failed with the identical message.

I wondered if a Transfer Text always did an Insert on the table, in which case I will have to import to two separate tables and merge the Joint Tenant's data by code.

Please help.
Go to the top of the page
post Sep 10 2019, 09:51 AM

Posts: 669
Joined: 31-August 12
From: London, England

I have sorted things out.
1. After experiments I have established, with 90% certainty, that TransferText always inserts a record in a table
2. Therefore my request how to update a record using TransferText is meaningless. There is no way it can be done.

My workaround was to build a table for the second import of Joint Tenant data and then transfer the fields to the main record one by one.


Go to the top of the page
post Sep 10 2019, 12:28 PM

Posts: 1,003
Joined: 12-November 03
From: Iowa Lot

My practice for imports is to always use a holding table for imported data.
1. This allows inspection/editing, if desired, prior to finalizing the import.
2. You can append or update the target table based on the holding table.

Also, if the imported data is intended to override the data in the target table (or a subset thereof) you can delete the target table data prior to the import.
If the override is conditional, see #2 above and create your update query accordingly.

Whatever works best for you, anyway.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    19th October 2019 - 12:50 PM