Crystal,
Why can't I use something like
if not isnull(sName) then
stSQL = "DROP TABLE [" & sTablename & "];"
Db.Execute stSQL
Db.Execute stSQL
end if
Adam
---In MS_Access_Professionals@yahoogroups.com, <strive4peace2008@...> wrote :
---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
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
__._,_.___
Posted by: runuphillracing@yahoo.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar