Write import specification in Access module? 2

[Dan01]

Is it possible to write an import specification in a module, bypassing the import specification wizard? Thanks, Dan.

 

[neemi]

Use the wizzard as to how you want to import your data.. Just before you press finish on the import, select Advanced, then select Save As. this will allow you to save the Spec you have created, and call it from the TransferText command.

Hope this is what you wanted.

neemi.

 

[SQLBI]

Dan,

Are you importing from a .xls or .txt/csv file?

If you're using .xls, use the TransferSpreadsheet method from the DoCmd Object.

XL2k has very good help file on this.

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[SQLBI]

I meant to say Acces2k, not XL... I hate Monday's!

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[Dan01]

Thanks neemi. That's how I do it now.

Hi Leigh, sometimes import txt, sometimes xls. Thanks, Dan.

 

[VBslammer]

I've built import specs programmatically in the past to bypass the wizard. Although there is no documentation I've found that details how to do it, I found through experimentation that all you have to do is create the spec tables (if they don't exist) and add the rows of specification information pertinent to your import.

Here's an example of the routine I used to create the spec tables and a procedure to call it:

Function AddSpecs(ParamArray ColumnNames() As Variant) As Boolean
  Dim db As DAO.Database
  Dim rst As DAO.Recordset
  Dim tdf As DAO.TableDef
  Dim tdf2 As DAO.TableDef
  Dim idx As DAO.index
  Dim idx2 As DAO.index
  Dim i As Integer
  
  Set db = CurrentDb
  
  ' Make the spec table.
  Set tdf = db.CreateTableDef("MSysIMEXSpecs")
  
  With tdf
    
    .Fields.Append .CreateField("DateDelim", dbText, 2)
    .Fields.Append .CreateField("DateFourDigitYear", dbBoolean)
    .Fields.Append .CreateField("DateLeadingZeros", dbBoolean)
    .Fields.Append .CreateField("DateOrder", dbInteger)
    .Fields.Append .CreateField("DecimalPoint", dbText, 2)
    .Fields.Append .CreateField("FieldSeparator", dbText, 2)
    .Fields.Append .CreateField("FileType", dbInteger)
    .Fields.Append .CreateField("SpecID", dbLong)
    .Fields("SpecID").Attributes = dbAutoIncrField      ' Autonumber
    .Fields.Append .CreateField("SpecName", dbText, 64)
    .Fields.Append .CreateField("SpecType", dbByte)
    .Fields.Append .CreateField("StartRow", dbLong)
    .Fields.Append .CreateField("TextDelim", dbText, 2)
    .Fields.Append .CreateField("TimeDelim", dbText, 2)
    db.TableDefs.Append tdf
    db.TableDefs.Refresh
    
    Set idx = db.TableDefs(tdf.Name).CreateIndex
    With idx
      .Name = "PrimaryKey"
      .Fields.Append .CreateField("SpecName")
      .Unique = True
      .Primary = True
    End With
    .Indexes.Append idx
    .Indexes.Refresh
  End With
  
  Set rst = db.OpenRecordset(tdf.Name)
  With rst
    .AddNew
    .Fields("DateDelim") = "/"
    .Fields("DateFourDigitYear") = True
    .Fields("DateLeadingZeros") = False
    .Fields("DateOrder") = 2
    .Fields("DecimalPoint") = "."
    .Fields("FieldSeparator") = " "   ' space
    .Fields("FileType") = 437   ' Excel 5
    .Fields("SpecName") = "ImportTest Import Specification"
    .Fields("SpecType") = 1
    .Fields("StartRow") = 1  ' 0 for 'No Header Row'
    .Fields("TextDelim") = Chr(34)
    .Fields("TimeDelim") = ":"
    .Update
  End With
  
  ' Make the columns table
  Set tdf2 = db.CreateTableDef("MSysIMEXColumns")
  
  With tdf2
    .Fields.Append .CreateField("Attributes", dbLong)
    .Fields.Append .CreateField("DataType", dbInteger)
    .Fields.Append .CreateField("FieldName", dbText, 64)
    .Fields.Append .CreateField("IndexType", dbByte)
    .Fields.Append .CreateField("SkipColumn", dbBoolean)
    .Fields.Append .CreateField("SpecID", dbLong)
    .Fields.Append .CreateField("Start", dbInteger)
    .Fields.Append .CreateField("Width", dbInteger)
    db.TableDefs.Append tdf2
    db.TableDefs.Refresh
    
    Set idx2 = db.TableDefs(tdf2.Name).CreateIndex
    With idx2
      .Name = "PrimaryKey"
      .Fields.Append .CreateField("FieldName")
      .Fields.Append .CreateField("SpecID")
      .Unique = True
      .Primary = True
    End With
    .Indexes.Append idx2
    .Indexes.Refresh
  End With
  
  Set rst = db.OpenRecordset(tdf2.Name)
  
  For i = 0 To UBound(ColumnNames)
    With rst
      .AddNew
      .Fields("Attributes") = 0
      .Fields("DataType") = dbText
      .Fields("FieldName") = ColumnNames(i)
      .Fields("IndexType") = 0
      .Fields("SkipColumn") = 0
      .Fields("SpecID") = 1  ' The spec table P.K.
      .Fields("Start") = i
      .Fields("Width") = Null
      .Update
    End With
  Next i
  
  AddSpecs = True
  
ExitHere:
  Set tdf = Nothing
  Set tdf2 = Nothing
  Set db = Nothing
  Set idx = Nothing
  Set idx2 = Nothing
  Set rst = Nothing
  Exit Function
ErrHandler:
  MsgBox "Error: " & Err & " - " & Err.Description
  Resume ExitHere
End Function


'@-----------------------------------------------------@


Sub ImportIt()
  Call AddSpecs("Hello", "World", "I'm", "Fine")
  DoCmd.TransferText acImportDelim, "ImportTest Import Specification", "ImportTemp", "C:\ImportTest2.txt", True
End Sub

There was a good discussion about this on Tek-Tips but the thread is no longer available. Do they delete posts here or what?

VBSlammer

Unemployed in Houston, Texas

 

[Dan01]

Thanks VBSlammer! Dan.