Automating Manual Access Steps

[jjlogan]

I am not getting consistent results in my tables after running a subroutine that performs about 35 Access steps (instead of doing them manually). I'm wondering if anyone has experience with this type application (basically automating manual steps)?
Background (sorry for lengthy): The Access steps consistent of Making Table queries (generally running stored queries with DoCmd.OpenQuery which put sums in a table), using DAO to rename the summed fields, running append queries using using DoCmd.RunSQL, running Update queries using ADO, and running Select queries using both stored queries and ADO (latter to get record counts for display on form). I generally get different results shown in my final report (which is based on cross-tab query of result tables) when I SetWarnings True (whereby Access asks me to respond to every Make-Table, etc. operation) versus using SetWarnings False (no Access questions - just speed through all steps). All my answers to system warning questions are Yes, so I don't see why I need to manually respond on every action query. But when I go "slow" and respond to every question, the results are better.
Thanks for any insights, Jeff

 

[jjlogan]

I found the problem. After posting above, I continued trouble shooting. Upon closer look at system warning messages, I see that when executing the Make Table queries, the system asks if I want to delete the old table and the default response is NO (I thought it was Yes). So, by running without system warnings, I was answering NO but intended Yes.
Jeff

 

[jjlogan]

I forgot to ask the related question: Does anyone know of a way to answer "Yes" to all the system warnings so that I can keep the message turned off, but insure my responses are all 'Yes'??. I will consider 1) using ADOX to see if system messages are generated, 2) checking stored Make Table queries to see if can turn off messages, and 3) look again at DoCmd.SetWarnings to see if a way to 'Yes' all questions.
Jeff

 

[traingamer]

One way to avoid that is to specifically delete the table(s) first:

DoCmd.DeleteObject acTable, strTable

where strTable is your table name, of course.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[TheComputerDude]

Are you constantly creating the same tables? If so, you may just want to delete all the records from the table. Access has a bloating issue when you create new objects and then delete them. To do this, simply use the following:
CurrentDb.Execute "Delete * from tblxxxx"

Let me know if this helps.

 

[jjlogan]

Thanks traingamer and c-dude for inputs. I am still working on the first suggestion of deleting tables before executing the make-table queries; currently still not getting the same results as leaving system warnings on and no prior DoCmd.DeleteObject, but i'm rechecking that i'm deleting all the right tables (think i am because don't get any warnings that i'm deleting a table). maybe the bloating problem is culprit? there are a couple (out of about 20) make-table queries that use the same table that had been deleted in previous steps.
jeff

 

[TheComputerDude]

I would keep the same table intact and do the delete script either right before you execute an append query (formerly your make table query) or when you are finished with the data. This will ensure that the table is clean when you put in the data. You can then do a record count and see if it is pulling the expected number of rows.

Hope this helps.

Terry

 

[jjlogan]

