Invalid Argument on TransferText (1 Viewer)

Isaac

Lifelong Learner
Local time
Yesterday, 18:41
Joined
Mar 14, 2017
Messages
4,339
I am getting Invalid Argument on TransferText. Unfortunately this table does have multi-valued fields--only because it is linked to a Sharepoint list with People fields, which I could not control.

If I run TranferText with no saved specification name, it works fine. But I had saved a spec with special delimiter and text quoting characters, now it barfs. :(
Is this just another thing I can't do with MV fields for some odd reason?? GRRRRRR -10 for sharepoint!

Code:
Sub BackupSharepointTables()

Dim strPathDist As String, strPathReview As String, strLine As String, x As Long, fld As DAO.Field
strPathDist = "U:\folder\BACKUPS\SP TABLE BACKUPS\DistributionPortal_" & Format(Now, "YYYY-MM-DD HHMMSS") & ".txt"
strPathReview = "U:\folder\BACKUPS\SP TABLE BACKUPS\ReviewAndChallengePortal_" & Format(Now, "YYYY-MM-DD HHMMSS") & ".txt"

DoCmd.TransferText acExportDelim, SpecificationName:="spec_Backup_ExportDIST", TableName:="Distribution Portal", FileName:=strPathDist, hasfieldnames:=True
DoCmd.TransferText acExportDelim, SpecificationName:="spec_Backup_ExportRCP", TableName:="RCP", FileName:=strPathReview, hasfieldnames:=True

MsgBox "Done", vbExclamation, " "

End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:41
Joined
Oct 29, 2018
Messages
14,560
Does the spec work if you don't include the MVF column?
 

Isaac

Lifelong Learner
Local time
Yesterday, 18:41
Joined
Mar 14, 2017
Messages
4,339
Does the spec work if you don't include the MVF column?
I tried to remove fields from the export when saving the spec, but I don't see the little checkboxes to the right as I have seen before.

So, I tried selecting the fields, as they are stacked, about 3 at once, like this:

1603991362785.png


When I hit Delete on my keyboard, I get this scary warning--Does this just refer to a system table somewhere referring to my export spec? It doesn't refer to real records in my table, does it??

1603991398480.png
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:41
Joined
Oct 29, 2018
Messages
14,560
I tried to remove fields from the export when saving the spec, but I don't see the little checkboxes to the right as I have seen before.

So, I tried selecting the fields, as they are stacked, about 3 at once, like this:

View attachment 86215

When I hit Delete on my keyboard, I get this scary warning--Does this just refer to a system table somewhere referring to my export spec? It doesn't refer to real records in my table, does it??

View attachment 86216
How about trying to create a new View without the MVF column, create a spec for it, and see if that works?
 

Isaac

Lifelong Learner
Local time
Yesterday, 18:41
Joined
Mar 14, 2017
Messages
4,339
How about trying to create a new View without the MVF column, create a spec for it, and see if that works?

I might try that, except ... I already know that wouldn't be sufficient for my backup coverage.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:41
Joined
Oct 29, 2018
Messages
14,560
I might try that, except ... I already know that wouldn't be sufficient for my backup coverage.
Well, right now, we're just trying to isolate potential causes to figure out the problem you're having. I wasn't trying to suggest an alternate approach - just some troubleshooting steps...
 

Isaac

Lifelong Learner
Local time
Yesterday, 18:41
Joined
Mar 14, 2017
Messages
4,339
Well, right now, we're just trying to isolate potential causes to figure out the problem you're having. I wasn't trying to suggest an alternate approach - just some troubleshooting steps...
Gotcha. I did create another list without MVF's and still got the error. But then again, I think about it....technically, Sharepoint's built-in "Modified by", "Created by", etc., are probably qualifying for being MVF anyway, too.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:41
Joined
Oct 29, 2018
Messages
14,560
Gotcha. I did create another list without MVF's and still got the error. But then again, I think about it....technically, Sharepoint's built-in "Modified by", "Created by", etc., are probably qualifying for being MVF anyway, too.
Well, that sucks. Okay, (I don't remember) are you able to do the export manually? I just gave it a try and was able to manually export but got the same error when using TransferText. I'll keep trying...
 

Isaac

Lifelong Learner
Local time
Yesterday, 18:41
Joined
Mar 14, 2017
Messages
4,339
I was trying to do a TransferText with a named export spec

both manually, and TT without named export spec, worked
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:41
Joined
Oct 29, 2018
Messages
14,560
Well, that sucks. Okay, (I don't remember) are you able to do the export manually? I just gave it a try and was able to manually export but got the same error when using TransferText. I'll keep trying...
Okay, this worked for me. I manually exported the linked List and saved the Export Steps. Then, I used RunSavedImportExport and it worked!
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:41
Joined
Oct 29, 2018
Messages
14,560
I was trying to do a TransferText with a named export spec

both manually, and TT without named export spec, worked
I manually exported using an export spec too, which I also used in the TransferText command. Like I said though, the manual step worked, but the code didn't.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:41
Joined
Oct 29, 2018
Messages
14,560
Quick question... Do you need to export "ALL" the columns from the List?

I just created a query based on my linked List including only some of the columns and the TransferText work. Perhaps you could experiment with that.

Just a thought...
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:41
Joined
Oct 29, 2018
Messages
14,560
Not when it doesn't work via code, in T.T.
Hmm, not sure I understand the difference. A saved export works via code using RunSavedImportExport, which should have the same end result at TransferText. What do you consider as a significant difference between the two that only one is acceptable? Just curious...

Besides, I have conducted a few more experiments since I posted about using the saved export approach and also posted my results above. I hope maybe one of them could also help?
 
Last edited:

Isaac

Lifelong Learner
Local time
Yesterday, 18:41
Joined
Mar 14, 2017
Messages
4,339
I think the problem is among all the back and forth I inadvertently missed post #10 entirely! Thanks. On this specific task I went a different direction (timelines are crazy tight on this thing), but, pretty soon I'll have to do this again.......and I will keep this in mind. the RunSavedImportExport. Thanks again
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:41
Joined
Oct 29, 2018
Messages
14,560
I think the problem is among all the back and forth I inadvertently missed post #10 entirely! Thanks. On this specific task I went a different direction (timelines are crazy tight on this thing), but, pretty soon I'll have to do this again.......and I will keep this in mind. the RunSavedImportExport. Thanks again
No worries. By "different direction," you mean you weren't able to fix the original problem, correct? I did a search and couldn't find a fix for it either. The closest I got was the experiment I outlined in post #14. Cheers!
 

Isaac

Lifelong Learner
Local time
Yesterday, 18:41
Joined
Mar 14, 2017
Messages
4,339
No worries. By "different direction," you mean you weren't able to fix the original problem, correct? I did a search and couldn't find a fix for it either. The closest I got was the experiment I outlined in post #14. Cheers!
Correct. I didn't see your RunSavedImportExport until now, so solved it a different way, exporting to text using no named spec at all. Next time I know :)
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:41
Joined
Oct 29, 2018
Messages
14,560
Correct. I didn't see your RunSavedImportExport until now, so solved it a different way, exporting to text using no named spec at all. Next time I know :)
Cool. Good luck with your project.
 

Users who are viewing this thread

Top Bottom