Drop Table IF EXISTS in MS access 1

[Korizon67]

I have seen many ways to do this, but am not fluent in functions, can someone step me thru how to do this? I want to check and see if the Table leads and leads_ImportErrors exists and if so drop them

Thank you for any help

Mike

 

[PHV]

And what have you tried so far and where are you stuck in your code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Korizon67]

I have a function that Heather floyd made, but I am a SQL guy and really dont know much about the innerworkings of access...

no idea how to paste code here so...

Function TableExists(TableName As String) As Boolean
'================================================= ============================
' hlfUtils.TableExists
'-----------------------------------------------------------------------------
' Copyright by Heather L. Floyd - Floyd Innovations -

http://www.floydinnovations.com

' Created 08-01-2005
'-----------------------------------------------------------------------------
' Purpose: Checks to see whether the named table exists in the database
'-----------------------------------------------------------------------------
' Parameters:
' ARGUEMENT : DESCRIPTION
'-----------------------------------------------------------------------------
' TableName (String) : Name of table to check for
'-----------------------------------------------------------------------------
' Returns: True, if table found in current db, False if not found.
'================================================= ============================

Dim strTableNameCheck
On Error GoTo ErrorCode

'try to assign tablename value
strTableNameCheck = CurrentDb.TableDefs(TableName)

'If no error and we get to this line, true
TableExists = True

ExitCode:
On Error Resume Next
Exit Function

ErrorCode:
Select Case Err.number
Case 3265 'Item not found in this collection
TableExists = False
Resume ExitCode
Case Else
MsgBox "Error " & Err.number & ": " & Err.Description, vbCritical, "hlfUtils.TableExists"
'Debug.Print "Error " & Err.number & ": " & Err.Description & "hlfUtils.TableExists"
Resume ExitCode
End Select

End Function

<<< then she has this >>>

If TableExists("MYTABLE") Then
CurrentDb.TableDefs.Delete ("MYTABLE")
End If

Not sure where to put all that, I assume a module... but do they go in seperate ones?

 

[PHV]

Why not simply this ?

On Error Resume Next
DoCmd.RunSQL "DROP TABLE leads"
DoCmd.RunSQL "DROP TABLE leads_ImportErrors"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Korizon67]

Simple for a smart guy but for a dumb dumb like me hehe... would I run that as a module?

Sorry for my ignorance
and Thank you

Mike

 

[PHV]

How do you expect to launch this code ?
With a button on a form ?
If so then write the code in the Click event procedure.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Korizon67]

Sorry, What I am doing is pulling a CSV file with WGET then importing it to MS access and pushing it to MySQL. Its been working fine because every time we get a leads table and a leads_ImportErrors table...

Recently due to a change on the website (we get the csv from) and some help from here on how to strip bad data. We are now not getting the errors all the time. So what I mostly want is to delete the errors table if it shows up. But also once in a great while we lose net and the csv fails to download.

When this happens we get repeat leads imported into MySql (which is very bad for us because the person in charge of watching never does and just submit dupes to the db from the pre leads table) Anywho the way it did work it ran a macro, transfered the text, dropped the table and error table. then ran some php scripts via a BAT file.

What i would like to do now is drop all tables when access opens via a autoexec macro, this way if we lose internet we dont get the same file loaded into our pre leads table. so basically a batch opens access deletes the table, makes the mysql table and then deletes the leads and leads errors if they exist again. may seem dumb but its always worked except when we dont have internet to get the csv.

I altered my wget batch to drop the files ahead of the get incase the net is down. so pretty much if it doesnt work access will halt... i would rather have a halt than dupe leads.

I probably confused you more that anything with all this, and by all means if you know a better way to import the csv and do away with things let me know... I tried importing them directly into access from the leads site, but for some reason it wants a login... even tho it doesnt require one with wget

 

[Korizon67]

I forgot to mention this is all automated, a batch file opens access, which autoexecs a macro and then i have vbs that closes it all when its done. it runs a few different things... Wget, Firefox for some php scripts .. some vbs scripts for fixing the bad data in the csv.... that kinda thing

Thanks again mike

 

[KornGeek]

PHV,

I had some ideas for how to do this, but none of them were nearly so concise or elegant. I'm blown away that you did that in only 3 lines of code and I'm also wondering why I didn't think of that.


Mike,

Create a function in VBA that does exactly what PHV posted above. Then, either insert a function call into the code that calls your macro (if it's tied to a button on a form or something similar), or add a RunCode action to your macro to call this function.

Side note: I despise using macros in almost all circumstances and generally advise converting to code, but that may not be practical in your situation.

 

[Korizon67]

Thanks for all the input guys I ended up getting the information from the site dev to directly query the mysql database .... I then wrote a multi connect script and copied the table directly sql to sql.... so much cleaner that way. again ty for the replies,.. i now have a new question for the mysql boards


Thanks

Mike