By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,946 Members | 741 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,946 IT Pros & Developers. It's quick & easy.

MS Jet Engine error: "Cannot open any more tables" -1311

P: n/a
I'm investigating an issue I have when pulling data back from MS access I'm retrieving all rows from a column in a table of 5000 rows - accessing a column of type "memo" ( can be 65353 character long) . I'm pulling the data back using ODBC. Details are:

Microsoft Access Driver 4.00.6306.00
Jet 4.0 release level that is currently installed (Msjet40.dll ) "Microsoft Jet Engine Library" 4.0.9025.0
After 291 rows are retreived I am consisitently getting:

[Microsoft][ODBC Microsoft Access Driver] Cannot open any more tables. (SQL State: 00000; SQL Code: -1311) DRV_BlobRead: errors.h (1336): ; [Microsoft][ODBC Microsoft Access Driver]

Any ideas what might be causing this error?

Thanks in advance for any feedback.

Mat.

May 26 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Whilst trying to generate a reproducible cut down test case to investigate this issue ( basically be taking a copy ofthe problem data ) I've identified I am regularly encountering the an error for specific rows. The error occurs when I update the column of "memo" data ( I'm hiding the data so that I can send a test case to support - the column contains confidential data ). Here is the error:

"Run-time error Could not update; currently locked by another session on this machine"

This is a copy of a "live" access DB, but I am the only user ( it's a local copy ). Is it possible that I could have picked up some row locked when I took my copy?

If so, how do I remove all of these?

Mat.
"Mathew Butler" <ma***********@logicacmg.com> wrote in message news:11***************@ernani.logica.co.uk...
I'm investigating an issue I have when pulling data back from MS access I'm retrieving all rows from a column in a table of 5000 rows - accessing a column of type "memo" ( can be 65353 character long) . I'm pulling the data back using ODBC. Details are:

Microsoft Access Driver 4.00.6306.00
Jet 4.0 release level that is currently installed (Msjet40.dll ) "Microsoft Jet Engine Library" 4.0.9025.0
After 291 rows are retreived I am consisitently getting:

[Microsoft][ODBC Microsoft Access Driver] Cannot open any more tables. (SQL State: 00000; SQL Code: -1311) DRV_BlobRead: errors.h (1336): ; [Microsoft][ODBC Microsoft Access Driver]

Any ideas what might be causing this error?

Thanks in advance for any feedback.

Mat.

May 26 '06 #2

P: n/a
99.44% likelihood is that your code is screwed. Why not post it all so
we can figure it out ... or in the 0.56 % case we can learn something
new.

May 26 '06 #3

P: n/a
I would love post the original code except that the "code" that initiated
the first post is an fact retreival via ODBC through a set of services
configured from an Oracle DB. I'm working with Oracle to try and figure what
the issue is - it is definately an error from teh ODBC driver though.

My current hypothesis is that when I took a copy of the DB it was in the
process of being accessed and as such the copy includes some locked records.
And the error from the first post relates to the locks. I'm still working on
this idea...

I managed to get the "Run-time error Could not update; currently locked by
another session on this machine" when updating records one at time. The code
for the one at a time processing is below, I basically have a form that
dosplays the data with a button, behind the button is the code at the end of
this post.

Some investigation actions:

1) I click the button
2) Some updates occur, but my debug highlights an error. eg:
Error encountered processing 1621.Attempting to process:
UPDATE Part1 SET Part1.[Description] = 'X' WHERE
(((Part1.Reference)='22.3.1') AND ((Part1.ReferenceSeq)=1621));
3) I check the data and no update has occured
4) I cut and paste the command into a query window and execute and the
update succeeds (!)
5) I re-click the button, but debug highlights an error eg:
Error encountered processing 1633.Attempting to process:
UPDATE Part1 SET Part1.[Description] = 'X' WHERE
(((Part1.Reference)='22.3.1') AND ((Part1.ReferenceSeq)=1633));
6) So I run the the following command in a query window (to update all
rows):
UPDATE Part1 SET Part1.[Description] = 'X'
This successfully updates the table!

I'm not sure if this relates to my original issue or whether this is a
red-herring caused by soem dodgy VB implementation.

Questions:

*Any idea why/how the locks occur? Clearly I am not an access programmer.

Ideally I would write a single query to do the work I am attempting to do
procedurally. I want a query to update the description column which may
contain arbitrary text, say "This is some arbitrary text" and convert this
to "XXXXXXXXXXXXXXXXXXXXXXXXX". Does Access have any standard functions to
do this?

