Going trough all tables in Access with macro?

[donalde]

Hello. This will be 1st macro I code with VBA in Access, so be patient, I may ask silly questions. (Also, I also have very little experience using Access)

I am trying to do macro, which activates each table in my access DB, clears tables contents, then copies data from excel, and paste append clipboard to table. Then close table, and opens next table. Table and Excel sheet have exactly same name, depending name table has different dimensions and formats.

How do I do macro that goes trough all tables I have in my Access database? In excel going trough all worksheets I’d do following method:

Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
Select Case sh.Name
Case “Sheet1”
… some function calls in here
Case “Sheet9”
… some other function calls in here
End Select
Next

But how I do same in access?

Before copying data to clipboard, I need to run macro in excel which search correct area from excel sheet, but that macrocode is easy to code for me. Area which I need to copy varies in each excel sheet.

I can do delete and copy manually, but it is quite boring, since I have some 40+ tables to update.

I am using Access 2k, Excel 2k

 

[Norie]

Pasi

I use the following code to check the length of the text in every text field in every table in my database. Perhaps it is of some use?

Sub TestTextFields()
Dim db As Database
Dim tdf As TableDef
Dim rst As Recordset
Dim fld As Field
Dim MaxLength

    Set db = CurrentDb
    
    For Each tdf In db.TableDefs
        
        For Each fld In tdf.Fields
            If fld.Type = dbText Then
                Set rst = db.OpenRecordset("SELECT Max(Len([" & fld.Name & "])) FROM [" & tdf.Name & "];")
                MaxLength = rst.Fields(0).Value
                If MaxLength < fld.Size Then
                    Debug.Print tdf.Name, fld.Name & "(" & fld.Size&; ")", MaxLength
                End If
            End If
        Next
    Next
    
    Set rst = Nothing
    Set db = Nothing

End Sub