SQL Code Problem

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

matt donker via AccessMonster.com

Now i have posted this same entry about 3 different ways now and got no
response begining to think that this site is useless. What i am trying to
do is use the Run Sql command from vb to run a make table query which takes
all fields from the table toolingtracker that datepart m of date = a
variable i have previously stored made as monthnum and puts the information
into a table called tooling archives or something.

Then i want use the docm.output object to output the table and then delete
the table i make. This code works for my inventory table but when i try to
the same using the tooling table i get an error message 3211 table locked
by another person or process. Nobody else is using the table and the vb
code is on the first form so nothing else is even using the table.

If someone could please help me out here it would be much appreciated this
is the third time i have posted and no one has responded so if you guys
don't know could you reccomend a better forum as i really need an answer.
Is there a better way to do this?

Here is the code:
DoCmd.SetWarnings False
Dim strOutputSQLT As String

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

DoCmd.RunSQL strOutputSQLT

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


'Delete Table
DoCmd.DeleteObject acTable, "tblToolingArchive"

CurrentDb.TableDefs.Refresh
 
It sounds to me that JET database engine is still creating the Table (with
the RunSQL) and the OutputTo statement has already tried to access the Table
being created.

Note that VBA and JET run asynchronously and the above timing problem will
happen if the code assumes that a process needs to be completed before the
next statement is executed. This is why it works in one case but doesn't in
another as per your post.

Try adding DoEvents twice between the RunSQL and OutputTo as well as between
OutputTo and DeleteObject.

The prefer method is to use the Execute method of the Database object and
then use the Idle Method to make sure that the data is actually written to
the hard-disk, i.e. not in cache before accessing the data with OutputTo.

Check Access VB Help on Execute Method of the Database Object and the Idle
Method of the DBEngine. You may need to add DAO Library to the References
of your database.
 
Okay i have tried your suggestion and made an execute statement however it
doesn't seem to work. The query makes the table then gives me an error
3010 table already exsists. But it makes it so i don't understand and when
i debug it says its a problem with the execute statment so i think since
this is the first execute statement i have done that i must be a simple
error in my code. Please help

'Setup Db
Dim db As Database
Set db = CurrentDb
db.Execute "SELECT * INTO [tblToolingArchive] FROM
[tblToolingTracker] WHERE DatePart ('m', [Date]) =" & MonthNum


DoCmd.OutputTo acOutputTable, "tblToolingArchive",
acFormatXLS, "\\tor-file-01\BusinessImprovement\AssemblySummer\mto08428\
Computer Inventory Database- Active\ToolingArchives\" & MonthName & YearNum


'Delete Table
DoCmd.DeleteObject acTable, "tblToolingArchive"

CurrentDb.TableDefs.Refresh
 
* Delete the Table [tblToolingArchive] before you run the code.

* Add the statement

DBEngine.Idle dbRefreshCache

after the Execute statement.
 
This does not work as once i try to delete it it responds with an error
saying it cannot delete the table while its open. So i wrote a close code
before trying to delete it. Then when ran it it responding with and error
3211 cannot lock table because its in use by another person or process.
Which makes no sense because this is the start up form nothing else is open
or using it. And the only process that uses this table is the process i am
trying to run.

any suggestions?
 
Another thing i don't understand is i delete the table restart access and
there is no table as soon as i run the code it gives me the error that the
table already exsists. It's as if its running the the query twice or
something. It makes no sense.... Sombody help me
 
I have also discovered something very interesting as i stated earlier i do
these with another table and it worked fine. When i moved the tooling code
to before the inventory code the tool code worked find and the inventory
code gave me an error. What is causing this? So does excess have some
problem with running an SQL code after running an SQL code???
 
Close your database. Close Access. Go to the directory you store the
database and find the file (DatabaseName).LDB. If it exists, delete this
file (note: NOT the MDB file).

Open Access, open the database, delete the Table. Use the Menu View /
Refresh to make sure the Table is deleted and then run the code.
 
There is no LDB file open where i store the database. I then open my
database the table is not there as i deleted it. I run the code and i get
the same error. If this is of any note i get the same error message twice.
When i click on either debug or cancel the exact same error message comes
up again.

I appreciat your help Van T. Dinh its nice to know someone is willing to
help.

Any other ideas?
I have to go out into the shop and do some other work but i will respond
back to any posts that are left once i try the suggestions.
 
* Have you compacted and repaired the database?

* Post you amended code.

* How did you run the code?

* Post your OS version and Access version (including SP / SR number)
 
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\Computer 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\Computer Inventory
Database- Active\InventoryArchives\" & MonthName & YearNum

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

CurrentDb.TableDefs.Refresh
 
DoCmd.Run SQL and i get two error message for it that are the same message,
just comes up twice.
 
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.
 
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\Computer 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\Computer Inventory
Database- Active\InventoryArchives\" & MonthName & YearNum

'Delete the table from the database
DoCmd.DeleteObject acTable, "tblIArch"
 
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.
 
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.
 
(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\Computer 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\Computer 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 said:
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\Computer 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\Computer Inventory
Database- Active\InventoryArchives\" & MonthName & YearNum

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Mysterious SQL statement hating tables 3
locked table error 2
Help with code 2
DSUM Syntax Error 1
Format DatePart 4
Running total in query 2
Awkward Query Idea / Help!? Percentiles. 2
querry sql problem for previous month 4

Back
Top