TransferText Method Imports All Data Into One Column

[moon unit]

I've been beating my head against the wall all day on this & have a somewhat different approach .... the following code creates an entry into MSysIMEXSpecs along with the required entries into MSysIMEXColumns

It then links to my text file using the spec entries just created

While the code is a bit crude - it's been a long day and I only completed this a few mins ago

Editing, adding and deleting records in these two tables is possible - just not directly in table view.

I've got a few hundred text files to link to every week & am planning on storing all spec info in a control table to facilitate their update as needed.

After a bit of sleep I'll be connecting to the various access databases involved & making a copy of the spec records I need along with the unc of each file.

I took this route because I discovered this afternoon that TransferText will NOT use a Schema.ini file with delimited files, of course that was after I looped through all the databases in question and generated the schema files needed.

Although it's not pretty - here's the code:

Option Compare Database
Option Explicit

Function SpecControl() As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rCol As DAO.Recordset
Dim tdf As DAO.TableDef
Dim sSQL As String
Dim str As String
Dim lng As Long
Dim sLocNm As String
Dim sScrDB As String

sSQL = "INSERT INTO MSysIMEXSpecs (DateDelim, DateFourDigitYear, DateLeadingZeros, DateOrder, DecimalPoint, FieldSeparator, FileType, " _
& "SpecName, SpecType, StartRow, TextDelim, TimeDelim) SELECT '/' AS DateDelim, -1 AS DateFourDigitYear, 0 AS DateLeadingZeros, " _
& "2 AS DateOrder, '.' AS DecimalPoint, ',' AS FieldSeparator, 437 AS FileType, 'Link Spec Madness' AS SpecName, 1 AS SpecType, " _
& "1 AS StartRow, '" & Chr(34) & "' AS TextDelim, ':' AS TimeDelim;"

str = "INSERT INTO MSysIMEXColumns ( Attributes, DataType, FieldName, IndexType, SkipColumn, SpecID, Start, Width ) SELECT "

sLocNm = "Col_List"

sScrDB = "D:\Documents and Settings\lbrbt\Desktop\updating the scorecard\Clue\Woot Woot"

Set db = CurrentDb
db.Execute sSQL

Set rs = db.OpenRecordset("SELECT Max(MSysIMEXSpecs.SpecID) AS MaxOfSpecID FROM MSysIMEXSpecs;")

lng = rs.Fields(0)

rs.Close
Set rs = Nothing

db.Execute str & "0 AS Attributes, 4 AS DataType, 'fk_DID' AS FieldName, 0 AS IndexType, 0 AS SkipColumn, " & lng & " AS SpecID, 1 AS Start, 7 AS Width;"

db.Execute str & "0 AS Attributes, 10 AS DataType, 'TableName' AS FieldName, 0 AS IndexType, 0 AS SkipColumn, " & lng & " AS SpecID, 8 AS Start, 24 AS Width;"

db.Execute str & "0 AS Attributes, 10 AS DataType, 'ColumnName' AS FieldName, 0 AS IndexType, 0 AS SkipColumn, " & lng & " AS SpecID, 32 AS Start, 24 AS Width;"

db.Execute str & "0 AS Attributes, 10 AS DataType, 'ColumnDataType' AS FieldName, 0 AS IndexType, 0 AS SkipColumn, " & lng & " AS SpecID, 56 AS Start, 15 AS Width;"

db.Execute str & "0 AS Attributes, 4 AS DataType, 'ColumnSize' AS FieldName, 0 AS IndexType, 0 AS SkipColumn, " & lng & " AS SpecID, 71 AS Start, 11 AS Width;"

With db
Set tdf = .CreateTableDef(sLocNm)
tdf.SourceTableName = "Col_List.txt"
tdf.Connect = "Text;DSN=Link Spec Madness;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=437;Database=" & sScrDB
.TableDefs.Append tdf
Set tdf = Nothing
End With

db.Close
Set db = Nothing

End Function

 

[gemma-the-husky]

its not too hard to read a text file in a line at a time, and parse it yourself.

a bit harder than using an importspec, but if you cant get an import spec theres no alternative.

and then you could certainly include code to manage an ini file

---
ok its a fair bit of code, but you only have to develop it once, and then you get lots of flexibility