DoCmd.TransferText acExportFixed

[rgreene]

I am trying to use the DoCmd.TransferText acExportFixed command to create a text file. but everytime I run the code I get the error Run-time error '2511':
The action or method requires a Specification Name argument.

I have created the Spec file by doing the export clicking on the advanced tab creating my field sizes and saving the spec file (ClaimsExportSpecs). If I run the export manually everything works fine.

I've also tried the Schema.ini file and put it in the same directory I'm putting my export file. The Schema.ini gives me a Run-time error '424': Object Required error.

My code is:
DoCmd.TransferText acExportFixed, ClaimsExportSpecs, "qryExport", "C:\Export Test\ExportTest.txt"

and/or (trying both ways)

DoCmd.TransferText acExportFixed, Schema.ini, "qryExport", "C:\Export Test\ExportTest.txt"

doing an acExportDelim works fine but I need it in fixed format.

Thanks in advance!
Rick

 

[vbaInet]

Perhaps the import specification doesn't exist.

If you don't have too many import speicifcations try this:

currentproject.ImportExportSpecifications.Count

Once you know the number use it in this:

currentproject.ImportExportSpecifications(0).Name

Use, 0, 1, 2... etc until the number from count - 1. Or do a For Each loop.

 

[rgreene]

Thanks vbaInet,

When I run the first code I get a "Compile error: Invalid use of property" and the .Count is highlighted.

I marked out the rest of my code and I put that coding in my event procedure, is that where it should go?

'CurrentProject.ImportExportSpecifications(0).ClaimsExportSpecs

'DoCmd.TransferText acExportFixed, ClaimsExportSpecs, "qryExport", "C:\Users\RGreene.LINNCNTY\Documents\ExportTest.txt"

CurrentProject.ImportExportSpecifications.Count

Rick

 

[vbaInet]

That code was just for you to test to see the names of your import specifications. Do an Msgbox on the Count version and tell me how many it shows.

 

[rgreene]

Sorry I'm not real good at this stuff. I've tried the message box thing but I can't figure out the correct code. I have done msgbox with If Then but I'm not sure how to do it for what you are requesting.

 

[vbaInet]

Simply

Msgbox CurrentProject.ImportExportSpecifications.Count

Run the code and tell me what the number is.

 

[gemma-the-husky]

maybe you need "ClaimsExportSpecs"

just having ClaimsExportSpecs will look for a variable of that name

I am assuming your paramaters are in the right order.

 

[rgreene]

When I run that, my message box displays "CurrentProject.ImportExportSpecifications.Count"

 

[rgreene]

My bad it displays 0

 

[vbaInet]

Then it means you don't have any import specs.

 

[rgreene]

Not sure if it matters but this is an export.. If I do it manually on the export I click Advanced then click Specs, and the ClaimsExport is there and all my Field names and sizes are set. I even found an article on copying them from one Database to another. I went through that process and the new empty database had the ClaimsExport listed with all the fields and sizes.

 

[rgreene]

I tried changing it to ClaimsExportSpecs and ClaimExportSpecifications per gemma-the-husky's post but neither of them worked either. When I hover over the ClaimsExport in the code I get ClaimsExport=Empty. So I would agree that it doesn't think there is a spec file, but there is (when I do it maually) or it's not in the correct place or something.

 

[vbaInet]

How did you get the tables, queries etc into your new db?

You need to import your specs.

* Run the Import Wizard
* Select the db that contains the import specs
* Click the Options button and tick the Import Specs checkbox
* Ok > Ok > Run Run Run

That should get your import specs into your new db.

 

[rgreene]

I only did the new db as a test to see if it found anything to import. I was like you in thinking that the current db didn't think there was a spec file. So I thought if I tried to import it to a different db either it wouldn't import the spec which would lead me to believe that it isn't there or if it did import it then that would confirm that the spec file was there. Since it did import to the new empty (no tables no nothing) db then that means there actually is a spec file.
FYI The steps you listed above are the ones I did earlier.

 

[vbaInet]

There's something you haven't done correctly. Either you haven't created the spec or didn't import it from the right db.

The code I gave you tells you exactly how many specs are in your db.

Have a look at this and follow both instructions:

http://support.microsoft.com/kb/208991

 

[rgreene]

OK I moved all the tables, queries, forms from the original db over to the new one and now when I run the MsgBox CurrentProject.ImportExportSpecifications.Count it returns a count of 1. So to me that means it's seeing 1 spec (which the other database didn't). However I still get the error The action or method requires a Specification Name argument.

 

[vbaInet]

Good! Now use the second code I gave you and tell me what it displays.

 

[rgreene]

it says "Claims Export"

 

[vbaInet]

That is the name of your Import Spec.

 

[rgreene]

vabInet,

THANK YOU so much for putting up with me and my lack of knowledge. I finally got it. I tired it with the Claims Export but that didn't work so I put the ClaimsExports in "" and that did it. So I have
DoCmd.TransferText acExportFixed, "ClaimsExport", "qryExport", "C:\Users\RGreene.LINNCNTY\Desktop\ExportTest.txt"

Please understand that the help you and everyone else provides on this forum is a gods send for those of us who have to do these things and wish we were more knowledgeable.

THANK YOU again. I truely appreciate you sticking with me!!

Rick