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;Trusted_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;Trusted_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!
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;Trusted_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;Trusted_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!
