mSysImexSpecs help

[DS]

Hi all,

First, I researched my issue first on google to no avail....

OK, so I'm creating a tableDef in VB code. This tableDef is actually a
linked tab-delimited file. I am creating the link first, then using a
'SELECT INTO' SQL command to insert all of the text records into a
tempTable. Then using the tempTable to update records in another table.

Since speed is an issue, I can not open the text file, read each line, do
a split, then insert records into the tempTable one at a time, it must be
done with a SELECT INTO.

The code is:

tempDB.CreateTableDef(tempTbl)
tempTbl.Connect=Text;DSN=tabDelimited Link
Specification;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=
437;DATABASE=D:\My Documents\PROJECTS\Temp
tempTbl.TableDataSource="filename.tab"
tempDB.TableDefs.Append tempTbl

At the APPEND attempt, it return the 'Link Specification does not
exist....'

It does. It is visible in the mSysImex tbls. I can use the 'Linked Table
Manager', choose a file to import, pick the 'Link Specification' out of
the dropdown-box, click 'Finish' and it links with not problem. The
specification was originally created using the Link wizard.

I cannot use the spec programatically, only throught the linking
'wizard'.

Any ideas,


TIA

DS

 

[John Nurick]

Hi DS,

I've never tried this myself, but googling for "schema.ini tabledef
connect specification text group:microsoft.public.access.*" throws up
some useful-looking suggestions (in particular to use schema.ini instead
of an import specification).

 

[DS]

Hi DS,

I've never tried this myself, but googling for "schema.ini tabledef
connect specification text group:microsoft.public.access.*" throws up
some useful-looking suggestions (in particular to use schema.ini
instead of an import specification).

John,

I tried schema.ini, or should say am trying, again. I just don't
understand because it never works. All of the pages at MSDN say the
schema.ini must be in the same folder as the text file, which it is. But
that's all the page's say. It seems like it just ignores it anyway.

From everything I've read, it never says that you have to tell it to use
schema.ini somehow. The text file contains column names in the first row,
and the schema indicates that, yet it takes the first row and just takes
all the column names and adds them all together with an _ between them
and makes that the only column. It does the same thing with the data.

DS

 

[John Nurick]

The least bad schema.ini documentation I've found is at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetsdk_98.asp
AFAIK schema.ini is used if it exists in the folder and contains an
entry for the filename in question: is it possible that you haven't
included the appropriate [filename.txt] line in schema.ini?

If the worst comes to the worst you can use DoCmd.TransferText acLink to
link the textfile into the current database (with an import
specification) and then use INSERT .. INTO .. IN or SELECT .. INTO .. IN
to move the data into the other database.

 

[Joe Fallon]

FYI
I have never used schema.ini for an Access program.
The import spec has always been sufficient.

However,
I had to learn the ins and outs of the schema.ini for VB.Net and the
TextHandler class I wrote using ADO.Net.
All the points you mention are valid (including the lack of decent
documentation and sample code!)
--
Joe Fallon
Access MVP

The least bad schema.ini documentation I've found is at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetsdk_98.asp
AFAIK schema.ini is used if it exists in the folder and contains an
entry for the filename in question: is it possible that you haven't
included the appropriate [filename.txt] line in schema.ini?

If the worst comes to the worst you can use DoCmd.TransferText acLink to
link the textfile into the current database (with an import
specification) and then use INSERT .. INTO .. IN or SELECT .. INTO .. IN
to move the data into the other database.

John,

I tried schema.ini, or should say am trying, again. I just don't
understand because it never works. All of the pages at MSDN say the
schema.ini must be in the same folder as the text file, which it is. But
that's all the page's say. It seems like it just ignores it anyway.

From everything I've read, it never says that you have to tell it to use
schema.ini somehow. The text file contains column names in the first row,
and the schema indicates that, yet it takes the first row and just takes
all the column names and adds them all together with an _ between them
and makes that the only column. It does the same thing with the data.

DS

 

[david epsom dot com dot au]

I haven't done exactly what you are trying to do, but FWIW:

(1) select statement without IMEXspec:
SELECT tblfx_audit.* INTO [Text;DATABASE=c:\].[mytext.txt]
(creates schema.ini which may be used for import)

(2) transfertext:
DoCmd.TransferText AcLinkDelim ,specname , "tblCB_Cashbook_Import", sFile,
Me.chkHeading

AcLinkDelim can be used to create the linked table: this is normally a lot
easier
than trying to create a link anyother way. Or you can just import the data
(also
easier than using SQL):
DoCmd.TransferText acImportDelim, specname, "tblCB_Cashbook_Import", sFile,
Me.chkHeading

(3) select statement from linked table:
SELECT * FROM tbl_tmptext into tbl_tmpImput;

After you have linked to a text file, you can treat it as a
local table: If you have a linked table, you don't need to
use "Text;" to copy the table to the next table.

(4) DSN=tabDelimited Link Specification;
I'm not familiar with that syntax: is it correct?

(5) tempTbl.TableDataSource="filename.tab"
Can you do that? I can't use files with a ".tab" extension
unless I make registry changes. I use ".txt" or ".csv",
which are accepted by default.

(david)

 

[DS]

The least bad schema.ini documentation I've found is at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/h
tm/odbcjetsdk_98.asp AFAIK schema.ini is used if it exists in the
folder and contains an entry for the filename in question: is it
possible that you haven't included the appropriate [filename.txt] line
in schema.ini?

If the worst comes to the worst you can use DoCmd.TransferText acLink
to link the textfile into the current database (with an import
specification) and then use INSERT .. INTO .. IN or SELECT .. INTO ..
IN to move the data into the other database.

John,

Just as an update, I did finally get the schema.ini thing to work. I had
2 problems with the file:

1. Two columns had the same number. The schema file I was using was one
that I had generated last time I failed to get the schema thing working.
I added another column in the file by cut & paste and neglected to change
the number.

2. Although not painfully obvious, the [filename] at the top of the file
is just that, the filename. I was putting the filename w/complete path
there.
(Apparently only) most of the time full path is a good thing.

Using schema.ini works fine for me, as the code generates it, uses it,
then deletes it, so unless you look in the temp directly at the exact
moment, it's transparent to the user.

Thanks for your time,

DS