Using Access & Excel

Excel Import Assistant for Microsoft Access

Import/export specification collection

Question

I've looked in help, I've looked in the object browser, can't find it anywhere... Is there any way to reference a databases' Import/Export Specification Collection? I'd like to write a little FOR EACH routine that prints out their names, and maybe some other properties.

Answer

John,

The code you find here seems to be an overkill but I think it solves your task - better more than nothing ;-)

HTH,
Shamil

P.S. The code:

' _test
Sub a_test()
  Dim specs As New CIMEXSpecs
  Dim spec As CIMEXSpec
  Dim col As CIMEXColumn

  specs.Init

  For Each spec In specs.IMEXSpecs
    Debug.Print spec.SpecName
    For Each col In spec.IMEXColumns
      Debug.Print "  " & col.FieldName
    Next
  Next

  Set col = Nothing
  Set spec = Nothing
  Set specs = Nothing
End Sub

' Сlass module - CIMEXSpecs
Private mcolIMEXSpecs As Collection

Private Sub Class_Initialize()
  Set mcolIMEXSpecs = New Collection
End Sub

Public Sub Init(Optional ByRef rdbs As Database, _
                Optional ByVal vstrDBPath As String = "")
  Dim objSpec As CIMEXSpec

  Dim dbs As Database
  Dim rst As Recordset

  If rdbs Is Nothing And vstrDBPath = "" Then
    Set dbs = CodeDb()
  ElseIf Not (rdbs Is Nothing) Then
    Set dbs = rdbs
  Else
    Set dbs = DBEngine(0).OpenDatabase(vstrDBPath, , False)
  End If

  Set rst = dbs.OpenRecordset("MSYSIMEXSpecs", dbOpenSnapshot)
  While Not rst.EOF
    Set objSpec = New CIMEXSpec
    With objSpec
     .DateDelim = rst![DateDelim]
     .DateFourDigitYear = rst![DateFourDigitYear]
     .DateLeadingZeros = rst![DateLeadingZeros]
     .DateOrder = rst![DateOrder]
     .DecimalPoint = rst![DecimalPoint]
     .FieldSeparator = rst![FieldSeparator]
     .FileType = rst![FileType]
     .SpecID = rst![SpecID]
     .SpecName = rst![SpecName]
     .SpecType = rst![SpecType]
     .StartRow = Nz(rst![StartRow], 0)
     .TextDelim = Nz(rst![TextDelim], "")
     .TimeDelim = Nz(rst![TimeDelim], "")
    End With

    objSpec.GetColumns dbs

    Me.IMEXSpecs.Add objSpec, objSpec.SpecName
    rst.MoveNext
  Wend

  rst.Close
  Set rst = Nothing
  Set dbs = Nothing
End Sub

Public Property Get IMEXSpecs() As Collection
  Set IMEXSpecs = mcolIMEXSpecs
End Property

' Class module CIMEXSpec
Private mstrDateDelim As String '* 1
Private mblnDateFourDigitYear As Boolean
Private mblnLeadingZeros As Boolean
Private mintDateOrder As Integer
Private mstrDecimalPoint As String '* 1
Private mstrFieldSeparator As String '* 1
Private mintFileType As Integer
Private mlngSpecID As Long
Private mstrSpecName As String '* 64
Private mbytSpecType As Byte
Private mlngStartRow As Long
Private mstrTextDelim As String '* 1
Private mstrTimeDelim As String '* 1

Private mcolIMEXColumns As Collection

Public Property Get DateDelim() As String
  DateDelim = mstrDateDelim
End Property
Public Property Let DateDelim(ByVal vstrDateDelim As String)
  mstrDateDelim = mstrDateDelim
End Property

Public Property Get DateFourDigitYear() As Boolean
  DateFourDigitYear = mblnDateFourDigitYear
End Property
Public Property Let DateFourDigitYear(ByVal vblnDateFourDigitYear As
Boolean)
  mblnDateFourDigitYear = vblnDateFourDigitYear
End Property

Public Property Get DateLeadingZeros() As Boolean
  DateLeadingZeros = mblnLeadingZeros
End Property
Public Property Let DateLeadingZeros(ByVal vblnLeadingZeros As Boolean)
  mblnLeadingZeros = mblnLeadingZeros
End Property

Public Property Get DateOrder() As Integer
  DateOrder = mintDateOrder
End Property
Public Property Let DateOrder(ByVal vintDateOrder As Integer)
  mintDateOrder = vintDateOrder
End Property

Public Property Get DecimalPoint() As String
  DecimalPoint = mstrDecimalPoint
End Property
Public Property Let DecimalPoint(ByVal vstrDecimalPoint As String)
  mstrDecimalPoint = vstrDecimalPoint
End Property

Public Property Get FieldSeparator() As String
  FieldSeparator = mstrFieldSeparator
