SQL Code Problem

I have tried compacting and repairing database and this does not help at
all. The following in my ammended code. I run the code at the startup
screen once an employee card is swiped the number appears in the textbox
and then this code is part of the on update event. Now that i have
switched and moved the tooling part above the inventory part the inventory
query is now the problem, giving me the same error about the table being
locked. My OS is Windows 2000 service pack 4, I am running Microsoft
Access 2000 9.0.6926 SP-3. Not quite sure what the SR number is but thats
all the info i could find.


'Run Make Table Query
Dim db As Database
Set db = CurrentDb
db.Execute "SELECT * INTO [tblToolA] FROM [tblToolingTracker]
WHERE DatePart ('m', [Date]) =" & MonthNum
DBEngine.Idle dbRefreshCache

'Export table to an excel file
DoCmd.OutputTo acOutputTable, "tblToolA", acFormatXLS, "\\
tor-file-01\BusinessImprovement\AssemblySummer\mto08428\Com puter Inventory
Database- Active\ToolingArchives\" & MonthName & YearNum


'Delete Table
DoCmd.DeleteObject acTable, "tblToolA"

CurrentDb.TableDefs.Refresh

'Run SQL that copys inventory records from last month to
new table
DBEngine.Idle dbRefreshCache

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\Com puter Inventory
Database- Active\InventoryArchives\" & MonthName & YearNum

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

CurrentDb.TableDefs.Refresh

--
Message posted via http://www.accessmonster.com

It looks like you OS / Access are patched OK.

Which line of the code errored out?

--
HTH
Van T. Dinh
MVP (Access)



"matt donker via AccessMonster.com" wrote in
message news:d9698240d417419eb0a61163c6125d70@AccessMonste r.com...
I have tried compacting and repairing database and this does not help at
all. The following in my ammended code. I run the code at the startup
screen once an employee card is swiped the number appears in the textbox
and then this code is part of the on update event. Now that i have
switched and moved the tooling part above the inventory part the inventory
query is now the problem, giving me the same error about the table being
locked. My OS is Windows 2000 service pack 4, I am running Microsoft
Access 2000 9.0.6926 SP-3. Not quite sure what the SR number is but thats
all the info i could find.


'Run Make Table Query
Dim db As Database
Set db = CurrentDb
db.Execute "SELECT * INTO [tblToolA] FROM
[tblToolingTracker]
WHERE DatePart ('m', [Date]) =" & MonthNum
DBEngine.Idle dbRefreshCache

'Export table to an excel file
DoCmd.OutputTo acOutputTable, "tblToolA", acFormatXLS, "\\
tor-file-01\BusinessImprovement\AssemblySummer\mto08428\Com puter Inventory
Database- Active\ToolingArchives\" & MonthName & YearNum


'Delete Table
DoCmd.DeleteObject acTable, "tblToolA"

CurrentDb.TableDefs.Refresh

'Run SQL that copys inventory records from last month to
new table
DBEngine.Idle dbRefreshCache

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\Com puter Inventory
Database- Active\InventoryArchives\" & MonthName & YearNum

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

CurrentDb.TableDefs.Refresh

--
Message posted via http://www.accessmonster.com

DoCmd.Run SQL and i get two error message for it that are the same message,
just comes up twice.

--
Message posted via http://www.accessmonster.com

Not sure why you get the same error message twice but did you make sure that
both Tables [tblToolA] and [tblIArch] have been deleted before running the
code?

Try changing the remaining RunSQL to Execute also. If you get error on the
RunSQL, that means that the amended part of the code (using Execute) is OK,
perhaps?

Try also adding:

DoEvents

after the Idle statement for both Execute statement.

--
HTH
Van T. Dinh
MVP (Access)


"matt donker via AccessMonster.com" wrote in
message news:14afc0b0fb2d41c48621a874de43c2ab@AccessMonste r.com...
DoCmd.Run SQL and i get two error message for it that are the same
message,
just comes up twice.

--
Message posted via http://www.accessmonster.com

I have as you can see made both into execute statements, changed the
position of the idle statements and i still get the same error. 3010 table
already exsists although it doesn't because i make sure everytime that the
table is delted and gone and restart access just to make double sure. The
statement that makes the table tbliarch is the one that recieves the error
any more help/tips would be great as this is driving me insane.

'Run Make Table Query
Dim db As Database
Set db = CurrentDb
db.Execute "SELECT * INTO [tblToolA] FROM [tblToolingTracker]
WHERE DatePart ('m', [Date]) =" & MonthNum
DBEngine.Idle dbRefreshCache
CurrentDb.TableDefs.Refresh
'Export table to an excel file
DoCmd.OutputTo acOutputTable, "tblToolA", acFormatXLS, "\\
tor-file-01\BusinessImprovement\AssemblySummer\mto08428\Com puter Inventory
Database- Active\ToolingArchives\" & MonthName & YearNum


'Delete Table
DoCmd.DeleteObject acTable, "tblToolA"



'Run SQL that copys inventory records from last month to
new table
Dim dbI As Database
Set dbI = CurrentDb
dbI.Execute "SELECT * INTO [tblIArch] FROM
[tblInventoryTaken] WHERE DatePart('m', [Date Taken]) =" & MonthNum
DBEngine.Idle dbRefreshCache
CurrentDb.TableDefs.Refresh
'Output the table to an excel file
DoCmd.OutputTo acOutputTable, "tblIArch", acFormatXLS, "\\
tor-file-01\BusinessImprovement\AssemblySummer\mto08428\Com puter Inventory
Database- Active\InventoryArchives\" & MonthName & YearNum

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

--
Message posted via http://www.accessmonster.com

I moved the enitire code to run off a command button. So once the user
swipes there card and clicks continue instead of after update event and it
all works no problem. I guess it just couldn't work off the after update
event. Makes no sense to me but whatever i got it working so i am happy.
Thanks for all the help guys.

--
Message posted via http://www.accessmonster.com

Just to confirm: Did you delete *both* Tables [tblToolA] and [tblIArch]
before running the code?

You wrote in your post that you deleted the Table (note: singular noun) but
you need to delete *two* Tables before running the code.

--
HTH
Van T. Dinh
MVP (Access)


"matt donker via AccessMonster.com" wrote in
message news:c0b6f89066094c4c9f287417bbbdb2cd@AccessMonste r.com...
I have as you can see made both into execute statements, changed the
position of the idle statements and i still get the same error. 3010
table
already exsists although it doesn't because i make sure everytime that the
table is delted and gone and restart access just to make double sure. The
statement that makes the table tbliarch is the one that recieves the error
any more help/tips would be great as this is driving me insane.

'Run Make Table Query
Dim db As Database
Set db = CurrentDb
db.Execute "SELECT * INTO [tblToolA] FROM
[tblToolingTracker]
WHERE DatePart ('m', [Date]) =" & MonthNum
DBEngine.Idle dbRefreshCache
CurrentDb.TableDefs.Refresh
'Export table to an excel file
DoCmd.OutputTo acOutputTable, "tblToolA", acFormatXLS, "\\
tor-file-01\BusinessImprovement\AssemblySummer\mto08428\Com puter Inventory
Database- Active\ToolingArchives\" & MonthName & YearNum


'Delete Table
DoCmd.DeleteObject acTable, "tblToolA"



'Run SQL that copys inventory records from last month to
new table
Dim dbI As Database
Set dbI = CurrentDb
dbI.Execute "SELECT * INTO [tblIArch] FROM
[tblInventoryTaken] WHERE DatePart('m', [Date Taken]) =" & MonthNum
DBEngine.Idle dbRefreshCache
CurrentDb.TableDefs.Refresh
'Output the table to an excel file
DoCmd.OutputTo acOutputTable, "tblIArch", acFormatXLS,
"\\
tor-file-01\BusinessImprovement\AssemblySummer\mto08428\Com puter Inventory
Database- Active\InventoryArchives\" & MonthName & YearNum

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

--
Message posted via http://www.accessmonster.com

(sorry, accidentally hit the Send key previously)

Just to confirm: Did you delete *both* Tables [tblToolA] and [tblIArch]
before running the code?

You wrote in your post that you deleted the Table (note: singular noun) but
you need to delete *two* Tables before running the code.

Try this bit of *dirty* code:

********
On Error Resume Next
DoCmd.DeleteObject acTable, "tblToolA"

'Run Make Table Query
Dim db As Database
Set db = CurrentDb

db.Execute "SELECT * INTO [tblToolA] FROM [tblToolingTracker]
WHERE DatePart ('m', [Date]) =" & MonthNum
DBEngine.Idle dbRefreshCache
CurrentDb.TableDefs.Refresh

'Export table to an excel file
DoCmd.OutputTo acOutputTable, "tblToolA", acFormatXLS, "\\
tor-file-01\BusinessImprovement\AssemblySummer\mto08428\Com puter Inventory
Database- Active\ToolingArchives\" & MonthName & YearNum


'Delete Tables
DoCmd.DeleteObject acTable, "tblToolA"
DoCmd.DeleteObject acTable, "tblIArch"
CurrentDb.TableDefs.Refresh

'Run SQL that copys inventory records from last month to new table
'Dim dbI As Database (Don't need this)
'Set dbI = CurrentDb (Don't need this)
db.Execute "SELECT * INTO [tblIArch] FROM
[tblInventoryTaken] WHERE DatePart('m', [Date Taken]) =" & MonthNum
DBEngine.Idle dbRefreshCache
CurrentDb.TableDefs.Refresh

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

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

Any similar error when you run the above code?

--
HTH
Van T. Dinh
MVP (Access)


"matt donker via AccessMonster.com" wrote in
message news:c0b6f89066094c4c9f287417bbbdb2cd@AccessMonste r.com...
I have as you can see made both into execute statements, changed the
position of the idle statements and i still get the same error. 3010
table
already exsists although it doesn't because i make sure everytime that the
table is delted and gone and restart access just to make double sure. The
statement that makes the table tbliarch is the one that recieves the error
any more help/tips would be great as this is driving me insane.

'Run Make Table Query
Dim db As Database
Set db = CurrentDb
db.Execute "SELECT * INTO [tblToolA] FROM
[tblToolingTracker]
WHERE DatePart ('m', [Date]) =" & MonthNum
DBEngine.Idle dbRefreshCache
CurrentDb.TableDefs.Refresh
'Export table to an excel file
DoCmd.OutputTo acOutputTable, "tblToolA", acFormatXLS, "\\
tor-file-01\BusinessImprovement\AssemblySummer\mto08428\Com puter Inventory
Database- Active\ToolingArchives\" & MonthName & YearNum


'Delete Table
DoCmd.DeleteObject acTable, "tblToolA"



'Run SQL that copys inventory records from last month to
new table
Dim dbI As Database
Set dbI = CurrentDb
dbI.Execute "SELECT * INTO [tblIArch] FROM
[tblInventoryTaken] WHERE DatePart('m', [Date Taken]) =" & MonthNum
DBEngine.Idle dbRefreshCache
CurrentDb.TableDefs.Refresh
'Output the table to an excel file
DoCmd.OutputTo acOutputTable, "tblIArch", acFormatXLS,
"\\
tor-file-01\BusinessImprovement\AssemblySummer\mto08428\Com puter Inventory
Database- Active\InventoryArchives\" & MonthName & YearNum

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

--
Message posted via http://www.accessmonster.com