|
 |
access thread: Run Time 3211 error..Im suicidal......
Message #1 by "Tim Maher" <tim.maher@s...> on Thu, 29 Aug 2002 16:16:37 +0100
|
|
Im seting up a system which automates Access queries and exports them to
Excel. Ive got a problem where I have a sub routine running in a
loop:
Private Sub RunQueries()
DoCmd.OpenQuery "Make_emergencies", , acReadOnly 'Make-table
query
DoCmd.OpenQuery "Emergencies_Crosstab", , acReadOnly
DoCmd.Close acQuery, "Emergencies_Crosstab"
End Sub
This routine runs fine the first time it passes through the loop,
however, on the second pass I get a :
Run time 3211 error - Cannot lock "Emergencies" table ...in use ny
another person or process.
Ive tried a few things like "DoEvents" before this routine is run,
Deleting the contents of the table after the first pass etc.
Hope you have an idea on this.
Tim (getting very desperate)
This e-mail transmission is strictly confidential and intended solely
for the person or organisation to who it is addressed. It may contain
privileged and confidential information and if you are not the
intended recipient, you must not copy, distribute or take any action
in reliance on it.
If you have received this email in error, please notify us as soon as
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however,
Swansea NHS Trust accept no responsibility for infection caused by
any virus received on the recipients system.
Message #2 by "Gerald, Rand" <RGerald@u...> on Thu, 29 Aug 2002 11:36:26 -0500
|
|
Dear Tim,
Try explicitly deleting the table after the last query.
You will also need to put a DoCmd.TransferSpreadsheet into the loop to
perform the export to Excel.
You may also need to put a counter into the loop to hash into the name
of
the output spreadsheet so that subsequent loops don't overwrite the
data in
the last spreadsheet.
Good luck! Let me know how it works.
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Tim Maher [mailto:tim.maher@s...]
Sent: Thursday, August 29, 2002 10:17 AM
To: Access
Subject: [access] Run Time 3211 error..Im suicidal......
Im seting up a system which automates Access queries and exports them
to
Excel. Ive got a problem where I have a sub routine running in a
loop:
Private Sub RunQueries()
DoCmd.OpenQuery "Make_emergencies", , acReadOnly 'Make-table
query
DoCmd.OpenQuery "Emergencies_Crosstab", , acReadOnly
DoCmd.Close acQuery, "Emergencies_Crosstab"
End Sub
This routine runs fine the first time it passes through the loop,
however, on the second pass I get a :
Run time 3211 error - Cannot lock "Emergencies" table ...in use ny
another person or process.
Ive tried a few things like "DoEvents" before this routine is run,
Deleting the contents of the table after the first pass etc.
Hope you have an idea on this.
Tim (getting very desperate)
This e-mail transmission is strictly confidential and intended solely
for the person or organisation to who it is addressed. It may contain
privileged and confidential information and if you are not the
intended recipient, you must not copy, distribute or take any action
in reliance on it.
If you have received this email in error, please notify us as soon as
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however,
Swansea NHS Trust accept no responsibility for infection caused by
any virus received on the recipients system.
Message #3 by "Carnley, Dave" <dcarnley@a...> on Thu, 29 Aug 2002 12:50:45 -0500
|
|
Myabe replace the DoCmd's with
CurrentDB.QueryDefs("Make_emergencies").Execute etc... this might take
better advantage of JET locking / concurrency etc.
-----Original Message-----
From: Tim Maher [mailto:tim.maher@s...]
Sent: Thursday, August 29, 2002 10:17 AM
To: Access
Subject: [access] Run Time 3211 error..Im suicidal......
Im seting up a system which automates Access queries and exports them to
Excel. Ive got a problem where I have a sub routine running in a
loop:
Private Sub RunQueries()
DoCmd.OpenQuery "Make_emergencies", , acReadOnly 'Make-table
query
DoCmd.OpenQuery "Emergencies_Crosstab", , acReadOnly
DoCmd.Close acQuery, "Emergencies_Crosstab"
End Sub
This routine runs fine the first time it passes through the loop,
however, on the second pass I get a :
Run time 3211 error - Cannot lock "Emergencies" table ...in use ny
another person or process.
Ive tried a few things like "DoEvents" before this routine is run,
Deleting the contents of the table after the first pass etc.
Hope you have an idea on this.
Tim (getting very desperate)
This e-mail transmission is strictly confidential and intended solely
for the person or organisation to who it is addressed. It may contain
privileged and confidential information and if you are not the
intended recipient, you must not copy, distribute or take any action
in reliance on it.
If you have received this email in error, please notify us as soon as
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however,
Swansea NHS Trust accept no responsibility for infection caused by
any virus received on the recipients system.
Message #4 by "Gerald, Rand" <RGerald@u...> on Thu, 29 Aug 2002 13:34:17 -0500
|
|
Here is some code that splits a table into 26 spreadsheets depending
upon
the first letter of the fund_code:
'=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D begin code
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Public Sub LoopTest()
Dim i As Integer
Dim strLtr As String
Dim strSQL As String
Dim strOutput As String
For i =3D 65 To 90
strLtr =3D Chr(i)
strOutput =3D "tmpOutput_" & strLtr
strSQL =3D "SELECT FES_Codes.* INTO tmpTable_Loop FROM
FES_Codes WHERE
((([FES_Codes].[Fund Code]) Like '" & strLtr & "*'));"
DoCmd.RunSQL strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"tmpTable_Loop", strOutput, True
DoCmd.DeleteObject acTable, "tmpTable_Loop"
Next
End Sub
'=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D end code
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
This is an expansion on my earlier comments.
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Gerald, Rand [mailto:RGerald@u...]
Sent: Thursday, August 29, 2002 11:36 AM
To: Access
Subject: [access] RE: Run Time 3211 error..Im suicidal......
Importance: High
Dear Tim,
Try explicitly deleting the table after the last query.
You will also need to put a DoCmd.TransferSpreadsheet into the loop to
perform the export to Excel.
You may also need to put a counter into the loop to hash into the name
of
the output spreadsheet so that subsequent loops don't overwrite the
data in
the last spreadsheet.
Good luck! Let me know how it works.
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Tim Maher [mailto:tim.maher@s...]
Sent: Thursday, August 29, 2002 10:17 AM
To: Access
Subject: [access] Run Time 3211 error..Im suicidal......
Im seting up a system which automates Access queries and exports them
to
Excel. Ive got a problem where I have a sub routine running in a
loop:
Private Sub RunQueries()
DoCmd.OpenQuery "Make_emergencies", , acReadOnly 'Make-table
query
DoCmd.OpenQuery "Emergencies_Crosstab", , acReadOnly
DoCmd.Close acQuery, "Emergencies_Crosstab"
End Sub
This routine runs fine the first time it passes through the loop,
however, on the second pass I get a :
Run time 3211 error - Cannot lock "Emergencies" table ...in use ny
another person or process.
Ive tried a few things like "DoEvents" before this routine is run,
Deleting the contents of the table after the first pass etc.
Hope you have an idea on this.
Tim (getting very desperate)
This e-mail transmission is strictly confidential and intended solely
for the person or organisation to who it is addressed. It may contain
privileged and confidential information and if you are not the
intended recipient, you must not copy, distribute or take any action
in reliance on it.
If you have received this email in error, please notify us as soon as
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however,
Swansea NHS Trust accept no responsibility for infection caused by
any virus received on the recipients system.
Message #5 by "Tim Maher" <tim.maher@s...> on Fri, 30 Aug 2002 09:39:41 +0100
|
|
Hi Rand,
Many hnaks for reading my plea for help an for taking the time to write
that section of code you supplied to me!!!!!
With regards to exprtong to excel, I have created an Object of Excel in
Access:
Set xlApp =3D CreateObject("Excel.Application")
Set xlBook =3D xlApp.Workbooks.Add()
xlSheet.Range("A4").CopyFromRecordset rstTemp
etc, etc,
This part of the code is working without any problems.
Getting back to my problem of Table Locking.....
This may sound a bit stupid of me but how do I "Explicity delete" the
table. I have tried the following code, but this doesn't seem to help:
DoCmd.RunSQL "Delete * From Emergencies_Table"
Also someone had mentioned using DoEvents, but this didn't work
either.
Again, I have tried another suggestion:
CurrentDb.QueryDefs("Make_emergencies").Execute
But Im getting :
Run Time 3061 - Too Few parameters. Expected 3.
If I can get this system fixed by the end of today my weekend would be
sooooo much better. All that is hiolding me back is this "Locking"
problem AAGGGHHHH!!!
If you have any further Ideas I would be most grateful if you could
pass them onto me.
Very Very Best regards
Tim Maher
Tim Maher
Information Officer
Swansea NHS Trust
Tel 01792 703661
>>> RGerald@u... 08/29/02 05:36pm >>>
Dear Tim,
Try explicitly deleting the table after the last query.
You will also need to put a DoCmd.TransferSpreadsheet into the loop to
perform the export to Excel.
You may also need to put a counter into the loop to hash into the name
of
the output spreadsheet so that subsequent loops don't overwrite the
data in
the last spreadsheet.
Good luck! Let me know how it works.
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Tim Maher [mailto:tim.maher@s...]
Sent: Thursday, August 29, 2002 10:17 AM
To: Access
Subject: [access] Run Time 3211 error..Im suicidal......
Im seting up a system which automates Access queries and exports them
to
Excel. Ive got a problem where I have a sub routine running in a
loop:
Private Sub RunQueries()
DoCmd.OpenQuery "Make_emergencies", , acReadOnly 'Make-table
query
DoCmd.OpenQuery "Emergencies_Crosstab", , acReadOnly
DoCmd.Close acQuery, "Emergencies_Crosstab"
End Sub
This routine runs fine the first time it passes through the loop,
however, on the second pass I get a :
Run time 3211 error - Cannot lock "Emergencies" table ...in use ny
another person or process.
Ive tried a few things like "DoEvents" before this routine is run,
Deleting the contents of the table after the first pass etc.
Hope you have an idea on this.
Tim (getting very desperate)
This e-mail transmission is strictly confidential and intended solely
for the person or organisation to who it is addressed. It may contain
privileged and confidential information and if you are not the
intended recipient, you must not copy, distribute or take any action
in reliance on it.
If you have received this email in error, please notify us as soon as
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however,
Swansea NHS Trust accept no responsibility for infection caused by
any virus received on the recipients system.
This e-mail transmission is strictly confidential and intended solely
for the person or organisation to who it is addressed. It may contain
privileged and confidential information and if you are not the
intended recipient, you must not copy, distribute or take any action
in reliance on it.
If you have received this email in error, please notify us as soon as
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however,
Swansea NHS Trust accept no responsibility for infection caused by
any virus received on the recipients system.
Message #6 by "Gerald, Rand" <RGerald@u...> on Fri, 30 Aug 2002 09:52:34 -0500
|
|
Hi Tim,
Re: Explicit table deletes.
In my earlier example, the
DoCmd.DeleteObject acTable, <table name>
line explicitly deletes the temporary table.
So you could code it as:
DoCmd.DeleteObject actable, "Emergencies_Table"
You can also do it with SQL
StrSQL =3D "DROP " & strTablName & ";"
DoCmd.RunSQL strSQL
Or
DoCmd.RunSQL "DROP Emergencies_Table"
If the explicit delete generates an error message, it's probably due to
the
fact that you have linked the Access table to the Excel spreadsheet.
In
that case the Access table will have to continue to exist. This would
cause
the second loop to fail. The second iteration would try to overwrite an
open
table. For this kind of loop situation, you must do a true export and
not a
link. Take a close look at the DoCmd.TransferSpreadsheet command in
the
Access help files for specific details on the parameters for that
command.
Good luck again,
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Tim Maher [mailto:tim.maher@s...]
Sent: Friday, August 30, 2002 3:40 AM
To: Access
Subject: [access] RE: Run Time 3211 error..Im suicidal......
Hi Rand,
Many hnaks for reading my plea for help an for taking the time to write
that section of code you supplied to me!!!!!
With regards to exprtong to excel, I have created an Object of Excel in
Access:
Set xlApp =3D CreateObject("Excel.Application")
Set xlBook =3D xlApp.Workbooks.Add()
xlSheet.Range("A4").CopyFromRecordset rstTemp
etc, etc,
This part of the code is working without any problems.
Getting back to my problem of Table Locking.....
This may sound a bit stupid of me but how do I "Explicity delete" the
table. I have tried the following code, but this doesn't seem to help:
DoCmd.RunSQL "Delete * From Emergencies_Table"
Also someone had mentioned using DoEvents, but this didn't work
either.
Again, I have tried another suggestion:
CurrentDb.QueryDefs("Make_emergencies").Execute
But Im getting :
Run Time 3061 - Too Few parameters. Expected 3.
If I can get this system fixed by the end of today my weekend would be
sooooo much better. All that is hiolding me back is this "Locking"
problem AAGGGHHHH!!!
If you have any further Ideas I would be most grateful if you could
pass them onto me.
Very Very Best regards
Tim Maher
Tim Maher
Information Officer
Swansea NHS Trust
Tel 01792 703661
>>> RGerald@u... 08/29/02 05:36pm >>>
Dear Tim,
Try explicitly deleting the table after the last query.
You will also need to put a DoCmd.TransferSpreadsheet into the loop to
perform the export to Excel.
You may also need to put a counter into the loop to hash into the name
of
the output spreadsheet so that subsequent loops don't overwrite the
data in
the last spreadsheet.
Good luck! Let me know how it works.
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Tim Maher [mailto:tim.maher@s...]
Sent: Thursday, August 29, 2002 10:17 AM
To: Access
Subject: [access] Run Time 3211 error..Im suicidal......
Im seting up a system which automates Access queries and exports them
to
Excel. Ive got a problem where I have a sub routine running in a
loop:
Private Sub RunQueries()
DoCmd.OpenQuery "Make_emergencies", , acReadOnly 'Make-table
query
DoCmd.OpenQuery "Emergencies_Crosstab", , acReadOnly
DoCmd.Close acQuery, "Emergencies_Crosstab"
End Sub
This routine runs fine the first time it passes through the loop,
however, on the second pass I get a :
Run time 3211 error - Cannot lock "Emergencies" table ...in use ny
another person or process.
Ive tried a few things like "DoEvents" before this routine is run,
Deleting the contents of the table after the first pass etc.
Hope you have an idea on this.
Tim (getting very desperate)
This e-mail transmission is strictly confidential and intended solely
for the person or organisation to who it is addressed. It may contain
privileged and confidential information and if you are not the
intended recipient, you must not copy, distribute or take any action
in reliance on it.
If you have received this email in error, please notify us as soon as
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however,
Swansea NHS Trust accept no responsibility for infection caused by
any virus received on the recipients system.
This e-mail transmission is strictly confidential and intended solely
for the person or organisation to who it is addressed. It may contain
privileged and confidential information and if you are not the
intended recipient, you must not copy, distribute or take any action
in reliance on it.
If you have received this email in error, please notify us as soon as
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however,
Swansea NHS Trust accept no responsibility for infection caused by
any virus received on the recipients system.
Message #7 by "Carnley, Dave" <dcarnley@a...> on Fri, 30 Aug 2002 11:43:13 -0500
|
|
deleteing all records from a table is completely different from
dropping a
table. its like emptying a box and crushing a box...
You tried DoCmd.RunSQL "Delete * From Emergencies_Table" this just
empties
the box. To destroy the box you need DoCmd.RunSQL "DROP
Emergencies_Table"
hope this helps!
-----Original Message-----
From: Tim Maher [mailto:tim.maher@s...]
Sent: Friday, August 30, 2002 3:40 AM
To: Access
Subject: [access] RE: Run Time 3211 error..Im suicidal......
Hi Rand,
Many hnaks for reading my plea for help an for taking the time to write
that section of code you supplied to me!!!!!
With regards to exprtong to excel, I have created an Object of Excel in
Access:
Set xlApp =3D CreateObject("Excel.Application")
Set xlBook =3D xlApp.Workbooks.Add()
xlSheet.Range("A4").CopyFromRecordset rstTemp
etc, etc,
This part of the code is working without any problems.
Getting back to my problem of Table Locking.....
This may sound a bit stupid of me but how do I "Explicity delete" the
table. I have tried the following code, but this doesn't seem to help:
DoCmd.RunSQL "Delete * From Emergencies_Table"
Also someone had mentioned using DoEvents, but this didn't work
either.
Again, I have tried another suggestion:
CurrentDb.QueryDefs("Make_emergencies").Execute
But Im getting :
Run Time 3061 - Too Few parameters. Expected 3.
If I can get this system fixed by the end of today my weekend would be
sooooo much better. All that is hiolding me back is this "Locking"
problem AAGGGHHHH!!!
If you have any further Ideas I would be most grateful if you could
pass them onto me.
Very Very Best regards
Tim Maher
Tim Maher
Information Officer
Swansea NHS Trust
Tel 01792 703661
>>> RGerald@u... 08/29/02 05:36pm >>>
Dear Tim,
Try explicitly deleting the table after the last query.
You will also need to put a DoCmd.TransferSpreadsheet into the loop to
perform the export to Excel.
You may also need to put a counter into the loop to hash into the name
of
the output spreadsheet so that subsequent loops don't overwrite the
data in
the last spreadsheet.
Good luck! Let me know how it works.
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Tim Maher [mailto:tim.maher@s...]
Sent: Thursday, August 29, 2002 10:17 AM
To: Access
Subject: [access] Run Time 3211 error..Im suicidal......
Im seting up a system which automates Access queries and exports them
to
Excel. Ive got a problem where I have a sub routine running in a
loop:
Private Sub RunQueries()
DoCmd.OpenQuery "Make_emergencies", , acReadOnly 'Make-table
query
DoCmd.OpenQuery "Emergencies_Crosstab", , acReadOnly
DoCmd.Close acQuery, "Emergencies_Crosstab"
End Sub
This routine runs fine the first time it passes through the loop,
however, on the second pass I get a :
Run time 3211 error - Cannot lock "Emergencies" table ...in use ny
another person or process.
Ive tried a few things like "DoEvents" before this routine is run,
Deleting the contents of the table after the first pass etc.
Hope you have an idea on this.
Tim (getting very desperate)
This e-mail transmission is strictly confidential and intended solely
for the person or organisation to who it is addressed. It may contain
privileged and confidential information and if you are not the
intended recipient, you must not copy, distribute or take any action
in reliance on it.
If you have received this email in error, please notify us as soon as
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however,
Swansea NHS Trust accept no responsibility for infection caused by
any virus received on the recipients system.
This e-mail transmission is strictly confidential and intended solely
for the person or organisation to who it is addressed. It may contain
privileged and confidential information and if you are not the
intended recipient, you must not copy, distribute or take any action
in reliance on it.
If you have received this email in error, please notify us as soon as
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however,
Swansea NHS Trust accept no responsibility for infection caused by
any virus received on the recipients system.
|
|
|
View Cart
