"DROP TABLE table" works once then fails later on. (3 Viewers)

[KACJR]

I come again to the well of knowledge...

Access 2016, Azure SQL instance.

My application maintains a local table of "AllClients" which is a subset of my AllPeople table. At app startup, the sub that drops AllClients then creates a new AllClients table works fine. Then, later on, the same sub fails with Run Time code 438 (Object doesdn't support this property or method) when attempting to drop the table.

As noted in the code below, I tried emptying out AllClients and executing a query to refill it. This also fails.

Public Sub BuildAllClients(Initial As Boolean)                                            ' Update AllClients.  This is a temporary table.
On Error GoTo ShowMeError
    Dim ClientCount As Integer
    
    SysCmdResult = SysCmd(4, "Building AllClients table. ")
    Call BriefDelay
    If IsObjectOpen("AllClients", acTable) Then DoCmd.Close acTable, "AllClients"
'   If IsTableQuery("AllClients") Then TILLDataBase.Execute "DROP TABLE AllClients", dbSeeChanges
    If IsTableQuery("AllClients") Then CurrentProject.Connection.Execute "DROP TABLE AllClients", dbSeeChanges
'   TILLDataBase.Execute "DELETE * FROM AllClients", dbSeeChanges: Call BriefDelay
    Call BriefDelay
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryBuildAllClients"
    DoCmd.SetWarnings True
'    TILLDataBase.Execute "INSERT INTO AllClients ( DisplayName, ReverseDisplayName, IndexedName, LastName, FirstName, MiddleInitial, IsClientDay, IsClientRes, IsClientTrans, IsClientVocat, IsCilentCLO, IsClientIndiv, IsClientAutism, IsClientPCA, IsClientIHBC, IsClientSpring, IsClientTRASE, IsClientSTRATTUS, IsClientCommunityConnections) " & _
'        "SELECT [tblPeople]![FirstName] & ' ' & [tblPeople]![MiddleInitial] & ' ' & [tblPeople]![LastName] AS DisplayName, [tblPeople].[LastName] & ', ' & [tblPeople].[Firstname] AS ReverseDisplayName, tblPeople.IndexedName, tblPeople.LastName, tblPeople.FirstName, tblPeople.MiddleInitial, tblPeople.IsClientDay, tblPeople.IsClientRes, tblPeople.IsClientTrans, tblPeople.IsClientVocat, tblPeople.IsCilentCLO, tblPeople.IsClientIndiv, tblPeople.IsClientAutism, tblPeople.IsClientPCA, tblPeople.IsClientIHBC, tblPeople.IsClientSpring, tblPeople.IsClientTRASE, tblPeople.IsClientSTRATTUS, tblPeople.IsClientCommunityConnections FROM tblPeople " & _
'        "WHERE tblPeople.ClientCompletelyInactive=False AND tblPeople.IsDeceased=False AND tblPeople.IsClient=True " & _
'        "ORDER BY [tblPeople].[LastName] & ', ' & [tblPeople].[Firstname];", dbSeeChanges: Call BriefDelay
    Call BriefDelay(2)
    If Initial Then
        ClientCount = DCount("DisplayName", "AllClients")
        Call ProgressMessages("Append", "   " & ClientCount & " total active clients identified.")
    End If
    Exit Sub
ShowMeError:
    Err.Source = "PublicSubroutines" & "(Line #" & Str(Err.Erl) & ")": TILLDBErrorMessage = "Error # " & Str(Err.Number) & " was generated by " & Err.Source & Chr(13) & Err.Description
    MsgBox TILLDBErrorMessage, vbOKOnly, "Error", Err.HelpFile, Err.HelpContext
End Sub

Notes: Azure SQL does not play nice with Access. Sometimes the queries work, sometimes not. I use brief delays after each SQL query to allow the command to complete (don't laugh, it works). I probably should make this a stored procedure.

Regards,
Ken

 

[tvanstiphout]

Isn't it MUCH better to delete the existing rows and append the new rows? Dropping and recreating a table seems very wasteful.

After dropping a table, the tabledefs collection has changed, and that may explain why it only works the first time. You need to run CurrentDB.Tabledefs.Refresh.

 

[Minty]

We do this all the time for local versions of lookup tables with Azure SQL.
Azure SQL plays absolutely fine with Access, it's our main data model for a lot of clients.

I uses this process and it works very reliably.

Sub sUpdateLocalTablesFromSQL(strTable As String)
    'update the local table from the SQL Server table

    Dim strLocal As String
    Dim strSQL as String
   
    strLocal = "tLocal_" & strTable
   
    If Not TableExists(strLocal) Then  ' Need to create it
        strSQL = "Select * INTO [" & strLocal & "] FROM [" & strTable & "] WHERE 1=0 "
    Else
    'delete local records
        strSQL = "DELETE * FROM " & strLocal
    End If
    CurrentDb.Execute strSQL

    'append records from SQL server table
    strSQL = "INSERT INTO [" & strLocal & "] SELECT * FROM [" & strTable & "]"
    CurrentDb.Execute strSQL
   
    'Debug.Print "End: " & Now()

End Sub

We add the tLocal_ prefix so we know which table we are using.

 

[GPGeorge]

If this process is repeated during the day, it might be wise to move the temporary, local cached tables to a throw-away accdb to avoid bloating your main accdb. There are multiple examples of that technique available.

 

[Ken Sheridan]

tblPeople.IsClientDay, tblPeople.IsClientRes, tblPeople.IsClientTrans, tblPeople.IsClientVocat, tblPeople.IsCilentCLO, tblPeople.IsClientIndiv, tblPeople.IsClientAutism, tblPeople.IsClientPCA, tblPeople.IsClientIHBC, tblPeople.IsClientSpring, tblPeople.IsClientTRASE, tblPeople.IsClientSTRATTUS, tblPeople.IsClientCommunityConnections

I'm concerned about all these, presumably Boolean, columns. They are encoding data as column headings. A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as single values at column positions in rows in tables, and in no other way.

The correct way to model this would be by means of a many-to-many relationship type between the People table and a ClientTypes table or similar. The relationship would be modelled in the usual way by means of a third table which resolves the many-to-many relationship type into two one-to-many relationship types

 

[KACJR]

Isn't it MUCH better to delete the existing rows and append the new rows? Dropping and recreating a table seems very wasteful.

After dropping a table, the tabledefs collection has changed, and that may explain why it only works the first time. You need to run CurrentDB.Tabledefs.Refresh.

I tried just as you suggested and wound up getting the same error.

 

[tvanstiphout]

On a copy of your FE, can you import the relevant Azure linked tables so they are now local tables, and try again?

 

[KACJR]

I'm concerned about all these, presumably Boolean, columns. They are encoding data as column headings. A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as single values at column positions in rows in tables, and in no other way.

The correct way to model this would be by means of a many-to-many relationship type between the People table and a ClientTypes table or similar. The relationship would be modelled in the usual way by means of a third table which resolves the many-to-many relationship type into two one-to-many relationship types

The Boolians are flags that identify which programs an individual is a member of. They are here for reporting purposes only.

 

[MarkK]

If you have a ProcessA that consumes a subset of rows from a larger table, and you have query qrySubsetA that correctly returns those rows...

It is a make-work project to...
• Drop tblSubsetA
• Create tblSubsetA = qrySubsetA
• Execute ProcessA with tblSubsetA

It would far simpler to...
• Execute ProcessA with qrySubsetA

 

[Ken Sheridan]

The Boolians are flags that identify which programs an individual is a member of. They are here for reporting purposes only.

That does not alter the fact that they violate the Information Principal. These formal principles of the database relational model exist for good reason. I'm attaching a little demo file which illustrates how to automate the recasting of such data into predefined correctly structured tables. In your case you would need to edit the ClientTypes table once populated to remove the IsClient prefixes, and where necessary amend the remaining string expressions to more appropriate values. As this table would have a limited number of rows, this would be a minor task.