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

Kamis, 29 Oktober 2015

Re: [MS_AccessPros] Make table query in VBA

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@yahoo.com [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