UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using Vba To Create List Of All Available Saved Exports/imports, Access 2013    
 
   
ozop102
post Aug 28 2019, 10:02 PM
Post#1



Posts: 8
Joined: 28-August 19



Hello everyone, I have seemingly simple idea but I lack the right amount of loop knowledge to accomplish it.

I need to loop through all available import and export specifications and create a list box of each one that exists (Access.CurrentProject.ImportExportSpecifications(i).Name)

I then want the user to to be able to choose the desired export or import from a list. Then I can run DoCmd.RunSavedImportExport "Name" to execute.

Im not strong with loops and any help would be very much appreciated. I need help looping through to make the list and then putting that list into a window the user can select. Thanks again for everyone time.

-Mario
This post has been edited by ozop102: Aug 28 2019, 10:03 PM
Go to the top of the page
 
isladogs
post Aug 28 2019, 10:32 PM
Post#2


UtterAccess VIP
Posts: 1,879
Joined: 4-June 18
From: Somerset, UK


Details of all saved imports and exports are stored in two system tables: MSysIMEXSpecs and MSysIMEXColumns
So all you need to do is run a query on one or both tables to get what you need.

For example
CODE
SELECT MSysIMEXSpecs.SpecName, MSysIMEXSpecs.SpecType
FROM MSysIMEXSpecs;


Use this as your listbox row source.

NOTE
Like all system tables, these are hidden by default.
Handle with care as the records in these two tables are editable

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
MadPiet
post Aug 28 2019, 10:36 PM
Post#3



Posts: 3,364
Joined: 27-February 09



Interesting... I did it the wrong way, but it worked:

Dim i As Integer

For i = 0 To Access.CurrentProject.ImportExportSpecifications.Count - 1
AddItemToEnd Me.lbxExportSpecs, Access.CurrentProject.ImportExportSpecifications(i).Name
Next i
Go to the top of the page
 
ozop102
post Aug 29 2019, 01:37 AM
Post#4



Posts: 8
Joined: 28-August 19



Ok, testing out both, I could not get any information to show from MSysIMEXSpecs, the query worked but was blank.

based on my database design (im launching the code from a switchboard) I had to make some changes and I used the loop idea to Frankenstein a working code. This is me reverse engineer alot of existing code so not sure if there are better ways of doing this. Also with the loop idea, AddItemToEnd was giving me an error.

The first function I put in was this:
------------------------------------------------------------------------------------------------------
Public Function InitiateSavedIE()

'<<<<<<<<<<<<<<<Part One(1) of Select Saved Import/Export>>>>>>>>>>>>>>

Dim ie As ImportExportSpecification
Dim arr() As Variant
Dim x As Long

DoCmd.OpenForm "SelectIE"

For Each ie In CurrentProject.ImportExportSpecifications
ReDim arr(x)
arr(x) = ie.Name
x = x + 1
Next

For x = LBound(arr) To UBound(arr)
Forms!SelectIE.ieChoice.AddItem arr(x)
Next x
End Function
------------------------------------------------------------------------------------------------------

I did it this way since I need to open the form as I start the function to be able to reference the listbox. Next I programmed the "Ok" button to run the next function.
------------------------------------------------------------------------------------------------------
Public Function SelectImportExport()

'<<<<<<<<<<<<<<<Part Two(2) of Select Saved Import/Export>>>>>>>>>>>>>>
Dim IEName As String

On Error GoTo ErrorHandler

If IsEmpty(Forms!SelectIE.ieChoice.Value) Then
GoTo ErrorHandler
Else
IEName = Forms!SelectIE.ieChoice.Value
End If

DoCmd.RunSavedImportExport IEName

MsgBox "File Import Complete"

Exit Function
ErrorHandler:
MsgBox "Error " & Err & ": " & Error(Err)

End Function
------------------------------------------------------------------------------------------------------

And finally I have it kinda working, it feels like I could do this alot better still.
Go to the top of the page
 
isladogs
post Aug 29 2019, 06:36 AM
Post#5


UtterAccess VIP
Posts: 1,879
Joined: 4-June 18
From: Somerset, UK


That's odd. Are there any saved exports/imports?
If so, open the MSysIMEXSpecs table itself and see if it contains any records of saved exports/imports
Similarly the MSysIMEXColumns table

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
ADezii
post Aug 29 2019, 07:17 AM
Post#6



Posts: 2,700
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. To be perfectly honest, your Code will never work as intended.
  2. Redim will re-initialize the Array arr() and destroy any Data in it unless you use the Preserve Keyword. In your specific case, only the 'last' Import Specification will be listed.
  3. Example and Output using Redim:
    CODE
    Dim varItm As Variant
    Dim intCtr As Integer
    Dim arr() As Variant
    Dim x As Long

    varItm = Array(12, 20, 30, 40)

    For intCtr = LBound(varItm) To UBound(varItm)
      ReDim arr(x)
      arr(x) = varItm(intCtr)
        x = x + 1
    Next

    For intCtr = LBound(arr) To UBound(arr)
      Debug.Print arr(intCtr)
    Next

    CODE
    Output: 40
  4. Previous example modified using Redim Preserve which will maintain any existing Data within the arr() Array while re-initializing the Upper Bound:
    CODE
    Dim varItm As Variant
    Dim intCtr As Integer
    Dim arr() As Variant
    Dim x As Long

    varItm = Array(12, 20, 30, 40)

    For intCtr = LBound(varItm) To UBound(varItm)
      ReDim Preserve arr(intCtr)
      arr(intCtr) = varItm(intCtr)
    Next

    For intCtr = LBound(arr) To UBound(arr)
      Debug.Print arr(intCtr)
    Next

    CODE
    Output:
    12
    20
    30
    40
  5. Hopefully, my explanation was clear enough.

This post has been edited by ADezii: Aug 29 2019, 07:26 AM
Go to the top of the page
 
ozop102
post Aug 29 2019, 11:33 AM
Post#7



Posts: 8
Joined: 28-August 19



Isladogs, I only had one saved import but it wouldnt show up at all in the specs nor the columns table. I do my work at home in a test database and the. i transfer the code to my work database later on. I tried again on my work database and it did show im, so Im not sure at all why it wasnt working for ke at home.

ADezii, you are 100% right! This was my first time using an array like this and didnt know about preserve. At work I added in the code and u were right. All blank enties and just the last one showed up! Preserve did the trick and its all good now! Thank you both for your help!
Go to the top of the page
 
Phil_cattivocara...
post Aug 30 2019, 01:40 AM
Post#8



Posts: 368
Joined: 2-April 18



You do not need array to populate the list box
CODE
Public Function InitiateSavedIE()
'<<<<<<<<<<<<<<<Part   One(1) of Select Saved   Import/Export>>>>>>>>>>>>>>
Dim ie As ImportExportSpecification
DoCmd.OpenForm "SelectIE"

For Each ie In CurrentProject.ImportExportSpecifications
      Forms!SelectIE.ieChoice.AddItem ie.Name
Next
End Function

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 08:23 PM