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
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
Last edited: