UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Delete All "*" & "temp" & "*" Tables, Access 2016    
 
   
Knuckles
post Oct 7 2019, 11:20 AM
Post#1



Posts: 901
Joined: 1-February 10
From: New Jersey



Greetings UtterAccess,

I have inherited a database where I need a method to delete/drop all tables named "*" & "tbltemp" & "*" on demand.
I've been searching but not successful in finding a solution.

Any help would be greatly appreciated. Thanks in advance.

Knuckles



Go to the top of the page
 
theDBguy
post Oct 7 2019, 11:27 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,416
Joined: 19-June 07
From: SunnySandyEggo


Hi. Do you mean "tbltemp" is somewhere in the name of the tables?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Oct 7 2019, 11:29 AM
Post#3


UA Admin
Posts: 35,897
Joined: 20-June 02
From: Newcastle, WA


You can do this in VBA, using the tableDefs collection.

Something like this ought to work.

DO IT FIRST ON A COPY OF THE ACCDB. MAKE SURE ALL TEMP TABLES ARE DELETED AND NO OTHER TABLES BEFORE RUNNING IT ON YOUR PRODUCTION ACCDB.
CODE
Public Sub DropTempTables()
    Dim tdf as DAO.TableDef
    For each tdf in CurrentDB.TableDefs
        If tdf.Name Like "*" & "tbltemp" & "*" Then
            tdf.Delete
        End If
    Next tdf

End Sub

This post has been edited by GroverParkGeorge: Oct 7 2019, 11:30 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Knuckles
post Oct 7 2019, 11:30 AM
Post#4



Posts: 901
Joined: 1-February 10
From: New Jersey


Hello DBguy,

Yes, that is correct.
Go to the top of the page
 
jleach
post Oct 7 2019, 11:34 AM
Post#5


UtterAccess Editor
Posts: 10,154
Joined: 7-December 09
From: St Augustine, FL


Hi, it's a bit risky, but first I'd collect a list of names, then delete the tabledef objects via DAO.

Something like this (aircode):

CODE
Dim TablesToDelete As String
Dim db As DAO.Database
Dim td AS DAO.TableDef

Set db = CurrentDb()

For Each td in db.TableDefs
  If InStr(1, td.Name, "tblTemp") <> 0
    Then TablesToDelete = TablesToDelete & ";"
  End If
Next td

'TablesToDelete should now contain a list of tables, separated by;
' remove the trailing;
If Len(TablesToDelete) Then TablesToDelete = Right(TablesToDelete, Len(TablesToDelete) - 1)

Dim v As Variant, s As String, i As Integer

v = Split(TablesToDelete, ";")

For i = 0 To UBound(v)
  s = Trim(CStr(v(i))
  db.TableDefs(s).Delete 'or maybe .Remove?
Next i

--------------------
Go to the top of the page
 
Knuckles
post Oct 7 2019, 11:40 AM
Post#6



Posts: 901
Joined: 1-February 10
From: New Jersey


Hello George,

I was hoping it would be something simple like that but when I went to debug I got
Compile error: method or data member not found
Go to the top of the page
 
theDBguy
post Oct 7 2019, 11:47 AM
Post#7


Access Wiki and Forums Moderator
Posts: 76,416
Joined: 19-June 07
From: SunnySandyEggo


QUOTE
...but when I went to debug I got
Compile error: method or data member not found

Hi. Which line is highlighted with the error?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Knuckles
post Oct 7 2019, 11:48 AM
Post#8



Posts: 901
Joined: 1-February 10
From: New Jersey


Hello Jack,

I created a public function.

s = Trim(CStr(v(i)) turned red when I went to debug.

Go to the top of the page
 
Knuckles
post Oct 7 2019, 11:49 AM
Post#9



Posts: 901
Joined: 1-February 10
From: New Jersey


the word "Delete" in tdf.Delete
Go to the top of the page
 
theDBguy
post Oct 7 2019, 11:54 AM
Post#10


Access Wiki and Forums Moderator
Posts: 76,416
Joined: 19-June 07
From: SunnySandyEggo


QUOTE
the word "Delete" in tdf.Delete

That should be available. But try it this way:

CODE
CurrentDb.TableDefs.Delete tdf.Name

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Knuckles
post Oct 7 2019, 11:59 AM
Post#11



Posts: 901
Joined: 1-February 10
From: New Jersey



I have attached a DB with just 1 table in it.
Attached File(s)
Attached File  TempTables.zip ( 31.77K )Number of downloads: 3
 
Go to the top of the page
 
Knuckles
post Oct 7 2019, 12:18 PM
Post#12



Posts: 901
Joined: 1-February 10
From: New Jersey



Fantastic, CurrentDb.TableDefs.Delete tdf.Name worked.

Thank you all for your help.

Knuckles cheers.gif
Go to the top of the page
 
theDBguy
post Oct 7 2019, 12:56 PM
Post#13


Access Wiki and Forums Moderator
Posts: 76,416
Joined: 19-June 07
From: SunnySandyEggo


Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Oct 7 2019, 01:39 PM
Post#14



Posts: 6,125
Joined: 11-November 10
From: SoCal, USA


hello, Knuckles, how are you? :)

QUOTE
I have inherited a database where I need a method to delete/drop all tables named "*" & "tbltemp" & "*" on demand.

i know you got a solution already, so hope you don't mind me asking why you need to delete temp tables on demand. i'm wondering if that means that temp tables are repeatedly created/deleted in the normal use of the app.

if that's the case, you might want to consider putting temp tables into a temp backend db instead. creating/deleting tables repeatedly in either your FE or BE db will case bloat over time.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
Knuckles
post Oct 7 2019, 02:05 PM
Post#15



Posts: 901
Joined: 1-February 10
From: New Jersey


Hi Tina,

Shared environment.
All users accessing a single front end. I know, yikes!!
Each user winds up creating up to 60 temp tables to generate reports that do not get deleted until they run them again in the upcoming month.
At least that's the way I understand it.

I just started a contracting assignment here last week so I don't have a lot to go on yet.

K
Go to the top of the page
 
tina t
post Oct 7 2019, 02:10 PM
Post#16



Posts: 6,125
Joined: 11-November 10
From: SoCal, USA


QUOTE
All users accessing a single front end. I know, yikes!!
Each user winds up creating up to 60 temp tables to generate reports that do not get deleted until they run them again in the upcoming month

no kidding, yikes to both! ;) but maybe you'll be able to exert some influence and get those design and usage flaws corrected, hon. good luck with it, and you know we all have your back if you run into any issues. :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
MadPiet
post Oct 7 2019, 08:03 PM
Post#17



Posts: 3,334
Joined: 27-February 09



Oh ouch. I walked into a contract job disaster once. Not quite like that, but really bad. You might have to educate them as to the risks they're taking by sharing a single front end. And put it in writing in case something blows up. Explain to them the risks they are taking by doing that, and the repercussions should something go really bad.

SQL Server lets you create tables in TempDB, which is session-specific, which means it will get cleaned up automatically when the user's session ends, but Access doesn't have that.
Go to the top of the page
 
Knuckles
post Oct 8 2019, 05:38 AM
Post#18



Posts: 901
Joined: 1-February 10
From: New Jersey



I am hoping that once I get settled in I will be able to educate them a bit and see if there is the possibility to address the issues.
They also have local Access tables in some of the front ends.

K
Go to the top of the page
 
WildBird
post Oct 8 2019, 07:08 PM
Post#19


UtterAccess VIP
Posts: 3,620
Joined: 19-August 03
From: Auckland, Little Australia


Couple of things, Tina and others are right, that is a terrible thing to have users create temp tables in a single front end :-)

Jack is right when he puts the table names into an array, then deleting from the array. Trying to loop the tabledefs AND deleting at the same time will cause issues as the index changes. Means you wont delete all them, and would need to do a few passes depending on how many there. referring to them by name, i.e. in a table or array, is the correct way.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
Knuckles
post Oct 8 2019, 08:05 PM
Post#20



Posts: 901
Joined: 1-February 10
From: New Jersey



Thanks WildBird.

I'll take another look at Jack's code but I know I had a problem with one of the lines turning red.
I called it out in an earlier post.

Hello Jack,

I created a public function.

s = Trim(CStr(v(i)) turned red when I went to debug.

Knuckles
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    22nd October 2019 - 08:49 AM