ACCESS FEED: Re: [MS_AccessPros] Re: Make table query in VBA

Jumat, 30 Oktober 2015

Re: [MS_AccessPros] Re: Make table query in VBA

hi Adam,

DropTheTable does everything -- just put it into a general module (so other code can use it too -- or it can be in the same module as wou are running from if that is the only place you need it) and run it first

in your code, add this statement before the rest:
call DropTheTable(CurrentDb, "MyTablename")

if the table is there, it will be deleted. If not, nothing will happen and you can continue running whatever you need to.

warm regards,
crystal

Learn Access Playlist on YouTube
http://www.youtube.com/view_play_list?p=1B2705CCB40CA4CA

~ have an awesome day ~

On 10/30/2015 3:15 PM, runuphillracing@yahoo.com [MS_Access_Professionals] wrote:

Crystal,

Why can't I use something like

   if not isnull(sName) then

      stSQL = "DROP TABLE [" & sTablename & "];"
     Db.Execute stSQL

   end if

Adam

---In MS_Access_Professionals@yahoogroups.com, <strive4peace2008@...> wrote :

Hi Adam,

run this code first:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Sub DropTheTable( _
   pdb As DAO.Database _
   , sTablename As String _
   )
'150821 s4p
'deletes a table from the passed database reference
'if the table is not there to delete, no error will be returned

    Dim sName As String

    On Error GoTo Proc_Err

    'See if the table is there
    sName = pdb.TableDefs(sTablename).Name

    'If no error then table is there -- delete it
    With pdb
      .Execute "DROP TABLE [" & sTablename & "];"
      .TableDefs.Refresh
   End With
   DoEvents


Proc_Exit:
   On Error Resume Next
    Exit Sub

Proc_Err:

    Select Case Err.Number
      Case 3265 'Table does not exist
      Case Else
         MsgBox Err.Description, , _
           "ERROR " & Err.Number _
           & "   DropTheTable"
   End Select

   Resume Proc_Exit
   Resume

End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On 10/29/2015 2:14 PM, runuphillracing@... [MS_Access_Professionals] wrote:

If I run a make table query from the database window, and the table I want to make already exists, it prompts me, warns me about deleting the existing table first, and then replaces it. If I run it through VBA, I get "RT error 3010, Table 'tblName2' already exists."

stSQL = "SELECT tblName1.* INTO tblName2 FROM tblName1
Db.Execute stSQL, dbFailOnError

How do I write the VBA so that it will replace an existing table?

Adam