Avatar of Chris Bottomley
Chris Bottomley
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Open database

As I try to progress the creation of a 'safe' db copy I am thinking it will help to delete the table IF it exists in a remote database.  This will mean changes in table structure of the master will always be reflected in the test mule.

I will use select to Insert a new table into the closed db copy so how can I test if the table exists in the db and delete it if so.  Note the script runs from the source db which WILL be open and the target may or may not be open so how can I delete a specific table in the copy irrespective of the db being open and / the table existing therein.

Chris
Microsoft AccessMicrosoft ApplicationsMicrosoft Office

Avatar of undefined
Last Comment
Chris Bottomley

8/22/2022 - Mon
macksm

IN microsoft access you cannot create or drop table with code. you have to do this in database design view. But in SQl SERver You can do this. it supports create table and drop table.
ASKER CERTIFIED SOLUTION
als315

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Scott McDaniel (EE MVE )

"IN microsoft access you cannot create or drop table with code"

This is not accurate. You can use standard DROP TABLE DDL commands to remove a table from an Access database, as asl315 has shown.

asl315: That's very interesting. I wasn't aware you could preface the object name with the full path to the database and have it work. I would have thought the syntax would be:

DROP TABLE [TableName] IN 'Full path to your database'

But I don't use this sort of syntax, so have no real-world knowledge of it's use.


Nick67

This code copies all the non-system tables from one database to another
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
    If Not tdf.Name Like "Msys*" Then
        DoCmd.TransferDatabase acExport, "Microsoft Access", Application.CurrentProject.Path & "\test.mdb", acTable, tdf.Name, tdf.Name, False
    End If
Next tdf

MsgBox "Done!"

Open in new window


You'd like to add the wrinkle of checking for pre-existence of any table and then dropping it first.
As per als315's contribution the code would then be:

Dim DropString As String
On Error Resume Next
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
    If Not tdf.Name Like "Msys*" Then
        DropString = "drop table " & SomeStringRepresentingTheFullPathtoYourTarget & "." & tdf.Name
        CurrentDb.Execute DropString
        DoCmd.TransferDatabase acExport, "Microsoft Access", SomeStringRepresentingTheFullPathtoYourTarget, acTable, tdf.Name, tdf.Name, False
    End If
Next tdf

MsgBox "Done!"
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
als315

nick67:
I think better will be to add square brackets
DropString = "drop table [" & SomeStringRepresentingTheFullPathtoYourTarget & "]." & tdf.Name
If path have spaces, it code will not work.
May be to table name also
Nick67

Ok @als315!
I sometimes forget that others may be using silly names thatdon't conform to good SQL Server conventions (like spaces in names)
This should cover the possibilities!

Dim DropString As String
On Error Resume Next
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
    If Not tdf.Name Like "Msys*" Then
        DropString = "drop table [" & SomeStringRepresentingTheFullPathtoYourTarget & "].[" & tdf.Name & "]"
        CurrentDb.Execute DropString
        DoCmd.TransferDatabase acExport, "Microsoft Access", SomeStringRepresentingTheFullPathtoYourTarget, acTable, tdf.Name, tdf.Name, False
    End If
Next tdf

MsgBox "Done!"
Chris Bottomley

ASKER
Took me a while to test ... totally missed the square brackets, and once added realised the solution already included them ... ho hum integrated into the existing code a treat after adding an error trap for cases where the table was already deleted.

Ta muchly
Chris
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.