Access linked table to text file with schema.ini not working ADO

[donbexcel]

I'm trying to create a linked table in Access 2000 where the source is a
..TXT file using ADO. Even though I'm now using a schema.ini file, the
resulting linked file appears as a single column of information. It is not
being parsed correctly. Has anyone had this problem?

The code looks like this:

Function bSetDatasourceLinkFile(sTableName As String) As Boolean
Dim tblTable As New ADOX.Table
Dim cat As New ADOX.Catalog


bSetDatasourceLinkFile = True
cat.ActiveConnection = p_dbDatabase

On Error GoTo LocalHandler




If bTableExists(sTableName) Then
bDeleteTable sTableName
End If

tblTable.Name = sTableName
Set tblTable.ParentCatalog = cat



With tblTable
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Link Provider String") =
"Text;FMT=Delimited;HDR=YES;IMEX=2;CharacterSet=437;"
.Properties("Jet OLEDB:Link Datasource") = Left(p_sMSPath,
Len(p_sMSPath) - 1)
.Properties("Jet OLEDB:Cache Link Name/Password") = False
.Properties("Jet OLEDB:Remote Table Name") = "Demand daily#txt"
End With

cat.Tables.Append tblTable


Set tblTable = Nothing
Set cat = Nothing


Exit Function
LocalHandler:
MsgBox Err.Description
Set tblTable = Nothing
Set cat = Nothing
bSetDatasourceLinkFile = False



End Function

The schema.ini file looks like this:

