Test if DB or table exist

[Wayne-I-M]

Hi All

I have a code that deletes an external database (kill code), imports a new
copy of this (call it DB1), then deletes a few tables from another DB (call
it DB2) and imports new - up to date - copies of these table from DB1 into
DB2.

Sounds daft I know - but it's to ensure that field staff have an up to date
copy of the server backend on any stand alone laptops.

Here is the problem (never underestimate the need for field staff to mess
things up). Everything works fine "unless" either of the DB is missing or
any of the table that are being deleted or imported is not there. How is
this possible hmmmm your guesse is as good as mine.

Is there a way to check that a DB exists or table exists in an external or
home system?

Something like

Iff DB1 exists then
Delete it
Import a new copy
Else
Import a new copy
End if

At the moment the code only works if the DB1 is there - or it can not delete
it so the code stops. The same goes for the tables - if they are not there
they can't be re-written etc etc.

Any tips would be really great


________________________________________

It's not really important but here is the code (the first line is a form -
frmMerchanttimerWait - to tell the field staff not to touch anything - they
ignor it)

Private Sub cboImport_Click()
DoCmd.OpenForm "frmMerchanttimerWait", acNormal, "", "", , acNormal
Dim strOldPathName As String
Dim strNewPathName As String
Kill "C:\Documents and Settings\UKOPS\My Documents\UKOP_Stuff\NewVenture.mdb"
strOldPathName = "S:\NewVenture.mdb"
strNewPathName = "C:\Documents and Settings\UKOPS\My
Documents\UKOP_Stuff\NewVenture.mdb"
DBEngine.CompactDatabase strOldPathName, strNewPathName
DoCmd.DeleteObject acTable, "tblBookings"
DoCmd.DeleteObject acTable, "tblClients"
DoCmd.DeleteObject acTable, "tblEvents"
DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Documents and
Settings\UKOPS\My Documents\UKOP_Stuff\NewVenture.mdb", acTable,
"tblBookings", "tblBookings", structureonly:=False
DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Documents and
Settings\UKOPS\My Documents\UKOP_Stuff\NewVenture.mdb", acTable,
"tblClients", "tblClients", structureonly:=False
DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Documents and
Settings\UKOPS\My Documents\UKOP_Stuff\NewVenture.mdb", acTable, "tblEvents",
"tblEvents", structureonly:=False
End Sub

 

[aaron.kempf]

If Exists(Select Name From Master.dbo.SysDatabases Where Name like
'susiedatabase')
BEGIN
drop database susiedatabase
END


If Exists(Select Name From SysObjects Where Name like 'susietable')
BEGIN
drop database susietable
END

 

[Katrina]

You can use the DIR function. That will tell you if the db exists. You have
to know the path of the db.

myfile = Dir(db1pathandname)
If myfile <> "" Then 'file exists
delete old
copy new
Else
'does not exist
copy new
End If

To find out if a table exists in the current database you use the tabledefs
object. Not sure about how to do it in an external database.

Dim db As Database, tdf As TableDef
Set db = CurrentDb()
For Each tdf In db.TableDefs
If tdf.Name = "desiredtablename" Then
'desired table exists do whatever you want with it.
End If
Next tdf

Hope this helps.

Kat

 

[Wayne-I-M]

Many thanks for the tip. I will give it a try in the office tommorrow.


--
Wayne
Manchester, England.

You can use the DIR function. That will tell you if the db exists. You have
to know the path of the db.

myfile = Dir(db1pathandname)
If myfile <> "" Then 'file exists
delete old
copy new
Else
'does not exist
copy new
End If

To find out if a table exists in the current database you use the tabledefs
object. Not sure about how to do it in an external database.

Dim db As Database, tdf As TableDef
Set db = CurrentDb()
For Each tdf In db.TableDefs
If tdf.Name = "desiredtablename" Then
'desired table exists do whatever you want with it.
End If
Next tdf

Hope this helps.

Kat

 

[John Nurick]

Hi Wayne,

Does a file exist?

If Len(Dir(strFileName)) > 0 Then
'File exists
...
End If

Does a table exist?
http://groups.google.co.uk/groups?as_q=group:microsoft.public.access.*+tableexists+function

 

[Wayne-I-M]

Thanks John

I will also try that in the morning.

The basic problems is that I really should not have to code this - BUT -
there is no way of securing a db on a laptop that a bored field rep will not
find a way around so ....

Ain't the real world great

Many thanks again

 

[dbahooker]

Wayne;

quit your fucking bitching.

there is a way to secure your databases. it is called ACCESS DATA
PROJECT.

ADP has superior replication to anything else on the market

-Aaron

 

[Wayne-I-M]

Many thanks to you both the code works like a dream. I have deleted various
files, tables, even the whole external database and the (rather long) code
run smoothly through the process - even when I renamed file incorrectly, and
tried deleting relationships, etc.

Cheers