UtterAccess.com
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    
 
   
JohnvanSomeren
post Sep 10 2019, 06:50 AM
Post#1



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


Hi
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)
CODE
'        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.
John
Go to the top of the page
 
JohnvanSomeren
post Sep 10 2019, 09:51 AM
Post#2



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.

Thanks
John

Go to the top of the page
 
kfield7
post Sep 10 2019, 12:28 PM
Post#3



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    20th October 2019 - 11:00 PM