Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: Select statement on table collection

    Observe the below code, in which I search for a table with the last 10 characters being "importfejl" meaning import errors.

    Obviously this piece of code is run after an import of a CSV file, and would open the importerror table, should one such exist after the import.

    BUT, on request, I want to develop it a little further. I want it to open any table containing importfejl in the name. Sorta like SELECT Tabledefs.Name FROM Tabledefs WHERE Tabledefs.Name like %importfejl% and then do a For each loop opening the tables.

    Any ideas?

    Cheers, Trin

    Code:
        For Each tabeldef In CurrentDb.TableDefs
        
            If Right(tabeldef.Name, 10) = "importfejl" Then
            
                MsgBox "Der opstod fejl under importen. Tabellen over importfejl åbnes", vbOKOnly
                DoCmd.OpenTable tabeldef.Name, acViewNormal, acReadOnly
                
            End If
        
        Next tabeldef
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    In order to do what you want you will probably want to Query the MSysObjects table (Hidden System table). To view this table go to Tools/Options/View Tab/System Objects check box. Then you can see the table and look at the Name field to get all of the tables that match your pattern.

    Keep in mind Microsoft warns anyone about using the system tables for anything. Change the information in the tables may create problems for your database. But I have never had any problems reading the information from the tables. But... I have had plenty of problems with code that uses System tables and upgrading to a newer version. The data in the tables is not consistent between versions. So if you write code for 2000 and then you upgrade to 2002 or 2007 you should expect problems. Usually, they are minor and my code could be changed to use the latest version of Access.

  3. #3
    Join Date
    Oct 2005
    Posts
    183
    Interesting.... I'll see if I can d something using len(tabledefs.name) and mid() in a loop. The entire database consists of a maximum of 4 tables, so looping through a f ew tabls that way wuld be preferable to messing about in the sys tables.

    Thanks for the advice though, it might come in handy in larger db's.

    Cheers, Trin
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

Posting Permissions

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