Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to build an array (Access 2000)

    I want to build an array or something like that to rationalize my function. The function must first detect any database called
    DepotSo,DepotVa,DepotBl, etc, i have about 14
    If the database is detected then the function FromDepot is triggered importing tables depending on the source.


    Public Const DSo As String = "C:beDepotSo.mdb"
    Public Const DVa As String = "C:beDepotVa.mdb"
    Public Const DBl As String = "C:beDepotBl.mdb"
    etc
    etc

    Public Function FncCollectFromDepot()

    ' import the tablles from any available Depot, and after that deletes

    this depot

    '******************** Stuttgardt
    If Dir(DSo, vbNormal) <> "" Then
    Call FromDepot(DSo)
    UpdateTables
    Kill (DSo)
    End If
    '******************** Beograd
    If Dir(DVa, vbNormal) <> "" Then
    Call FromDepot(DVa)
    UpdateTables
    Kill (DVa)
    End If
    '******************** Nish
    If Dir(DBl, vbNormal) <> "" Then
    Call FromDepot(DBl)
    UpdateTables
    Kill (DBl)
    End If
    '******************** Berlin
    If Dir(DHa, vbNormal) <> "" Then
    Call UnleashDepot(DHa)
    Call FromDepot(DHa)
    UpdateTables
    Kill (DHa)
    etc
    etc

    End If
    End Function


    Public Function FromDepot(appath As String)
    DoCmd.TransferDatabase acImport, "Microsoft Access", appath, acTable, "Customers", "Customers1"
    DoCmd.TransferDatabase acImport, "Microsoft Access", appath, acTable, "order details", "order details1"
    DoCmd.TransferDatabase acImport, "Microsoft Access", appath, acTable, "orders", "orders1"
    End Function

    So i want to build my function FncCollectFromDepot so as to evade the enumeration of each database separately for example
    If Dir(DSo, vbNormal) <> "" Then
    etctect
    ant to obtain a global code.

    I will be grateful for any help in this respect

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to build an array (Access 2000)

    Sorry, but I don't see where an array enters into this at all. Is there something you left out? If you want to test every depot each time you call fncCollectFromDepot, then you're still going to be doing that regardless. What you could do is use a loop instead of separate If-End If statements. Create a string variable for strDepot and use a Choose() function to assign the constants, one at a time, to the variable. Then use the variable in the Dir function like this:

    For intLoop = 1 to 3
    strDepot = Choose(intLoop, DSo, DVa, DBi)

    If Dir(strDepot, vbNormal) <> "" Then
    Call FromDepot(strDepot)
    UpdateTables
    Kill (strDepot)
    End If
    Next intLoop
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to build an array (Access 2000)

    Thank you !!! it is exactly what i wanted. I see i was not very clear but you did guess what i need and it will solve my problem.

    Warm regards

  4. #4
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to build an array (Access 2000)

    May i have a small help?

    i have copied your proposal and made a function, but obviously
    i have missed something, since i receive the message
    for IntLoop that variable is not defined.The function is below:

    Public Function Test()
    Dim strDepot As String
    For intLoop = 1 To 3
    strDepot = Choose(intLoop, DSo, DVa, DBi)
    If Dir(strDepot, vbNormal) <> "" Then
    Call FromDepot(strDepot)
    UpdateTables
    Kill (strDepot)
    End If
    Next intLoop
    End Function

    Best regards

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to build an array (Access 2000)

    You need to dim intLoop as integer before you try to use it. Put that line in either before or after the line that dims strDepot as string.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •