Invalid Argument on TransferText

[Isaac]

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!

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]

Does the spec work if you don't include the MVF column?

 

[Isaac]

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:

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??

 

[theDBguy]

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]

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 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]

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]

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]

I was trying to do a TransferText with a named export spec

both manually, and TT without named export spec, worked

 

[theDBguy]

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 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.

 

[Isaac]

 

[theDBguy]

Are you saying a saved export won't work for you?

 

[theDBguy]

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...

 

[Isaac]

Are you saying a saved export won't work for you?

Not when it doesn't work via code, in T.T.

 

[theDBguy]

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?

 

[Isaac]

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 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]

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]

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.