Delete Table

[Guest]

Hi, I have a Make Table Query in one source database that produces a Table in
a different destination database. I want to be able to run the source query
from the destination database. I have read on the newsgroups you have to do
this using db.Execute, this fails though as the Table already exists. I want
it to overwrite the table each time, probably a very simple task but is there
a command for deleting a table in vba? I have also read that it is best to
run a delete query then append query to repopulate but can this be done from
a different database?

 

[Guest]

Yes, you can delete a tablein VBA:
currentdb.TableDefs("Test3").delete
I think the alternate approach of the delete query followed by an append
query is the best approach.

I don't understand why you want to run the query in the destination
database. If it is a back end database where your data is stored, best
practice is to have nothing there but data. I know there are exceptions to
all rules, so I will accept that there is a really good reason to do that.

 

[Guest]

Thanks, tried this but I received a compile error - method or data member not
found. Am I missing something?

Set db = CurrentDb()

CurrentDb.TableDefs("Dealer Selection").Delete

It isn't a back-end database. The reason I want it to run in the destination
database is because it is run by a user who only accesses the destination db
and doesn't use the source db where the data comes from to produce the
results she wants to then work with further. I did not develop the source db
and it uses a lot of embedded queries to get the results, this is the best
way I could think of doing it.

 

[Tim Ferguson]

Hi, I have a Make Table Query in one source database that produces a
Table in a different destination database. I want to be able to run
the source query from the destination database. I have read on the
newsgroups you have to do this using db.Execute, this fails though as
the Table already exists. I want it to overwrite the table each time,
probably a very simple task but is there a command for deleting a
table in vba? I have also read that it is best to run a delete query
then append query to repopulate but can this be done from a different
database?

Number of points: you can manipulate a mdb other than the CurrentDB, but
you have to open a handle to it first:

Set dbOther = DBEngine.OpenDatabase(c_strOtherMDBPath, true, true)
dbOther.Execute "DROP TABLE TempTable", dbFailOnError

More to the point though, is that deleting and recreating tables leads to
a lot of file bloat unless you are regularly compacting too. It's putting
the other tables at risk of corruption too. You might be better advised
to create a special temporary mdb just for the temp file, and deleting it
at the end of the session.

Set dbTemp = DBEngine.CreateDatabase(c_strTemp, etc)
With dbTemp
.Execute strCreateTable, dbFailOnError
.Execute strExportDate, dbFailOnError

' etc

.Close
End With
Kill c_strTemp

Another approach, if you need to keep the temp table until the next time
it's refreshed, would be to empty it and append the new records; as
oppposed to deleting and starting afresh. There's less mangling of the
mdb file; bloat will happen but should be less; you don't have to
redefine the PK and other indexes (which don't get made after a make-
table query); and you don't have to take down any relationships.

Try this:

With dbOther
.Execute "DELETE FROM TempTable", dbFailOnError
.Execute "INSERT INTO TempTable SELECT etc etc ", dbFailOnError

End With


Sorry for the long post, but deleting and creating whole tables is a big
deal for a database and is practically always not just unneccessary but
undesirable. Think of it like changing the engine in a car when you only
need to change the oil...

Hope that helps


Tim F

 

[Guest]

Thanks Tim, this cracked it! I deleted all records using your 'delete from'
example in the current db and changed my make table query to an append query
in the source db and ran this from the current db successfully.

One other question, the append query is based on several other queries in a
fairly large database and takes a while to run. I can get it to display a
message box saying it has completed but is there a way of notifying the user
that the query is processing?

Thanks, Sue

 

[Guest]

Tried creating a pop up form with a label saying 'processing data please
wait' and opening in code before query is run then closing again - but the
form opens without displaying its contents until query finished?

I would have liked to use the progress meter I have read about but it seems
that you can only use this if your procedure has a known duration or number
of steps which I don't think will be the case for an append query? My VB
knowledge is VERY limited too ;-)

 

[Tim Ferguson]

One other question, the append query is based on several other queries
in a fairly large database and takes a while to run. I can get it to
display a message box saying it has completed but is there a way of
notifying the user that the query is processing?

This is from the help file on the .Execute method:

! In an ODBCDirect workspace, if you include the optional dbRunAsync
! constant, the query runs asynchronously. To determine whether an
! asynchronous query is still executing, check the value of the
! StillExecuting property on the object from which the Execute method was
! called. To terminate execution of an asynchronous Execute method call,
! use the Cancel method.

I haven't done this, but it looks as though you can do something like

dbOther.Execute strSQL, dbFailOnError OR dbRunAsync

Do While dbOther.StillExecuting
UpdateMyProgressBar ' although you don't actually know what
' 100% would look like
Loop

' finished at last
RemoveMyProgressBar



Hope that helps


Tim F

 

[Guest]

Thanks Tim, I will look at this and see if I can make it work, not sure what
it means by in an ODBC workspace. As I said my VB is VERY limited...will try
your example and see what happens.
Cheers
Sue

 

[Tim Ferguson]

Thanks Tim, I will look at this and see if I can make it work, not
sure what it means by in an ODBC workspace. As I said my VB is VERY
limited...will try your example and see what happens.
Cheers
Sue

Access works with data in Jet databases (ordinary mdb files) or makes
special arrangements to talk to any other kind (SQL Server, Oracle, etc
etc). The latter are organised in a OBDB Workspace rather than a Jet
Workspace, which is what you usually have.

Having just re-read your OP, the whole bit about asynchronous queries is
probably a bit of a red herring if you are using normal Jet databases.
Well-meant but irrelevant! If you just want to reassure your user that
something is happening, you could do something like:

strSQL = "SELECT Something INTO Somewhere FROM etc"

DoCmd.OpenForm "frmPleaseWait"
db.Execute strSQL, dbFailOnError
DoCmd.Close acForm, "frmPleaseWait"

with something helpful on the frmPleaseWait form for them to look at.


Hope that helps


Tim F

 

[Guest]

Tried editing to your example (see below) but get 'method or data member not
found'. I think this is because it needs to be done via an odbc ws so I tried
to do this using examples found but get error odbc call failed. Any ideas -
can I do this using a predefined query - please see 2nd code below thanks!

Set dbOther = OpenDatabase("mdb path")

dbOther.Execute ("Dealer Selection Extract"), dbFailOnError Or dbRunAsync

Do While dbOther.StillExecuting
Me.ProcessLabel.Visible = True
Loop

2nd code:

Dim dbOther As Database
Dim wrkSpace As Workspace
Dim conMain As Connection
Dim sngTime As Single
Dim qdf As QueryDef

Set wrkSpace = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
Set conMain = wrkSpace.OpenConnection("mdb path", dbDriverNoPrompt, False)
Set qdf = conMain.QueryDefs("Dealer Selection Extract")

qdf.Execute dbFailOnError Or dbRunAsync

Do While qdf.StillExecuting
If MsgBox("Query still running... Keep Waiting?", vbYesNo) = vbNo Then
conMain.Cancel
MsgBox "Query Cancelled by User!"
Exit Do
End If
Loop

qdf.Close
conMain.Close
wrkSpace.Close

 

[Guest]

Added a DoEvents and this seems to now work , thanks

Sue

 

[Tim Ferguson]

Added a DoEvents and this seems to now work , thanks

That would seem to be the right statement, but where did you put it..?


Tim F

 

[Guest]

I added a repaint action to the on open event of the progress form and then I
added DoEvents in the code after calling the form before running the query.