SQL Code Problem

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

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

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.

--
HTH
Van T. Dinh
MVP (Access)




"matt donker via AccessMonster.com" wrote
in message news:a90dbaf59eb645f6a8599d2eafd99727@AccessMonste r.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

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

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

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

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

* Add the statement

DBEngine.Idle dbRefreshCache

after the Execute statement.

--
HTH
Van T. Dinh
MVP (Access)


"matt donker via AccessMonster.com" wrote in
message news:c9fdbc6fc01e416299a2813b80afe537@AccessMonste r.com...
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

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

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?

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

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

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

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

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

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.

--
HTH
Van T. Dinh
MVP (Access)


"matt donker via AccessMonster.com" wrote in
message news:7d2ad2a902424dd7b7898da8370f1a69@AccessMonste r.com...
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?

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

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.

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

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

--
HTH
Van T. Dinh
MVP (Access)




"matt donker via AccessMonster.com" wrote in
message news:272f9d680e92420da072ab87f18982ea@AccessMonste r.com...
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.

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