Mysterious SQL statement hating tables

  • Thread starter Thread starter matt donker via AccessMonster.com
  • Start date Start date
M

matt donker via AccessMonster.com

Alright seriously at this moment in time i want to throw a brick threw mr
gates face. For some reason my SQL works perfectly to extract, make the
newtable output it to an excel file and delete the table made for the
inventory information. But when the second SQl is run for tooling it tells
me that it can't continue because the table is open. Now the table is not
open anywhere repeat anywhere!!! Not being used on a form not open on my
screen, nobody on the network is using it. It makes no sense, i get it to
work once and the code deletes the table then next time it says its open...
how can this be possible its been deleted???
Anyways here is the code please HELP ME


'Make an archived records if it is the first week of the month
'Make variable to insure code is only run once a day
Dim RecordThere As Integer

If DatePart("d", Date) <= 7 Then
RecordThere = RecordThere + 1
If RecordThere = 1 Then



'Declare month and year variables
Dim YearNum
Dim MonthNum As Long
Dim MonthName As String

If DatePart("m", Date) = 1 Then
YearNum = DatePart("yyyy", Date) - 1
Else
YearNum = DatePart("yyyy", Date)
End If

MonthNum = DatePart("m", Date) - 1

'Replace the month number with text
If MonthNum = 1 Then
MonthName = "January"
ElseIf MonthNum = 2 Then
MonthName = "February"
ElseIf MonthNum = 3 Then
MonthName = "March"
ElseIf MonthNum = 4 Then
MonthName = "April"
ElseIf MonthNum = 5 Then
MonthName = "May"
ElseIf MonthNum = 6 Then
MonthName = "June"
ElseIf MonthNum = 7 Then
MonthName = "July"
ElseIf MonthNum = 8 Then
MonthName = "August"
ElseIf MonthNum = 9 Then
MonthName = "September"
ElseIf MonthNum = 10 Then
MonthName = "October"
ElseIf MonthNum = 11 Then
MonthName = "November"
ElseIf MonthNum = 12 Then
MonthName = "December"
End If

'Run SQL that copys inventory records from last month to
new table
DoCmd.SetWarnings False
Dim strOutputSQLI As String

strOutputSQLI = "Select * INTO tblIArch" _
& " FROM [tblInventoryTaken] " _
& " WHERE DatePart('m', [Date Taken]) =" & MonthNum

DoCmd.RunSQL strOutputSQLI

'Output the table to an excel file
DoCmd.OutputTo acOutputTable, "tblIArch", acFormatXLS, "\\
tor-file-01\BusinessImprovement\AssemblySummer\mto08428\Computer Inventory
Database- Active\InventoryArchives\" & MonthName & YearNum

'Delete the table from the database
DoCmd.DeleteObject acTable, "tblIArch"


DoCmd.SetWarnings False
Dim strOutputSQLT As String

strOutputSQLT = "Select * INTO tblTArch" _
& " FROM [tblToolingTracker] " _
& " WHERE DatePart('m', [Date]) =" & MonthNum

DoCmd.RunSQL strOutputSQLT



'Output the table to an excel file
DoCmd.OutputTo acOutputTable, "tblTArch", acFormatXLS, "\\
tor-file-01\BusinessImprovement\AssemblySummer\mto08428\Computer Inventory
Database- Active\ToolingArchives\" & MonthName & YearNum



'Delete Table
DoCmd.DeleteObject acTable, "tblTArch"

End If
 
Hi,
You could try this statement in there after you delete
DBEngine.Idle dbRefreshCache

--
HTH
-------
Dan Artuso, MVP


matt donker via AccessMonster.com said:
Alright seriously at this moment in time i want to throw a brick threw mr
gates face. For some reason my SQL works perfectly to extract, make the
newtable output it to an excel file and delete the table made for the
inventory information. But when the second SQl is run for tooling it tells
me that it can't continue because the table is open. Now the table is not
open anywhere repeat anywhere!!! Not being used on a form not open on my
screen, nobody on the network is using it. It makes no sense, i get it to
work once and the code deletes the table then next time it says its open...
how can this be possible its been deleted???
Anyways here is the code please HELP ME


'Make an archived records if it is the first week of the month
'Make variable to insure code is only run once a day
Dim RecordThere As Integer

If DatePart("d", Date) <= 7 Then
RecordThere = RecordThere + 1
If RecordThere = 1 Then



'Declare month and year variables
Dim YearNum
Dim MonthNum As Long
Dim MonthName As String

If DatePart("m", Date) = 1 Then
YearNum = DatePart("yyyy", Date) - 1
Else
YearNum = DatePart("yyyy", Date)
End If

MonthNum = DatePart("m", Date) - 1

'Replace the month number with text
If MonthNum = 1 Then
MonthName = "January"
ElseIf MonthNum = 2 Then
MonthName = "February"
ElseIf MonthNum = 3 Then
MonthName = "March"
ElseIf MonthNum = 4 Then
MonthName = "April"
ElseIf MonthNum = 5 Then
MonthName = "May"
ElseIf MonthNum = 6 Then
MonthName = "June"
ElseIf MonthNum = 7 Then
MonthName = "July"
ElseIf MonthNum = 8 Then
MonthName = "August"
ElseIf MonthNum = 9 Then
MonthName = "September"
ElseIf MonthNum = 10 Then
MonthName = "October"
ElseIf MonthNum = 11 Then
MonthName = "November"
ElseIf MonthNum = 12 Then
MonthName = "December"
End If

'Run SQL that copys inventory records from last month to
new table
DoCmd.SetWarnings False
Dim strOutputSQLI As String

strOutputSQLI = "Select * INTO tblIArch" _
& " FROM [tblInventoryTaken] " _
& " WHERE DatePart('m', [Date Taken]) =" & MonthNum

DoCmd.RunSQL strOutputSQLI

'Output the table to an excel file
DoCmd.OutputTo acOutputTable, "tblIArch", acFormatXLS, "\\
tor-file-01\BusinessImprovement\AssemblySummer\mto08428\Computer Inventory
Database- Active\InventoryArchives\" & MonthName & YearNum

'Delete the table from the database
DoCmd.DeleteObject acTable, "tblIArch"


DoCmd.SetWarnings False
Dim strOutputSQLT As String

strOutputSQLT = "Select * INTO tblTArch" _
& " FROM [tblToolingTracker] " _
& " WHERE DatePart('m', [Date]) =" & MonthNum

DoCmd.RunSQL strOutputSQLT



'Output the table to an excel file
DoCmd.OutputTo acOutputTable, "tblTArch", acFormatXLS, "\\
tor-file-01\BusinessImprovement\AssemblySummer\mto08428\Computer Inventory
Database- Active\ToolingArchives\" & MonthName & YearNum



'Delete Table
DoCmd.DeleteObject acTable, "tblTArch"

End If
Click to expand...
 
I attempted placing this statement in about every area i could think. Does
not help me any more ideas. They are all much appreciated.
 
Back
Top