TransferText Macro: (Export Delimited) object-naming rules error on File Nae

[Alan Scott]

Hello... I've spent hours trying to guess what Access is complaining about. I have a Macro setup to transfer a table to a delimited text file using an export spec i created earlier.
I have specified these exact File Names (with and without the extension):

• C:\Users\ScottAl\Documents\AccessExports\MT_AL_Chk _Data_GS_2012
• C:\Users\ScottAl\Documents\AccessExports\MT_AL_Chk _Data_GS_2012.txt

Either way, i keep getting these messages, respectively:

• The object name 'MT_AL_CHK_DATA_GS_2012' you entered doesn't follow Microsft Office Access object-naming rules.
• The object name 'MT_AL_CHK_DATA_GS_2012#txt' you entered doesn't follow Microsft Office Access object-naming rules.

When i check what the rules are - i'm not violating any of them!
Here are the all of the parameters:

• Transfer Type: Export Delimited
• Specification Name: MT_AL_CHK_DATA Export Spec
• Table Name: MT_AL_CHK_DATA
• File Name: C:\Users\ScottAl\Documents\AccessExports\MT_AL_Chk _Data_GS_2012.txt
• Has Field Names: Yes
• HTML Table Name:
• Code Page: 20127: US-ASCII

What am i doing wrong?

[ItsMe]

Have you considered using VBA to call your saved export procedure?

DoCmd.TransferText acExportDelim, "MT_AL_CHK_DATA Export Spec", "MT_AL_CHK_DATA", "C:\Users\ScottAl\Documents\AccessExports\MT_AL_Chk_Data_GS_2012.txt"

[Alan Scott]

Sorry for the delayed response. I am not at all experienced with VBA, but i think it's time i dug into that. I'll see what i can do to make it work. Thanks for the DoCmd code!

[Alan Scott]

Thank you ItsMe for getting me started.
I was able to take that DoCmd code and successfully execute it. With more checking in the internet for pointers, i have expanded it to successfully run it as shown below. But i tried to make the Company and Year strings to be run-time parms by doing this: Sub ChkData(Company As String, Year As String) and removing the Dims and Inits to Company and Year. When i do that, however, i can no longer run it from the MS Visual Basic (Module) window in Access - it simply does not show up as a choice - even tho the module is listed. Once I click the > button ChkData does not appear - whether or not the module name is the same as the sub name (not sure if that matters for subs like it seems to for funcs).
So, in summary, here, below, is what does work. I'm wonder how to convert Company and Year to run-time parms then be able to run the thing from the Module window.
Sub ChkData()
Dim Path As Variant
Dim CheckPrefix As String
Dim CheckRec1 As String
Dim CheckRec2 As String
Dim CheckRec3 As String
Dim PathFile1 As Variant
Dim PathFile2 As Variant
Dim PathFile3 As Variant
Dim Company As String
Dim Year As String
Dim Ext As String
Path = "C:\Users\ScottAl\Documents\$Work\#Client\Acce ss\"
CheckPrefix = "MT_AL_Chk_"
CheckRec1 = "Data"
CheckRec2 = "Ded"
CheckRec3 = "Hrs_Ern"
Company = "GS"
Year = "2012"
Ext = ".txt"
PathFile1 = Path & CheckPrefix & CheckRec1 & "_" & Company & "_" & Year & Ext
PathFile2 = Path & CheckPrefix & CheckRec2 & "_" & Company & "_" & Year & Ext
PathFile3 = Path & CheckPrefix & CheckRec3 & "_" & Company & "_" & Year & Ext

DoCmd.TransferText acExportDelim, CheckPrefix & CheckRec1 & " Export Spec", CheckPrefix & CheckRec1, PathFile1, True
DoCmd.TransferText acExportDelim, CheckPrefix & CheckRec2 & " Export Spec", CheckPrefix & CheckRec2, PathFile2, True
DoCmd.TransferText acExportDelim, CheckPrefix & CheckRec3 & " Export Spec", CheckPrefix & CheckRec3, PathFile3, True
End Sub

[ItsMe]

I don't know what run-time parms are. Run Time is the your application running. Racking my brain here ummm. You write your code, that is I believe development time, you ask Access to compile the code you wrote in the IDE, that is compile time. The user interacts with your compiled code, that is Run Time. Run Time does not understand VBA the way you wrote it. The compiler runs through what your wrote and re-writes it, so to speak, so it will function in a Run Time environment.

When you declare a variable, declare it in your subroutine. This will allow your sub routine to use it. The declaration reserves a space in memory for the duration of the routine.
'Declaration
Dim CheckPrefix As String
Dim CheckRec1 As String
Dim CheckRec2 As String
'Initialize
CheckPrefix = ""
CheckRec1 = ""
CheckRec2 = ""

