Excel Import Assistant for Microsoft Access
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.
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.