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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"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.
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.
This code copies all the non-system tables from one database to another
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 " & SomeStringRepresentingTheF
CurrentDb.Execute DropString
DoCmd.TransferDatabase acExport, "Microsoft Access", SomeStringRepresentingTheF
End If
Next tdf
MsgBox "Done!"
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!"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 " & SomeStringRepresentingTheF
CurrentDb.Execute DropString
DoCmd.TransferDatabase acExport, "Microsoft Access", SomeStringRepresentingTheF
End If
Next tdf
MsgBox "Done!"
nick67:
I think better will be to add square brackets
DropString = "drop table [" & SomeStringRepresentingTheF
If path have spaces, it code will not work.
May be to table name also
I think better will be to add square brackets
DropString = "drop table [" & SomeStringRepresentingTheF
If path have spaces, it code will not work.
May be to table name also
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 [" & SomeStringRepresentingTheF
CurrentDb.Execute DropString
DoCmd.TransferDatabase acExport, "Microsoft Access", SomeStringRepresentingTheF
End If
Next tdf
MsgBox "Done!"
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 [" & SomeStringRepresentingTheF
CurrentDb.Execute DropString
DoCmd.TransferDatabase acExport, "Microsoft Access", SomeStringRepresentingTheF
End If
Next tdf
MsgBox "Done!"
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
Ta muchly
Chris