Arrays

[Mcgrco]

Im trying to preform an exisize that will help me understand arrays and there uses. What i want to do is from one database (a)lookup another database (b)and if the tables match import the new table from the second database. The first piece of code loads all the table names in the in an array from db(a). What I now want to do is for each table in db (b) look up the array and if it matches import it from db(a). I not sure how I access the information stored in the array. Can anyone point me in the right direction . Many thanks

My code so far is :

Public Function getTables()
Dim wsp As Workspace
Dim dbs As Database, dbsAnother As Database
Dim tdf As TableDef
Dim iDbCodeTables As Integer
Dim i As Integer
Set dbs = CurrentDb
Set wsp = DBEngine.Workspaces(0)
Set dbsAnother = wsp.OpenDatabase("S:\cadproj\DAILYREP\DATABASE\Liquidity\LiquidityMaster.mdb")
iDbCodeTables = dbsAnother.TableDefs.Count

ReDim dbcodeTables(iDbCodeTables)

For i = 0 To dbsAnother.TableDefs.Count - 1
For Each tdf In dbsAnother.TableDefs
dbcodeTables(i) = tdf.Name
Next tdf
Next i



Set dbs = Nothing
Set dbsAnother = Nothing
End Function

 

[pdx_man]

Start by fixing your loop:


i = 0
For Each tdf In dbsAnother.TableDefs
dbcodeTables(i) = tdf.Name
i=i+1
Next tdf

For as many tables you have, you were going through the lot assigning the names to the same place in the array.

Bottom line, you ended up with the last table name in every spot in your array ... right?

 

[WayneRyan]

Mcgrco,

I think a simple loop like this will work for you.

For Each tdf1 in dbsAnother.TableDefs
For Each tdf2 In dbs.TableDefs
If tdf1.Name = tdf2.Name Then
' Your code for matching tables ...
End If
Next tdf2
Next tdf1

If you really need the fun of working with arrays, then as
pdx_man said first fix your array contents. Then after your
original code put:

for i = 0 to dbsAnother.TableDefs.Count - 1
for each tdf2 in dbs.TableDefs
if dbcodeTables(i) = tdf.Name Then
' Your code for matching tables ...
End If
next tdf2
next i

hth,
Wayne

 

[Mcgrco]

Thanks Guys,

Sometimes you cant see the wood from the trees