Delay Command. 2

[lars7]

Hi There,
I was wondering if it's possible to run a query but after a time delay. I want to run 2 make table queries in a database in another department the first will trigger the booting sequence in their database and the second will replace the existing staff table with up to date information. The booting sequence takes approx 2 minutes so it would be after that I would like the second query to start. 1 day a week I want to update the information in their table and if possible I would like this to happen from 1 button. The code below boots the users and replaces the staff table but I need to delete the booting table to allow the users back in which is not working, it said it can't find the object.

Private Sub cmd4_Click()
On Error GoTo cmd4_Err
DoCmd.SetWarnings False
DoCmd.OpenQuery "qrybootprimarycare", acViewNormal, acEdit
DoCmd.OpenQuery "qryprimarycaretable", acViewNormal, acEdit
DoCmd.DeleteObject acTable, "C:\Documents and Settings\Tom\Desktop\tblversionserver"
DoCmd.SetWarnings True
cmd4_Exit:
Exit Sub
cmd4_Err:
MsgBox Error$
Resume cmd4_Exit
End Sub

 

[jrbarnett]

If you need to delete a table from the database, you can do something like

DoCmd.RunSQL "DROP TABLE YourTableName;"

If the table is a file on disk and you just need to delete that file, look up the Kill statement in the VBA help.

John

 

[lars7]

Hi jrbarnett,
Yes that worked fine for a table in my database but it's a table in another datase that I want to delete. I tried it with this path name but got syntax error in Drop Table or Drop Index message.

"C:\Documents and Settings\Tom\Desktop\tblversionserver

 

[Remou]

Perhaps:

Dim wrk As DAO.Workspace
Dim db As DAO.database
Set wrk = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrk.OpenDatabase(CurrentProject.Path & "\tek-tips.mdb")

strSQL = "DROP TABLE tblTable;"
db.Execute strSQL

 

[lars7]

Hi I noticed that I had not put in the database name but I have added it to the delete object and the now a kill strpath and it still hasn't worked.

Public Function DeleteInput()
Dim strPath
strPath = "C:\Documents and Settings\Tom\Desktop\primarycare.mdb\tblversionserver"
If Dir(strPath) <> "" Then Kill strPath
End Function

 

[Remou]

Kill is for files, not tables. You will need something similar to my snippet to delete a table from a database. The idea is to open the database in another workspace and then delete the table. You will also need error checking and clean up code.

 

[RoyVidar]

An ADO version could possibly look something like this

[tt]dim cn as adodb.connection
set cn = new adodb.connection
cn.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Tom\Desktop\primarycare.mdb;" & _
"User Id=admin;Password=;"
' or connection string from for instance
'

http://www.carlprothman.net/

cn.open
cn.execute "drop tblMyTable",,adcmdtext+adexecutenorecords
cn.close[/tt]

Roy-Vidar

 

[lars7]

Hi Remou,
I think I've missed something I'm gettting a "Not a File Name" message.
Here is what I have:

Private Sub cmd4_Click()
On Error GoTo cmd4_Err
Dim wrk As DAO.Workspace
Dim db As DAO.Database
DoCmd.SetWarnings False
DoCmd.OpenQuery "qrybootprimarycare", acViewNormal, acEdit
DoCmd.OpenQuery "qryprimarycaretable", acViewNormal, acEdit
Set wrk = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrk.OpenDatabase(CurrentProject.Path & "\primarycare.mdb C:\Documents and Settings\Tom\Desktop")
strSQL = "DROP TABLE tblversionserver;"
db.Execute strSQL
DoCmd.SetWarnings True
cmd4_Exit:
Exit Sub
cmd4_Err:
MsgBox Error$
Resume cmd4_Exit
End Sub

and do you think the first part of my original post is possible.

 

[Remou]

Sorry, I should have made that a bit clearer. This:
[tt]Set db = wrk.OpenDatabase(CurrentProject.Path & "\primarycare.mdb C:\Documents and Settings\Tom\Desktop")
strSQL = "DROP TABLE tblversionserver;"[/tt]
Should read:
[tt]Set db = wrk.OpenDatabase("C:\Documents and Settings\Tom\Desktop\primarycare.mdb")
strSQL = "DROP TABLE tblversionserver;"[/tt]

Where "C:\Documents and Settings\Tom\Desktop\primarycare.mdb" is the full name and path of the database you want to delete from.

As an aside, RoyVidar has reminded us that DAO is not going to last for much longer, so it may be best to look at ADO, which will be around for a bit longer.

Regarding the timing, yes it can be done in several ways, let me think about which might suit (and then come up with the wrong answer ).

 

[lars7]