c-dude:
Before I get to clean data idea; i just finished stepping thru my code (54 pages in Word) and checked every Make-Table query warning to verify the table name being deleted. All of my DoCmd.DeleteObject acTable, "tblName" are commented out just before the DoCmd.OpenQuery "QryMakeTable..." statements and the delete statements have the exact tbl names noted in system warning. So, ... When I run the sub with System Warnings On both ways (Delete statements in before MT's and Delete Statements out) and respond to all the warnings I get different results in my Total Cost report. The only thing that changed was the use of DoCmd.DeleteObject ... statements and these statements are deleting the correct tbl names that the Make-Table statement following will delete.
OK - clean data: You said keep the same table intact and do the delete script either right before you execute an append query (formerly your make table query) or when you are finished with the data. Don't quite understand because the delete script will delete the table and so not available for Appending data to it.??
Jeff

 

[JerryKlmns]

Jeff

Those statements
DoCmd.DeleteObject acTable, strTable
DoCmd.RunSQL "DELETE FROM " & strTable
are totaly different.

The first destroyes an object from the datasource while the second deletes records from an object of the datasource. To make it more clear the T-SQL statements would be executed like

CurrentDB.Execute "DROP TABLE " & strTable
CurrentDB.Execute "DELETE FROM " & strTable

 

[jjlogan]

I exchanged the code
DoCmd.DeleteObject acTable.. with
CurrentDB.Execute "DROP TABLE ..."
and ran the sub with System Warnings off but still got an incorrect result. One difference in running with these table deletes before the make table query is that at least one table when created with MakeTable query only loads with 2 records instead of correct 45 records. I'm coming to the conclusion that if you're going to use Access to make tables for you, then you better follow their procedure of responding to their system warnings; otherwise use a non-Access method like ADOX or DAO and probably won't have to deal with system warnings.
By the way here is a little bit of my code; notice my attempt in lines 4 and 5 to try and drop or delete the table before running the Access make-table query so that the system warning with default response of 'No' would not have to be answered and hence could turn off all system warnings and really fly at jet speed through the 50 steps.

'STEP 29: Run Make Table query (EnvInsOHBase)
'DoCmd.DeleteObject acTable, "EnvInsOHBase"
'CurrentDb.Execute "Delete * from EnvInsOHBase"
CurrentDb.Execute "DROP TABLE EnvInsOHBase"
DoCmd.OpenQuery "QryEnvBase" 'Make table query
'Box 19
Me.Label27.Caption = " Step 29: Make table EnvInsOHBase from QryEnvBase"
Me.Check27.DefaultValue = True
Me.Check27.Visible = True
Me.Label27.Visible = True

' Update field values in EnvInsOverhead using ADO SQL statements.
Dim lngCountBefore As Long 'Count of records selected for update query, using Select query first.
Dim lngCountAfter As Long

' Initialize counter variables
lngCountBefore = 0
lngCountAfter = 0

' GRPID = 3:
' Count records effected, using Select query
Set con = New ADODB.Connection
Set rs1 = New ADODB.Recordset
con.Open CurrentProject.Connection
rs1.CursorType = adOpenStatic
rs1.LockType = adLockOptimistic
strQuery = "SELECT [EnvInsOHBase].* FROM [EnvInsOHBase] WHERE (([EnvInsOHBase].[GRPID]) = 3);"
rs1.Open strQuery, con, , , adCmdText
lngCountBefore = rs1.RecordCount
' Debug.Print "GrpID 3 record count = " & lngCountBefore
rs1.Close
con.Close
Set rs1 = Nothing
Set con = Nothing

' Update Records with GrpId = 3 to GrpId = 5
Set con = New ADODB.Connection
Set rs1 = New ADODB.Recordset 'Instantiate the recordset
con.Open CurrentProject.Connection
rs1.CursorType = adOpenDynamic
rs1.LockType = adLockOptimistic
strQuery = "UPDATE [EnvInsOHBase] SET [EnvInsOHBase].[GRPID] = 5, [EnvInsOHBase].[NCGROUP] = 'ENVIRONMENTAL INSURANCE OH Base-PO' " & _
"WHERE (([EnvInsOHBase].[GRPID]) = 3);"
rs1.Open strQuery, con, , , adCmdText
'rs1.Close 'Appears that Update query recordsets close themselves after running.
Set rs1 = Nothing
con.Close
Set con = Nothing
' Box 20
Me.Label28.Caption = CStr("Update GrpId 3 to 5: " & lngCountBefore & " records.")
lngCountBefore = 0
Me.Check28.DefaultValue = True
Me.Check28.Visible = True
Me.Label28.Visible = True

I thank everyone for helping; it has given me more insight and led me to ADO and DAO use for future exploration.
Jeff

 

[JerryKlmns]

jjlogan,

To count the records in a table use this

SELECT Count(*)
FROM yourTableName
WHERE yourWhereClause

A trusted recordcount is a client side static recordset or server side keyset recordset. Many might argue with that but I believe it could be trusted. But PHV suggestion is the previous SQL statement. No matter how you open the recordset it still holds the correct value.

Action queries (UPDATE\DELETE\INSERT) should not be executed by openning a recordset whose source property is set to such. If it is, returned recordset is always closed cause there is nothing to return. Action queries should be executed A] against an opened Connection object, B]Command object C] a database object D] QueryDef object, A+B is for ADODB while the others for DAO
In order to find how many records where affected by the most recently invoked Execute method, check the RecordsAffected variable of the first two objects or RecordsAffected property of the others two.

For a connection obect execute method, use the ExecuteOptionEnum value adExecuteNoRecords to improve performance by minimizing internal processing.

Plus set a ADODB.Connection object equal to CurrentProject.Connection, not open one against it
Set con = CurrentProject.Connection

And
strSQL= "SELECT Count(*) FROM EnvInsOHBase WHERE GRPID = 3;"
Set rst = con.Execute strSQL , , 1' adCmdText
lngCountBefore = rst.Fields(0)
rst.Close
Set rst = Nothing

Con.Execute "Delete * from EnvInsOHBase",,129 'adCmdText + adExecuteNoRecords

That' s a lot to read for you to improve performance.
Give it a try and let us know

 

[jjlogan]

Thanks Computer-Dude and Jerry. I think I'm getting the light: I was thinking I had to delete the table object before running the make-table query. New thinking: Why do that - instead keep the object in place (may have primary keys, indexes, etc.) and just delete the records and then append the new records. WoW - I will go back to my code and make changes and let you know if I get better results; if not, the problem may be elsewhere. Will also change the count method from openning a recordset to just using Select Count with ADO con.Execute.
Jeff