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

Senin, 02 November 2015

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

You could do what you suggest but I think Crystal is trying to get you to think about reusing code.

If you save the procedure in a module the code could be called from other forms in your database if you find the need.

Also the module of generic utilities you accumulate can be copied into new databases and you would not need to rewrite the code.

Bob Peterson

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, November 02, 2015 3:27 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Make table query in VBA

Crystal,

Thanks for the reply. However, it didn't get at what I really wanted. I'm looking to understand, not just solve the proximate problem.

sName = pdb.TableDefs(sTablename).Name brings back the name. Presumably it's null if the table isn't there. Thus, again, why can't I just do as I asked? What additional and necessary does your DropTheTable do?

Or, even simpler, why can't I just do

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

Then, trap for the error that would occur if the table doesn't exist?

Thanks

Adam

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

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