Hi Roy,
I'm getting a "syntax error in drop table" again I'm probably missing something but deleting the table can only happen after I have booted the users which will take 2 minute or so and then replace the staff table.

Private Sub cmd4_Click()
On Error GoTo cmd4_Err
Dim cn As adodb.Connection
Set cn = New adodb.Connection
DoCmd.OpenQuery "qrybootprimarycare", acViewNormal, acEdit
DoCmd.OpenQuery "qryprimarycaretable", acViewNormal, acEdit
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Tom\Desktop\primarycare.mdb;" & _
"User Id=admin;Password=;"
cn.Open
cn.Execute "drop tblversionserver", , adCmdText + adExecuteNoRecords
cn.Close
'Dim wrk As DAO.Workspace
'Dim db As DAO.Database
'DoCmd.SetWarnings False
'DoCmd.OpenQuery "qrybootprimarycare", acViewNormal, acEdit
'DoCmd.OpenQuery "qryprimarycaretable", acViewNormal, acEdit
'Set wrk = CreateWorkspace("", "admin", "", dbUseJet)
'Set db = wrk.OpenDatabase(CurrentProject.Path & "\primarycare.mdb C:\Documents and Settings\Tom\Desktop")
'strSQL = "DROP TABLE tblversionserver;"
'db.Execute strSQL
DoCmd.SetWarnings True
cmd4_Exit:
Exit Sub
cmd4_Err:
MsgBox Error$
Resume cmd4_Exit
End Sub

 

[RoyVidar]

Remou,
Have I what???

What I personally think or believe is that ADO and DAO dies pretty much at the same time, which is when Access is "dot-net-i-fied", at which time we will start doing ADO.Net in stead - and that is quite a different animal from both DAO and ADO ;-)

Lars7,
Remous syntax is correct, it's the missing keyword "TABLE"

Roy-Vidar

 

[lars7]

Yes I tried this and It worked:

Private Sub cmd4_Click()
On Error GoTo cmd4_Err
Dim cn As adodb.Connection
Dim wrk As DAO.Workspace
Dim db As DAO.Database
DoCmd.SetWarnings False
DoCmd.OpenQuery "qrybootprimarycare", acViewNormal, acEdit
DoCmd.OpenQuery "qryprimarycaretable", acViewNormal, acEdit
Set wrk = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrk.OpenDatabase("C:\Documents and Settings\Tom\Desktop\primarycare.mdb")
strSQL = "DROP TABLE tblversionserver;"
db.Execute strSQL
DoCmd.SetWarnings True
cmd4_Exit:
Exit Sub
cmd4_Err:
MsgBox Error$
Resume cmd4_Exit
End Sub

and I will be happy to wait Remou.

 

[Remou]

Roy-Vidar
Um, but you .. they said .. I er

 

[lars7]

:-D

 

[RoyVidar]

Sleep API is one possible method - but it freezes your app while it "sleeps"

[tt]' in the declaration section
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

' where you wish the delay
Sleep 3000 ' three seconds[/tt]

I found this method demonstrating both sleep, and another one - and discussing a bit of pros and con, I haven't tried this in Access, though

http://support.microsoft.com/kb/q231298/

You could of course use the forms timer too, start it off when running this routine, then after a given interval fire off the remaining code, which will allow the GUI to be repainted, used ... but perhaps better ensure the form can't be closed during the process.

But - if all of these operations is on another db, could perhaps use this

http://support.microsoft.com/kb/q200300/

be of any use?

Roy-Vidar

 

[lars7]

Hi,
Thanks Roy I used a form opened in hidden mode that has a three minute timer and on close it sets in motion the remaining code.

Private Sub cmd4_Click()
On Error GoTo cmd4_Err
DoCmd.SetWarnings False
DoCmd.OpenQuery "qrybootprimarycare", acViewNormal, acEdit
DoCmd.OpenForm "frmallclear2", acNormal, "", "", , acHidden
DoCmd.SetWarnings True
cmd4_Exit:
Exit Sub
cmd4_Err:
MsgBox Error$
Resume cmd4_Exit
End Sub

and in the hidden form.

Private Sub Form_Close()
On Error GoTo Err_Handler
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryprimarycaretable", acViewNormal, acEdit
DoCmd.OpenQuery "qryallowprimarycare", acViewNormal, acEdit
DoCmd.SetWarnings True
Exit_Here:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here
End Sub

I had to take out the delete table code because I need that table in the other database as there is a hidden form that will scan for it every 30 seconds to see if there is a value in the "logoutallusers" field so I will replace that table with another, with the same name, which has no value in that field with the "qryallowprimarycare" query. Sorry Remou for wasting time with the delete table but I never thought that one through properly but I have the code for future reference. As always thanks for all the help.