Programmatically omitting "Linked" Tables (1 Viewer)

gblack · Apr 13, 2009

I have code that deletes all the data from all the non-system tables in the database:
--------------------------------------------------------------------------

Code:

Dim dbCurr As DAO.Database
Dim intLoop As Integer

Set dbCurr = CurrentDb()

  For intLoop = (dbCurr.TableDefs.Count - 1) To 0 Step -1
  
    If Not Left$(dbCurr.TableDefs(intLoop).Name, 4) = "Msys" Then
        DoCmd.RunSQL "DELETE * FROM " & dbCurr.TableDefs(intLoop).Name & ";"
    End If
    
  Next intLoop
  
Set dbCurr = Nothing

--------------------------------------------------------------------------

Now that my process has grown, I have tables which are linked to another database.

When I run the above code, I do not want to delete the data from the linked tables (only the data from the tables which reside in my Database). My question is:

Is there anyway to distinguish a "linked" table from the rest of the tables in the database, so that I can programmatically omit the "linked" tables from this data delete process?

Thanks,
Gary

pbaldy · Apr 13, 2009

If you were going through the MSysObjects table, I'd say to use the Type field. I don't know if that's available with a TableDef, so you could probably look at the Connect property. If it has anything in it, it would be a linked table, otherwise it's local.

gemma-the-husky · Apr 13, 2009

one way is something like

if tdf.attributes and dbattached = dbattached

then its a linked table

gblack · Apr 13, 2009

Sweet! That change works for both system and linked tables.

Code:

Dim dbCurr As DAO.Database
Dim intLoop As Integer

Set dbCurr = CurrentDb()

For intLoop = (dbCurr.TableDefs.Count - 1) To 0 Step -1

  'delete data from nonsystem/nonlinked tables
  If Not dbCurr.TableDefs(intLoop).Attributes And Not dbattached = dbattached Then
      DoCmd.RunSQL "DELETE * FROM " & dbCurr.TableDefs(intLoop).Name & ";"
  End If
  
Next intLoop
  
Set dbCurr = Nothing

One question though... It also seems to work fine without the: "And Not dbattached = dbattached", so why was this added?

I honestly don't know what's going on there... I'd love an explanation if you have time...

Thanks much!
Gary

dallr · Apr 14, 2009

You can also do this via the SysObjects table as Paul pointed out, and use the "WHERE TYPE = xxx". Replace xxx with 1, 6 or 4 as indicated below.

Access Tables = 1

Linked Access tables = 6

Link SQL Server Table = 4

Dallr

gemma-the-husky · Apr 14, 2009

do you not have option explicit set? it should have pciked up an error with dbattached

i've just checked, and its not dbattached (i said it was something like) - it should be dbattachedtable

look at help for table attributes, and you will see various settings that can be tested with a boolean add. i suspect that you may have been deleting all your data, not just (or only) linked tables.

gblack · Apr 14, 2009

No... I had option compare database.

Honestly I don't know enough about what the "option" is all about... don't even know why or how that was set like it was... I didn't write it... I probably copied it from somewhere else.

Now that I have "Option Explicit" set, it did give me an error when I ran the code... I changed: dbattached = dbattached to dbattachedtable = dbattachedtable

And it ran fine.

Dallr: Thanks for the information about the SysObjects table... whose data I mayhave I inadvertantly deleted... Yikes!

Um... Next question: So how do I access my system tables to see if they are now empty? LOL

Thanks,
-Gary

PS. I am ashamed to say that I've been using MS Access for well over a decade and with every new issue like this I feel like know less and less... not more and more. Sheesh! But thanks to everyone for their input!

gblack · Apr 14, 2009

Looks like my MsysObjects table is still intact

dallr · Apr 17, 2009

You cannot delete data from MsysObjects table.
Glad to help.

Dallr

gemma-the-husky · Apr 17, 2009

just open the table to see if it contains data

linked tables will havbe a little arrow against them

Programmatically omitting "Linked" Tables (1 Viewer)

gblack

Registered User.

pbaldy

Wino Moderator

gemma-the-husky

Super Moderator

gblack

Registered User.

dallr

AWF VIP

gemma-the-husky

Super Moderator

gblack

Registered User.

gblack

Registered User.

dallr

AWF VIP

gemma-the-husky

Super Moderator

Similar threads

Users who are viewing this thread