How to build an array (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote