Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Other Forums  MS Access  Problem Truncating Tables in Subform (Access 2007)

Author  Topic  mforman Starting Member 1 Post Posted - 2009-10-19 : 13:39:07 At the beginning of a function in a subform, I run this code to truncate a couple SQL tables: MySQL = "TRUNCATE TABLE tblNewHPNonSerialized; " & _ "TRUNCATE TABLE tblNewHPSerialized; " ExecuteSQLQueryNoRecords MySQL, 1 The Definition of "ExecuteSQLQueryNoRecords" is: Dim qdfTemp As QueryDef Dim rst1 As Object If Left(AnySQL, 6) = "SELECT" Then CurrentDb.QueryDefs("qrySQL").SQL = AnySQL CurrentDb.QueryDefs("qrySQL").Connect = "ODBC;DSN=VR_HewlettPackard;Description=Vendor Receivables ODBC connection for VR_HewlettPackard;UID=" & UserID & ";DATABASE=VR_HewlettPackard;Network=DBMSSOCN;Trus ted_Connection=Yes" Set rst = CurrentDb.OpenRecordset("qrySQL") Else Dim db, qdf Set db = CurrentDb Set qdf = db.CreateQueryDef("") qdf.ReturnsRecords = False qdf.Connect = "ODBC;DSN=VR_HewlettPackard;Description=Vendor Receivables ODBC connection for VR_HewlettPackard;UID=" & UserID & ";DATABASE=VR_HewlettPackard;Network=DBMSSOCN;Trus ted_Connection=Yes" qdf.ODBCTimeout = 0 qdf.SQL = AnySQL qdf.Execute qdf.Close Set db = Nothing Set qdf = Nothing End If The problem I'm having is that the truncating function keeps getting stuck and I have to force quit it. I've tried a number of things to fix this. The truncating code works when I put it in a separate form and run it, so I've deduced that it has something to do with being in a subform. It also seems to be related to the number of records in the tables since it truncates the first table (tblNewHPNonSerialized) fine but seems to get stuck on the second table (tblNewHPSerialized), which usually has about 20K records. I haven't been able to find any threads discussing a similar problem. Any ideas? I know I could build a work around by having it pop up a separate form to do the truncating, but I'd rather figure out the problem if possible rather than build a work around. Thanks!