Run make table query and create new database 2

[tnguyen315]

Hello,
I have a question, don’t know if it’s possible …

First, I have a form with 2 text fields and “BeginDate” and “EndDate” and a button “cmdRunQuery”. When the user enter data into 2 text fields with date format “MM/DD/YYYY”, and hit “ Run Query” . It’s working fine and run a make table query on the external database on network. However, my concern is if somehow, someone delete the database on network, then I get the error when running the make table query.
So Is there anyway to check the database on network first?, If the database exist then, run make table query. If the database is not exist, then create a new database on the same location and then run make table query. The database is on K drive, K:\Custom\IVD\Main\AccessSQL\AHD.mdb

Below is my code for run make table query. (Run make 4 tables at the same time, tblA, tblB, tblC and tblD)

Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery__Click

Dim stDocName1 As String
Dim stDocName2 As String
Dim stDocName3 As String
Dim stDocName4 As String

Dim rst As Recordset
Dim wsp As Workspace

If IsNull(Me.BeginDate) Or (Me.BeginDate) = "" Then
MsgBox "Please Enter Begin Date!", vbOKOnly, "Invalid Criterion!"
Me![BeginDate].SetFocus
Exit Sub
ElseIf IsNull(Me.EndDate) Or (Me.EndDate) = "" Then
MsgBox "Please Enter End Date!", vbOKOnly, "Invalid Criterion!"
Me![EndDate].SetFocus
Exit Sub
Else
I WOULD LIKE SOME CODE RIGHT HERE BUT DON”T KNOW HOW…
IF THE DATABASE “AHD.mdb” IS NOT EXIST THEN
CREATE NEW DATABASE on K:\Custom\IVD\Main\AccessSQL\AHD.mdb
ELSE ‘run code below


DoCmd.SetWarnings False
DoCmd.Hourglass True

stDocName1 = "qry_MakeTableA"
stDocName2 = "qry_MakeTableB"
stDocName3 = "qry_MakeTableC"
stDocName4 = "qry_MakeTableD"

DoCmd.OpenQuery stDocName1, acNormal, acEdit
DoCmd.OpenQuery stDocName2, acNormal, acEdit
DoCmd.OpenQuery stDocName3, acNormal, acEdit
DoCmd.OpenQuery stDocName4, acNormal, acEdit

DoCmd.SetWarnings True
DoCmd.Hourglass False

MsgBox ("Actions Completed!")

ENDIF
End If
Exit_cmdRunQuery__Click:
Exit Sub

Err_cmdRunQuery__Click:
MsgBox Err.Description
Resume Exit_cmdRunQuery__Click
End Sub

Please help, I’m very appreciated. Thanks

 

[longhair]

tnguyen315,
why not just restrict the user rights to the folder / db? only allow admins the ability to delete.
regards,
longhair

 

[PWise]

Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery__Click

Dim stDocName1 As String
Dim stDocName2 As String
Dim stDocName3 As String
Dim stDocName4 As String
dim db as database
Dim rst As Recordset
Dim wsp As Workspace

If IsNull(Me.BeginDate) Or (Me.BeginDate) = "" Then
MsgBox "Please Enter Begin Date!", vbOKOnly, "Invalid Criterion!"
Me![BeginDate].SetFocus
Exit Sub
ElseIf IsNull(Me.EndDate) Or (Me.EndDate) = "" Then
MsgBox "Please Enter End Date!", vbOKOnly, "Invalid Criterion!"
Me![EndDate].SetFocus
Exit Sub
Else

ELSE 'run code below
[COLOR=red ]
if dir("K:\Custom\IVD\Main\AccessSQL\AHD.mdb")="" then
Set db = CreateDatabase ("K:\Custom\IVD\Main\AccessSQL\AHD.mdb")
end if [/color]
DoCmd.SetWarnings False
DoCmd.Hourglass True

stDocName1 = "qry_MakeTableA"
stDocName2 = "qry_MakeTableB"
stDocName3 = "qry_MakeTableC"
stDocName4 = "qry_MakeTableD"

DoCmd.OpenQuery stDocName1, acNormal, acEdit
DoCmd.OpenQuery stDocName2, acNormal, acEdit
DoCmd.OpenQuery stDocName3, acNormal, acEdit
DoCmd.OpenQuery stDocName4, acNormal, acEdit

DoCmd.SetWarnings True
DoCmd.Hourglass False

MsgBox ("Actions Completed!")

ENDIF
End If
Exit_cmdRunQuery__Click:
Exit Sub

Err_cmdRunQuery__Click:
MsgBox Err.Description
Resume Exit_cmdRunQuery__Click
End Sub

 

[tnguyen315]

Thanks all for your respond...
Pwise, I got an error when running it and stop at

Set db = CreateDatabase ("K:\Custom\IVD\Main\AccessSQL\AHD.mdb")

Error: "Compile error: Argument not optional"
Please help

 

[tnguyen315]

Thanks pwise,....
I got it works...I play around with it and change to
Set dbs = CreateDatabase("K:\Custom\ENV\Food\Food-DHD\Food_DHD.mdb", dbLangGeneral)

 

[PWise]

how about

Set db = CreateDatabase ("K:\Custom\IVD\Main\AccessSQL\AHD.mdb",dbLangGeneral)

 

[tnguyen315]

Thanks pwise, that's what I did and got it works thanks...
BTW, I want something in the else option

if dir("K:\Custom\IVD\Main\AccessSQL\AHD.mdb")="" then
Set db = CreateDatabase ("K:\Custom\IVD\Main\AccessSQL\AHD.mdb")
else

EITHER DELETE AND RE-CREATE THE DATABASE or COMPACT AND REPAIR THE DATABASE...
end if

Please help, I'm greatly appreciated.
Thanks

 

[PHV]

If Dir("K:\Custom\IVD\Main\AccessSQL\AHD.mdb") <> "" Then
Kill "K:\Custom\IVD\Main\AccessSQL\AHD.mdb"
End If
CreateDatabase "K:\Custom\IVD\Main\AccessSQL\AHD.mdb", dbLangGeneral

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tnguyen315]

Thanks PHV, it works like a charm...
Again, thank you .

 

[tnguyen315]

These code below was working fine yesterday, however, today I got an error and cann't run make table query.
Error said

"Could not use 'K:\Custom\IVD\Main\AccessSQL\AHD.mdb'; file already in use"

Please help, thanks.