DoCmd.TransferText error with Schema.ini import specification

[Guest]

The code below successfully calls two functions that create a schema.ini file
based on user selected file input and an existing table; but when I try to
import the selected file into a new temporary table using the
DoCmd.TransferText command line, I receive the following error... "The text
file specification 'schema.ini' does not exist. You can not import, export,
or link using the specification." Any ideas why?

Function fncImportTextFile(strImportTbl As String, blnRow1FldNames As Boolean)
Dim strFilepath As String, strPath As String, strFile As String
Dim strSpec As String, strTmpTbl As String, strMsg As String

On Error GoTo Err_Handler

strFilepath = GetOpenFile("Please select an INPUT file...", 5)
strPath = Left(strFilepath, Len(strFilepath) - InStr(1,
StrReverse(strFilepath), "\") + 1)
strFile = Right(strFilepath, InStr(1, StrReverse(strFilepath), "\") - 1)
CreateSchemaFile blnRow1FldNames, strPath, strFile, strImportTbl

strSpec = "schema.ini"
strTmpTbl = "_import_" & strImportTbl
DoCmd.TransferText acImportDelim, strSpec, strTmpTbl, strFilepath,
blnRow1FldNames

Exit_Handler:
Exit Function

Err_Handler:
Msg = "Error #: " & Format$(Err.Number) & vbCrLf & Err.Description
MsgBox Msg, vbOKOnly, "Error..."
Resume Exit_Handler
End Function

 

['69 Camaro]

Hi.

DoCmd.TransferText command line, I receive the following error... "The
text
file specification 'schema.ini' does not exist. You can not import,
export,
or link using the specification." Any ideas why?

The Schema.ini file is a file that Jet uses to format the columns of an
external file when using an append query or make-table query. It cannot be
used as an import specification for the TransferText method, because it's
not at all the same thing. If you want to use an import specification for
the TransferText method, then you need to create one first with the Import
Text Wizard.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

I understand the DoCmd.TransferText method doesn't accept the schema.ini as
an import spec. Although, there are references in the "Initializing the Text
Data Source Driver" article
(http://office.microsoft.com/en-us/access/HP01032166033.aspx) that lead me to
believe you can use a schema.ini file as an import specification. But how, if
not by feeding the CreateSchemaFile function to the DoCmd.TransferText
command line?
* On page 12 it says... | ResultTextImport | REG_SZ | imports data from the
external file into the current database.
* On page 13 it says... To read, import, or export text and HTML data, you
need to
create a Schema.ini file in addition to including the Text ISAM info in
..ini file.
* On page 14 it gives an example of what appears to be an export
specification.

If not, is there a way to progrmatically create an import spec?

The end results I want are to automate import of user selected CSVdelimited
text files based on exsiting table configurations that are not visable to the
end user. I want a single form button to initiate import into a temporary
table.

I had started out creating a form/subform based on the MSysIMEXspecs and
MSysIMEXColumns tables but had abandoned that theory after running across the
schema.ini articles because the system tables seemed equally unnintuitive to
non-Access savy users as the Import Wizard is.

My second attempt uses an unbound form consisting of four controls. Two
comboboxes listing importable tables and filetypes, a checkbox for the "First
Row Contains Field Names" option, and a textbox in which I intended to
display specifications relevant to the selected import table.

Below is the relevant code I have behind the Import button.

Function fncImportTextFile(strImportTbl As String, blnRow1FldNames As Boolean)
Dim strFilepath As String, strPath As String, strFile As String
Dim strSpec As String, strTmpTbl As String, strMsg As String

strFilepath = GetOpenFile("Please select an INPUT file...", 5)
strPath = Left(strFilepath, Len(strFilepath) - InStr(1,
StrReverse(strFilepath), "\") + 1)
strFile = Right(strFilepath, InStr(1, StrReverse(strFilepath), "\") - 1)
CreateSchemaFile blnRow1FldNames, strPath, strFile, strImportTbl

strSpec = "schema.ini"
strTmpTbl = "_import_" & strImportTbl
DoCmd.TransferText acImportDelim, strSpec, strTmpTbl, strFilepath,
blnRow1FldNames

'...code that adds a sort field and fills-in missing sequential field values
'...code that handles import errors
'...code that appends modified rows to the previously existing table
' and delete the temporary table

End Function

Any help is appreciated.

 

['69 Camaro]

Hi.

Although, there are references in the "Initializing the Text
Data Source Driver" article
(http://office.microsoft.com/en-us/access/HP01032166033.aspx) that lead me
to
believe you can use a schema.ini file as an import specification.

There isn't any mention of import specifications in that article, nor that a
Schema.ini file name can be exchanged for the import specification name
parameter in the TransferText method.

That article leaves out important information and can be confusing for those
who aren't experienced Access developers. For example, it doesn't bother
telling the reader that Jet automatically creates the Schema.ini file in the
directory when exporting data to a text file via a query, so the developer
doesn't even have to create a Schema.ini file for imports, either
programmatically or manually. That's a fairly important point to leave out.
Don't you think?

But how, if
not by feeding the CreateSchemaFile function to the DoCmd.TransferText
command line?

Manually import the text file with the Import Text Wizard, create an import
specification while doing so, and name it MyImportSpec. When you use the
TransferText method in VBA code, use this name for the import specification
name parameter:

DoCmd.TransferText acImportDelim, "MyImportSpec", "tblTemp",
"C:\Input.txt", True

For more information on the TransferText method, please see Access online
help.

is there a way to progrmatically create an import spec?

Yes. However, you don't want to go to all that trouble. It's much easier
to manually create it the first time with the Import Text Wizard, then reuse
it for subsequent imports with the TransferText method.

My second attempt uses an unbound form consisting of four controls. Two
comboboxes listing importable tables and filetypes, a checkbox for the
"First
Row Contains Field Names" option, and a textbox in which I intended to
display specifications relevant to the selected import table.

I know you're trying to make it easier for the user, but don't bend over
backwards to allow the user to become the database developer. The user
shouldn't be selecting which import specification or file type to use for
data import. The user should be able to select the file names and their
paths for the data imports, press a button, and your code will do the rest,
because you've already done the preliminary work: designed the import
tables, defined the import specs, and set up the order the tables need to be
processed.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

Thanks, Gunny for the explanation of missing and misleading info in the
shema.ini article. I also whole heartedly agree the end user should not need
developer "know-how"; that's the reason behind this quest.

I will manually preconfigure the import specs and remove the filetype
combobox on my import form. But I still need a simple way to let the end user
know ahead of time the column names and sequence required for each import
spec.

 

['69 Camaro]

Hi.

But I still need a simple way to let the end user
know ahead of time the column names and sequence required for each import
spec.

If the sequence of the columns in the text file matters, then you're
fine-tuning your application too much. In a relational database, the order
of the columns in the tables is irrelevant, although one usually sees the
primary key columns as the first ones listed, but that's only by user
convention. The database engine doesn't care.

If the user needs to see the names of the columns during import, then this
indicates that the user is unsure that he's importing the correct file into
the correct table. This is a recipe for disaster. You need to automate the
application so that it looks for the correct text file in the correct
directory without user input.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.