How Test DROP Table in Access Backend

[giovanniroi]

Threre support,
I use this code to upgrade the Access Backend data base but I have the problem to test the DROP execution of the table in the Backend before copy the new table.

I insert a time pause to be sure that DROP command are executed but this is a critical issue because I am not sure that this time is suffcient to make the DROP.

How can I Test the DROP Command execution before copy the new table?

Thank' for the support

Giovanni Roi

======================
Function Update_backend_DB()
Dim NewTableName As String
Dim OldTableName As String
Dim PauseTime As Integer
Dim Start As Date
Dim cnn As ADODB.Connection
Dim Connessione As String
Dim rstD As New ADODB.Recordset

Set RsC = New ADODB.Recordset

NewTableName = "TA03_Compagnie_SEL"
OldTableName = "TA03_Compagnie"

' make the backend table list
& " FROM MSysObjects IN 'C:\TMP\Backend_be.accdb'" _
& " WHERE (((MSysObjects.Type)=6 Or (MSysObjects.Type)=1));"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL

'verify that the OLD table are available in the backend

strSQL = " SELECT MyTabelList_CTR.*" _
& " FROM MyTabelList_CTR" _
& " WHERE (((MyTabelList_CTR.Name)='" & OldTableName & "'));"
RsC.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

If RsC.EOF = True Then
RsC.Close
GoTo L4
End If
RsC.Close

'delete OLD Table from the backend
Set cnn = New ADODB.Connection
Connessione = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\TMP\Backend_be.accdb;Persist Security Info=False"
cnn.Open Connessione

rstD.Open "DROP TABLE [" & OldTableName & "];", cnn, _
adOpenKeyset, adLockOptimistic
cnn.Close
Set cnn = Nothing
Set rstD = Nothing

PauseTime = 6 ' Imposta la durata.
Start = Timer ' Imposta l'ora di inizio.
Do While Timer < Start + PauseTime
DoEvents ' Passa il controllo ad altri processi.
Loop

'copy the new table in the backend
DoCmd.CopyObject "C:\TMP\Backend_be.accdb", OldTableName, acTable, NewTableName

'delete new table from the front end
DoCmd.DeleteObject acTable, NewTableName

L4: rsCTR.Close
Set rsCTR = Nothing
Set cnn = Nothing
Set rstD = Nothing


End Function

================

Giovanni Roi

 

[LPurvis]

Oh there are just soo many questions. ;-)

First of all the purpose.
You're copying a table from the front end application into the back end file?
Why would you need to? And why would you need to delete the entire table?
Does the structure change each time?
Deleting and Appending data, that's fairly common. Replacing tables as a matter of course, not so much.

The method.
You're encountering some latency because you're using two methods to perform this task. And the method you're using to delete the table is a bit of a hack that ADO exposes, but supports only by its flexible nature.
i.e. dropping the table should notbe done through a recordset object - but the connection itself.
cnn.Execute "DROP TABLE [" & OldTableName & "];"

However you then use an Access method (DoCmd.CopyObject) to copy the table.
That has its advantages in that you maintain the schema entirely (indexes included) but it isn't connection aware and so you could have that ADO cache holding you up.

Using a make table query wouldn't give you this - but it would allow you to execute the method from SQL and therefore be within the same connection.
However reading the table remotely from the same FE that you have open could cause problems. ADO connections to MDBs/ACCDBs are twitchy when it comes to locking of the file (i.e. determining that it is exclusively locked when attempting to connect due to anything that could be perceived as a design change).

Personally if I had to do this (and I'm still struggling to think why there would be a need) I'd probably just use DAO.

Open a connection, delete and pull in the table.
Aircode for whole procedure:

With OpenDatabase("C:\TMP\Backend_be.accdb")
    .Execute "DROP TABLE [" & OldTableName & "]"
    .Execute "SELECT * INTO [" & OldTableName & "] FROM [" & CurrentDb.Name & "]." & "[" & NewTableName & "]"
End With

Cheers.

 

[giovanniroi]

Dear Cheers,
thank's for you support. The indications that you have sne to me are very useful to solve the problem to DROP the Table in the backend.

1. WHY I USE THIS TECNIQUE: I have decided to use this technique of integral copy the table from the front-end to the back-end why my intention is to reduce the times for the interventions of updating of the DB Access structure, automating all the activities of insert, delete, modify the table structure.
2. My intention is not to write a line of code for all these interventions ( Alter table...ect) and with the Access procedure that I have realized this it is possible.
3. NEXT STEP:I think therefore to use all of your indications for DROP the Table from the Back-end and to continue to use the function DoCmd.CopyObject to copye the tabble from the Front-end to the Back-end in how much I don't find alternatives in the SQL language.

Thank's for Your Support

Giovanni Roi

 

[giovanniroi]

Re: How Test DROP Table in Access Backend -Update

Dear Cheers,
I ask You a litle question.
For This code i snecessary to add a line with . Close?

Thank's for the support

Giovanni Roi

=====Your Code===========

With OpenDatabase("C:\TMP\Backend_be.accdb")
.Execute "DROP TABLE [" & OldTableName & "]"
End With


====question=====

With OpenDatabase("C:\TMP\Backend_be.accdb")
.Execute "DROP TABLE [" & OldTableName & "]"
.Close
End With

 

[LPurvis]

Yes - for what it's worth I would recommend it in a proper implementation.
To be fair though, I'd use a dedicated database object variable if I was doing this for real.
Implicit With block objects are all well and good - but there's no getting around neat error handling.

Dim db as DAO.Database

Set db = OpenDatabase("C:\TMP\Backend_be.accdb")
db.Execute "DROP TABLE [" & OldTableName & "]"
db.Close

That way you can include checking and destroying of the db object in your error handler too.

Leigh

 

[boblarson]

Leigh

Ah, but isn't Cheers a better name for you Leigh?

 

[LPurvis]

I have no <hic> idea what you could mean.