If you make these declarations in a class nodule's header, the variables or objects will be available everywhere in the class. Declarations in a form's header should be initialized in the form's load event.

Functions can receive values from variables passed to them from other functions or subs.

Public Function ChkData(Company As String, Year As String)
msgbox "Company = " & Company & "Year = " & Year
end function

You can call this function from a sub within its class module (or if the function is in a general module from anywhere) like this

Call ChkData("ACME", "1954")

[rpeare]

if by run time parameters you mean they are coming from a form you can do:

instead of:

Company = "GS"
Year = "2012"

have

Company = [Field that has company name]
Year = [Field that has the year]

so let's on your form you have a combo box that has the company ID in the first column and the company name in the second column but the bound column of that combo box is column 1, let's call that control cbo_Company, your company definition in your code would be something like

Company = cbo_Company.column(1)

[Alan Scott]

My issue is now solved. Thank you ItsMe for getting me started. RPeare, I have not ventured into the realm of Forms yet, but i am looking forward to it. I now have something in place now that is working. Most likely not the best way to do it but it's getting the job done. There are 3 modules that i am running from a macro.
Due to the 2 gig limit of Access, i have had to set up 19 different databases to handle the various combinations of Company and check Year. Each needs to export 3 very large files to text. The files contain hundreds of thousands to millions of rows so i'm right up against the limit.
From the Macro, i have module 1, called Global, that declares 2 public variables for gCompany and gYear.
module 2, called funChkParms are coded to receive their particular Company & Year parms. (that's what i meant by run-time parms)
module 3, called funExportChecks, does the bulk of the work of establishing the transfer variables and running the DoCmd to xfer the 3 files.
To my amazement - it all actually works. I had to import all of this from the one db to the others but now, whenever i have to regenerate these files - they all get done as part of the overall macro i run. So, just for the record, here are those 3 working modules:
'Start----------------------------------------------------
'module name=Globals
Option Compare Database
Option Explicit
Public gCompany As String
Public gYear As String
'module-name=funChkParms------------------------------------
'A company and a year variable are passed in via a macro
Public Function ChkParms(Company As String, Year As String)
gCompany = Company
gYear = Year
'MsgBox "Company = " & gCompany & " Year = " & gYear
End Function
'next-module-name=funExportChecks---------------------------
Public Function ExportChecks()
Dim Path As Variant
Dim CheckPrefix As String
Dim CheckRec1 As String
Dim CheckRec2 As String
Dim CheckRec3 As String
Dim PathFile1 As Variant
Dim PathFile2 As Variant
Dim PathFile3 As Variant
Dim Ext As String
On Error GoTo ErrHandler:
Path = "C:\Users\ScottAl\Documents\$Work\#Ceva\Access \"
CheckPrefix = "MT_AL_Chk_"
SpecSuffix = " Export Spec"
CheckRec1 = "Data"
CheckRec2 = "Ded"
CheckRec3 = "Hrs_Ern"
Ext = ".txt"
PathFile1 = Path & CheckPrefix & CheckRec1 & "_" & gCompany & "_" & gYear & Ext
PathFile2 = Path & CheckPrefix & CheckRec2 & "_" & gCompany & "_" & gYear & Ext
PathFile3 = Path & CheckPrefix & CheckRec3 & "_" & gCompany & "_" & gYear & Ext

DoCmd.TransferText acExportDelim, CheckPrefix & CheckRec1 & SpecSuffix, CheckPrefix & CheckRec1, PathFile1, True
DoCmd.TransferText acExportDelim, CheckPrefix & CheckRec2 & SpecSuffix, CheckPrefix & CheckRec2, PathFile2, True
DoCmd.TransferText acExportDelim, CheckPrefix & CheckRec3 & SpecSuffix, CheckPrefix & CheckRec3, PathFile3, True

MsgBox "Transfers Successful"
Exit Function

ErrHandler:
MsgBox "Run-Time Parms: Company=" & gCompany & " Year=" & gYear & vbNewLine & _
"Path=" & Path & vbNewLine & _
"CheckPrefix=" & CheckPrefix & vbNewLine & _
"SpecSuffix=" & SpecSuffix & vbNewLine & _
"CheckRec: 1=" & CheckRec1 & " 2=" & CheckRec2 & " 3=" & CheckRec3 & vbNewLine & _
"PathFile1=" & PathFile1 & vbNewLine & _
"PathFile2=" & PathFile2 & vbNewLine & _
"PathFile3=" & PathFile3 & vbNewLine & _
"Ext=" & Ext
End Function
'End-------------------------------------------------------

[ItsMe]

Glad it works. Welcome to the forum!