Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB Databases
|
Pro VB Databases Advanced-level VB coding questions specific to using VB with databases. Beginning-level questions or issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 3rd, 2004, 07:37 AM
Authorized User
 
Join Date: Jun 2004
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default Deleting tables in Access Database, if they exist

How can this be done? I have written code to add tables from one database to another but then it will give me an error if the table already exits. Therefore I want to delete the table in the target database if it exists and replace it with the copied table from the source database.

here is the original code:
Public Sub DB_CopyTable(ByVal DBPathName$, ByVal TargetTable$, ByVal NewTableName$)
On Error GoTo DB_CopyTable_ErrorHandler:

    If TargetTable = "TEDSSEQFN" Or TargetTable = "TEDSSEDK_" Then
        cnxModelDB.Execute "SELECT " & TargetTable & ".* INTO " & TargetTable & " IN '" & DBPathName & "' From " & NewTableName
    Else
        cnxMacroLibrary.Execute "SELECT " & TargetTable & ".* INTO " & TargetTable & " IN '" & DBPathName & "' From " & NewTableName
    End If

    Exit Sub

DB_CopyTable_ErrorHandler:
      MsgBox "Error: " & Err.Description, vbCritical + vbOKOnly, "Error " & Err.Number & " in DB_CopyTable_errorhandler"
End Sub


Thanks in advance for your help.
Rgrds,
Maria

 
Old August 25th, 2004, 11:10 PM
Authorized User
 
Join Date: Apr 2004
Posts: 87
Thanks: 0
Thanked 0 Times in 0 Posts
Default

cn.Execute "if exists(select * from sysobjects where type = 'U' and name =
'YourTableName')" _
    & " begin drop table YourTableName end " _
    & " CREATE TABLE YourTableName (SaleID INT IDENTITY(100,1) , " _
    & " SalesRegion CHAR(2), " _
    & " SalesAmount smallmoney," _
    & " ISBN VarChar(20)" _
    & " CONSTRAINT ID_PK PRIMARY KEY (SaleID) )"








Similar Threads
Thread Thread Starter Forum Replies Last Post
Selecting Two Tables in Access Database hollertrek General .NET 12 December 7th, 2007 12:29 AM
re-link tables in a .mde when .mdb does not exist poyserr Access 1 February 22nd, 2007 12:49 PM
deleting records in linked tables tico31pl Access 4 May 18th, 2006 04:10 PM
Deleting Records Within Relational Tables rit01 SQL Server 2000 5 May 11th, 2006 03:11 AM
Deleting Tables in SQL using ASP and VB Script sideshow245 Classic ASP Databases 1 February 2nd, 2004 07:38 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.