The Access 97 database I was trying to get working on my 365 Access was written to scramble data in existing tables in a destructive way so that users of a forum or other developers could look at code without seeing sensitive data.

I copied some large tables I had from a csv staging and importing system I’m working on and started the form. I tried to run the table scanner to pull in the tables and fields so that I could select them to be scrambled, but I started getting errors right away. Looking at the code, it’s running a query on internal system tables to try to get the list of tables for scrambling. It seems to also be looking at the fields and types. I suppose in Access 97 at least, it was likely faster to look at / query a system table or multiple system tables to get this information, but it seems that it must have changed the internal structures.

I would suppose that grabbing the TableDefs object and looping over it could be much slower which would make a difference in this routine if there were a lot of tables. Here is the query being run:

' Access 97 query to pull table names.
Set myRS = myDB.OpenRecordset("SELECT MSysObjects.Name FROM MSysObjects " & _
                              "WHERE (((MSysObjects.Name) Not Like 'MSys*' And " & _
                              "(MSysObjects.Name) Not Like 'USys*' And " & _
                              "(MSysObjects.Name) Not Like 'tmp*') AND " & _
                              "((MSysObjects.Type)=-32760 Or (MSysObjects.Type)=1 Or " & _
                              "(MSysObjects.Type)=6));", dbOpenDynaset)

Interestingly, the query is really only grabbing table names and then uses DAO Database and TableDef objects to get field types that it scrambles by default.

Overall this is a pretty simple app. I think I’ll write up my desired requirements tomorrow and maybe start updating this to work.