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
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