End Property
Public Property Let FieldSeparator(ByVal vstrFieldSeparator As String)
  mstrFieldSeparator = vstrFieldSeparator
End Property

Public Property Get FileType() As Integer
  FileType = mintFileType
End Property
Public Property Let FileType(ByVal vintFileType As Integer)
  mintFileType = vintFileType
End Property

Public Property Get SpecID() As Long
  SpecID = mlngSpecID
End Property
Public Property Let SpecID(ByVal vlngSpecID As Long)
  mlngSpecID = vlngSpecID
End Property

Public Property Get SpecName() As String
  SpecName = mstrSpecName
End Property
Public Property Let SpecName(ByVal vstrSpecName As String)
  mstrSpecName = vstrSpecName
End Property

Public Property Get SpecType() As Byte
  SpecType = mbytSpecType
End Property
Public Property Let SpecType(ByVal vbytSpecType As Byte)
  mbytSpecType = vbytSpecType
End Property

Public Property Get StartRow() As Long
  StartRow = mlngStartRow
End Property
Public Property Let StartRow(ByVal vlngStartRow As Long)
  On Error GoTo StartRow_Err
  mlngStartRow = vlngStartRow
StartRow_Err:
End Property

Public Property Get TextDelim() As String
  TextDelim = mstrTextDelim
End Property
Public Property Let TextDelim(ByVal vstrTextDelim As String)
  On Error GoTo TextDelim_Err
  mstrTextDelim = vstrTextDelim
TextDelim_Err:
End Property

Public Property Get TimeDelim() As String
  On Error GoTo TimeDelim_Err
  TimeDelim = mstrTimeDelim
TimeDelim_Err:
End Property
Public Property Let TimeDelim(ByVal vstrTimeDelim As String)
  mstrTimeDelim = vstrTimeDelim
End Property

Public Sub GetColumns(ByRef rdbs As Database)
  Dim objColumn As CIMEXColumn
  Dim rst As Recordset

  Set mcolIMEXColumns = New Collection

  Set rst = rdbs.OpenRecordset("select * from MSYSIMEXColumns where SpecId =
" & Me.SpecID, dbOpenSnapshot)
  While Not rst.EOF
    Set objColumn = New CIMEXColumn
    With objColumn
      .Attributes = Nz(rst![Attributes], 0)
      .DataType = rst![DataType]
      .FieldName = rst![FieldName]
      .IndexType = Nz(rst![IndexType], 0)
      .SkipColumn = Nz(rst![SkipColumn], 0)
      .SpecID = rst![SpecID]
      .Start = rst![Start]
      .Width = rst![Width]
    End With
    Me.IMEXColumns.Add objColumn, objColumn.FieldName
    rst.MoveNext
  Wend
  rst.Close
  Set rst = Nothing
End Sub

Public Property Get IMEXColumns() As Collection
  Set IMEXColumns = mcolIMEXColumns
End Property

' Class module CIMEXColumn
Private mlngAttributes As Long
Private mintDataType As Integer
Private mstrFieldName As String '* 64
Private mbytIndexType As Byte
Private mblnSkipColumn As Boolean
Private mlngSpecID As Long
Private mintStart As Integer
Private mintWidth As Integer

Public Property Get Attributes() As Long
  Attributes = mlngAttributes
End Property
Public Property Let Attributes(ByVal vlngAttributes As Long)
  mlngAttributes = vlngAttributes
End Property

Public Property Get DataType() As Integer
  DataType = mintDataType
End Property
Public Property Let DataType(ByVal vintDataType As Integer)
  mintDataType = vintDataType
End Property

Public Property Get FieldName() As String
  FieldName = mstrFieldName
End Property
Public Property Let FieldName(ByVal vstrFieldName As String)
  mstrFieldName = vstrFieldName
End Property

Public Property Get IndexType() As Byte
  IndexType = mbytIndexType
End Property
Public Property Let IndexType(ByVal vbytIndexType As Byte)
  mbytIndexType = vbytIndexType
End Property

Public Property Get SkipColumn() As Boolean
  SkipColumn = mblnSkipColumn
End Property
Public Property Let SkipColumn(ByVal vblnSkipColumn As Boolean)
  mblnSkipColumn = vblnSkipColumn
End Property

Public Property Get SpecID() As Long
  SpecID = mlngSpecID
End Property
Public Property Let SpecID(vlngSpecID As Long)
  mlngSpecID = vlngSpecID
End Property

Public Property Get Start() As Integer
  Start = mintStart
End Property
Public Property Let Start(ByVal vintStart As Integer)
  mintStart = vintStart
End Property

Public Property Get Width() As Integer
  Width = mintWidth
End Property
Public Property Let Width(ByVal vintWidth As Integer)
  mintWidth = vintWidth
End Property

Copyright © 1999-2008 by Shamil Salakhetdinov. All rights reserved.