[demand daily.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=25
CharacterSet=OEM
Col1="XPROD_CD" Char Width 200
Col2="MDL_NAME_CD" Char Width 200
Col3="MDL_FULL_YR" Integer
Col4="MDL_GRP_NAME" Char Width 200
Col5="MDL_SEG_NAME" Char Width 200
Col6="MDL_STAT_CD" Char Width 200
Col7="PROD_CTGY_NAME" Char Width 200
Col8="ORDER PROCESS (CR/C)" Float
Col9="ORDER PROCESS (SCH)" Float
Col10="ORDER PROCESS (SCHD)" Float
Col11="WHOLESALE (MTD)" Float
Col12="WARRANTY REGISTRATION (MTD)" Float
Col13="DEALER INVENTORY" Float
Col14="WAREHOUSE ID" Integer
Col15="GOOD" Float
Col16=HOLD/DAMAGED Float
Col17="IN TRANSIT" Float
Col18="JAPAN_MDL_TYP_CD" Char Width 200



Regards,

Don Buchanan

 

[Robert Morley]

I don't *think* this would cause that behaviour, but try changing
"cat.ActiveConnection = p_dbDatabase" to "Set cat.ActiveConnection =
p_dbDatabase". The difference is that as you have it, the default property
(ConnectionString) of p_dbDatabase is assigned to cat.ActiveConnection
instead of p_dbDatabase itself being assigned. Thus, you're establishing a
NEW connection for cat.ActiveConnection, whereas using Set assigns the
existing connection to it.

It seems unlikely that would cause the problem, but if nothing else, it
should speed up the routine a bit.

I've never done linked tables in ADOX, only in DAO, but the one thing that
occurs to me here is to double-check that schema.ini is in the same folder
as "demand daily.txt" and that there are no typos. Also, try removing the
space from the file name and see if that does it...never used filenames with
spaces in my work, so I don't know how the schema.ini file deals with them.



Rob

I'm trying to create a linked table in Access 2000 where the source is a
.TXT file using ADO. Even though I'm now using a schema.ini file, the
resulting linked file appears as a single column of information. It is not
being parsed correctly. Has anyone had this problem?

The code looks like this:

Function bSetDatasourceLinkFile(sTableName As String) As Boolean
Dim tblTable As New ADOX.Table
Dim cat As New ADOX.Catalog


bSetDatasourceLinkFile = True
cat.ActiveConnection = p_dbDatabase

On Error GoTo LocalHandler




If bTableExists(sTableName) Then
bDeleteTable sTableName
End If

tblTable.Name = sTableName
Set tblTable.ParentCatalog = cat



With tblTable
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Link Provider String") =
"Text;FMT=Delimited;HDR=YES;IMEX=2;CharacterSet=437;"
.Properties("Jet OLEDB:Link Datasource") = Left(p_sMSPath,
Len(p_sMSPath) - 1)
.Properties("Jet OLEDB:Cache Link Name/Password") = False
.Properties("Jet OLEDB:Remote Table Name") = "Demand daily#txt"
End With

cat.Tables.Append tblTable


Set tblTable = Nothing
Set cat = Nothing


Exit Function
LocalHandler:
MsgBox Err.Description
Set tblTable = Nothing
Set cat = Nothing
bSetDatasourceLinkFile = False



End Function

The schema.ini file looks like this:

[demand daily.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=25
CharacterSet=OEM
Col1="XPROD_CD" Char Width 200
Col2="MDL_NAME_CD" Char Width 200
Col3="MDL_FULL_YR" Integer
Col4="MDL_GRP_NAME" Char Width 200
Col5="MDL_SEG_NAME" Char Width 200
Col6="MDL_STAT_CD" Char Width 200
Col7="PROD_CTGY_NAME" Char Width 200
Col8="ORDER PROCESS (CR/C)" Float
Col9="ORDER PROCESS (SCH)" Float
Col10="ORDER PROCESS (SCHD)" Float
Col11="WHOLESALE (MTD)" Float
Col12="WARRANTY REGISTRATION (MTD)" Float
Col13="DEALER INVENTORY" Float
Col14="WAREHOUSE ID" Integer
Col15="GOOD" Float
Col16=HOLD/DAMAGED Float
Col17="IN TRANSIT" Float
Col18="JAPAN_MDL_TYP_CD" Char Width 200



Regards,

Don Buchanan

 

[donbexcel]

Robert,

p_dbDatabase is my ADODB.Connection. The New vs. Set is not going to matter
here. I'm setting the cat.ActiveConnection to this p_dbDatabase
(ADODB.Connection). I don't think that is keeping the linked file from being
parsed correctly.

I haven't tried removing the spaces. That's the clients file. I can link to
the file directly in Access.

Regards,

Don Buchanan

I don't *think* this would cause that behaviour, but try changing
"cat.ActiveConnection = p_dbDatabase" to "Set cat.ActiveConnection =
p_dbDatabase". The difference is that as you have it, the default property
(ConnectionString) of p_dbDatabase is assigned to cat.ActiveConnection
instead of p_dbDatabase itself being assigned. Thus, you're establishing a
NEW connection for cat.ActiveConnection, whereas using Set assigns the
existing connection to it.

It seems unlikely that would cause the problem, but if nothing else, it
should speed up the routine a bit.

I've never done linked tables in ADOX, only in DAO, but the one thing that
occurs to me here is to double-check that schema.ini is in the same folder
as "demand daily.txt" and that there are no typos. Also, try removing the
space from the file name and see if that does it...never used filenames
with spaces in my work, so I don't know how the schema.ini file deals with
them.



Rob

I'm trying to create a linked table in Access 2000 where the source is a
.TXT file using ADO. Even though I'm now using a schema.ini file, the
resulting linked file appears as a single column of information. It is
not
being parsed correctly. Has anyone had this problem?

The code looks like this:

Function bSetDatasourceLinkFile(sTableName As String) As Boolean
Dim tblTable As New ADOX.Table
Dim cat As New ADOX.Catalog


bSetDatasourceLinkFile = True
cat.ActiveConnection = p_dbDatabase

On Error GoTo LocalHandler




If bTableExists(sTableName) Then
bDeleteTable sTableName
End If

tblTable.Name = sTableName
Set tblTable.ParentCatalog = cat



With tblTable
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Link Provider String") =
"Text;FMT=Delimited;HDR=YES;IMEX=2;CharacterSet=437;"
.Properties("Jet OLEDB:Link Datasource") = Left(p_sMSPath,
Len(p_sMSPath) - 1)
.Properties("Jet OLEDB:Cache Link Name/Password") = False
.Properties("Jet OLEDB:Remote Table Name") = "Demand daily#txt"
End With

cat.Tables.Append tblTable


Set tblTable = Nothing
Set cat = Nothing


Exit Function
LocalHandler:
MsgBox Err.Description
Set tblTable = Nothing
Set cat = Nothing
bSetDatasourceLinkFile = False



End Function

The schema.ini file looks like this:

[demand daily.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=25
CharacterSet=OEM
Col1="XPROD_CD" Char Width 200
Col2="MDL_NAME_CD" Char Width 200
Col3="MDL_FULL_YR" Integer
Col4="MDL_GRP_NAME" Char Width 200
Col5="MDL_SEG_NAME" Char Width 200
Col6="MDL_STAT_CD" Char Width 200
Col7="PROD_CTGY_NAME" Char Width 200
Col8="ORDER PROCESS (CR/C)" Float
Col9="ORDER PROCESS (SCH)" Float
Col10="ORDER PROCESS (SCHD)" Float
Col11="WHOLESALE (MTD)" Float
Col12="WARRANTY REGISTRATION (MTD)" Float
Col13="DEALER INVENTORY" Float
Col14="WAREHOUSE ID" Integer
Col15="GOOD" Float
Col16=HOLD/DAMAGED Float
Col17="IN TRANSIT" Float
Col18="JAPAN_MDL_TYP_CD" Char Width 200



Regards,

Don Buchanan

 

[donbexcel]

Jamie,

The sTableName is the name of the table as it exists in the database.

Yes I intended to hard code this: "Demand daily#txt"

I have the schema.ini file in the same location as the Demand daily.txt
file. I know it is being referenced because if I change and entry to make it
incorrect I get an error message.

I created a very simple example of a text file that contained just a couple
of columns and a few rows of data to see if there was something, in
particular, about my text file and that one did the same thing.

Again, my results are a linked table that appears as a single column (not
parsed) and the single column has the values separated by tabs.

Perhaps my text driver is messed up?

Regards,

Don Buchanan