Hopefully one of us learns something new :o)

Thanks again in advance.

Mat.

Private Sub Command0_Click()

Dim sql_string As String 'declares string variable
Dim last_rec As Integer 'declares last record variable
Dim current_desc As String
Dim currReference As String
Dim currReferenceSeq As String
Dim length_of_string As Integer
Dim n As Integer
Dim m As Integer

On Error GoTo ShowErrorTrap
DoCmd.SetWarnings (False) 'turns off automatic access table change alerts
DoCmd.GoToRecord , , acLast
last_rec = Me.ReferenceSeq 'find the id of the last record

DoCmd.GoToRecord , , acFirst ' goes to first record

For m = 1 To Me.Recordset.RecordCount
length_of_string = Len(Me.Description)
'If length_of_string > 950 Then length_of_string = 950

Debug.Print length_of_string

currReference = Me.Reference
currReferenceSeq = Me.ReferenceSeq
current_desc = "X"
For n = 0 To length_of_string - 2
' current_desc = current_desc & "X"
Next n

'generates sql_query
sql_string = "UPDATE Part1 SET Part1.[Description] = '" & current_desc &
"' WHERE (((Part1.Reference)='" & currReference & "') AND
((Part1.ReferenceSeq)=" & currReferenceSeq & "));"

Debug.Print sql_string
DoCmd.RunSQL (sql_string) 'execute query

DoCmd.GoToRecord , , acNext 'goto next record

Next m
Me.Requery

DoCmd.SetWarnings (True) 'turns back on off automatic access table change
alerts

ShowErrorTrap:

Debug.Print "Error encountered processing " & currReferenceSeq &
".Attempting to process: "
Debug.Print sql_string

End Sub


"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...
99.44% likelihood is that your code is screwed. Why not post it all so
we can figure it out ... or in the 0.56 % case we can learn something
new.

May 26 '06 #4

P: n/a
Ouch, I am embarrassed. As I use neither ODBC nor Oracle I will pass
and hope that someone who does use them will step forward here.

May 26 '06 #5

P: n/a
"Mathew Butler" <ma***********@logicacmg.com> wrote in
news:11***************@ernani.logica.co.uk:
Whilst trying to generate a reproducible cut down test case to
investigate this issue ( basically be taking a copy ofthe problem
data ) I've identified I am regularly encountering the an error
for specific rows. The error occurs when I update the column of
"memo" data ( I'm hiding the data so that I can send a test case
to support - the column contains confidential data ). Here is the
error:

"Run-time error Could not update; currently locked by another
session on this machine"

This is a copy of a "live" access DB, but I am the only user (
it's a local copy ). Is it possible that I could have picked up
some row locked when I took my copy?


That can indicate corruption of the memo pointers. Make a backup
copy of the MDB and then compact the original. Then browse the data
table to see if any of the memo fields have #error in them. If so,
you'll have to copy all the other data to a new record and delete
the record (the memo data has been lost).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 26 '06 #6

P: n/a
"Mathew Butler" <ma***********@logicacmg.com> wrote in
news:11***************@ernani.logica.co.uk:
My current hypothesis is that when I took a copy of the DB it was
in the process of being accessed and as such the copy includes
some locked records.


That isn't really possible, as the locking information is stored in
the LDB file for the MDB, and if you didn't take it, too, there can
be no locking in place.

But the fact that you may have copied the file while it was in use
does lend support to my suggestion in my other reply that you have
corruption of the memo field pointers.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 26 '06 #7

P: n/a
Interesting. I will investigate...

Thanks.

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn*********************************@127.0.0.1 ...
"Mathew Butler" <ma***********@logicacmg.com> wrote in
news:11***************@ernani.logica.co.uk:
Whilst trying to generate a reproducible cut down test case to
investigate this issue ( basically be taking a copy ofthe problem
data ) I've identified I am regularly encountering the an error
for specific rows. The error occurs when I update the column of
"memo" data ( I'm hiding the data so that I can send a test case
to support - the column contains confidential data ). Here is the
error:

"Run-time error Could not update; currently locked by another
session on this machine"

This is a copy of a "live" access DB, but I am the only user (
it's a local copy ). Is it possible that I could have picked up
some row locked when I took my copy?


That can indicate corruption of the memo pointers. Make a backup
copy of the MDB and then compact the original. Then browse the data
table to see if any of the memo fields have #error in them. If so,
you'll have to copy all the other data to a new record and delete
the record (the memo data has been lost).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

May 30 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.