Link to home
Start Free TrialLog in
Avatar of elito
elito

asked on

Problems with Access

Hi,

I have an asp page that displays the contents of an Access table. If I don´t have the table opened with Access,  everything is fine. However, if it´s opened with Access, when I try to open the table I get Error Number 3704, i.e. the operation is not allowed if the object is closed (english translation of the spanish error message).


Can anybody explain me why this happens and how to solve it?

Thanks,
elito
Avatar of VBKevin
VBKevin

Unfortunetly Access puts a lock on the table whenever you try access the table from the outside world. You will see this in a vb application that tries to look at a table (Access) when the table is opened by the Access environment.  SQL Server though behaves diffrently.  I think it is just at the table level. You can have the Access environment and even other tables open but not the table.
Avatar of elito

ASKER

VBKevin,
thanks for your help. The main problem is that sometimes, even after I close Access I still get this error. However, some unpredictable time later, it starts to work correctly.

This looks very weird to me. I think I have all the permissions properly set.
I really can´t figure out how to fix this :(

Thanks
Please post the code and point out the error line.
Normally, this error message when a connection/recordset is not Open()ed, iow, its State is 0.
Be sure to close your recordsets and connections to the Access DB in your ASP code.  Access can only handle so many connections probably and after a while it is automatically killing the old ones and freeing up resources.

For example:

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open (Connection String Here)
Set objRec = ojbConn.Execute(SQL Here)

use the recordset info here.....

when done with computations close them.

objRec.Close
objConn.Close
Set objRec = nothing
Set objConn = nothing
Avatar of elito

ASKER

This is the code I'm using. I believe I´m closing all the conections correctly.

########################################


On Error Resume Next

' constants
const adOpenForwardOnly = 0 '
const adOpenKeyset      = 1 '

const adOpenDynamic = 2
const adOpenStatic  = 3
const adLockOptimistic = 3

Function CheckError()
     if Err.number <> 0 then
          Response.Write "<p><FONT color=red><I>A run-time error occurred.<BR>Error Number: " &_
                           Err.number & "<BR>Error Description: " & Err.description & "</Font></I>"
          CheckError = True
     Else
          CheckError = False
     End If
end Function

Function OpenDatabase(DatabaseVirtualFilename, Username, Password, byref Connection)

     'Initialize
     OpenDatabase = False
     
     Dim DatabaseFilename
     DatabaseFilename = Server.MapPath(DatabaseVirtualFilename)

     Dim ConnectionString
     ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" &_
                        "DBQ=" & DatabaseFilename & ";DefaultDir=;" &_
                        "UID=" & Username & ";" &_
                        "PWD=" & Password & ";"

     Set Connection = Server.CreateObject("ADODB.Connection")
     Connection.ConnectionTimeout = 30
     Connection.CommandTimeout = 80
     Connection.Open ConnectionString
         
     OpenDatabase = True
         
end Function

Sub DisplayTable(Connection, TableName)
         
     Response.Write "<h2>Contents of Table: " & TableName & "</h2>" & vbCRLF
     
     Dim SQL
     SQL = "SELECT * FROM " & TableName

     ' Create a RecordSet
     Dim rs
     set rs = Server.CreateObject("ADODB.RecordSet")
     rs.Open SQL, Connection, adOpenForwardOnly, adLockOptimistic
         
     if rs.EOF or rs.BOF Then
          Response.Write "<p align=center><i> -- No records --</i>" & vbCRLF
          rs.Close
          set rs = nothing
          Exit sub
     end if
     
     ' Init table
     Response.Write "<table width='100%' border=1>" & vbCRLF

     ' write out "field headings"
     Response.Write "<tr>"
     Dim item
     For each item in rs.Fields
          Response.Write "<td valign=top><b>" & item.Name & "</b></td>"
     next
     Response.Write "</tr>" & vbCRLF
               
     ' write out records
     while not rs.EOF
         
          Response.Write "<tr>"
          For each item in rs.Fields
               Response.Write "<td valign=top>" & item.Value & "</td>"
          next
          Response.Write "</tr>" & vbCRLF
         
          rs.MoveNext
     wend
     
     ' End with the table
     Response.Write "</table>" & vbCRLF

     'close recordset
     rs.Close
     set rs = nothing

End Sub


'=================================================
'MAIN!!!!!!
'=================================================

Dim databasefile, databasetable
databasefile = "mydatabase.mdb"
databasetable = "mytable"


'Abrir base de datos
if (databasefile <> "" and databasetable <> "") then

     Dim Connection
     if (OpenDatabase(databasefile, "", "", Connection)) Then
          DisplayTable Connection, databasetable
     End if
     
     Connection.Close
     set Connection = nothing
     
     CheckError
End If

########################################


Any idea?
Avatar of elito

ASKER

In the main code, the comment in spanish 'Abrir base de datos' means 'Open data base'

As you can see, after the connection is opened and the table is displayed, it's closed and assigned to nothing.

Also, the recordset is opened and closed inside the function DisplayTable. As far as I know, the procedure I'm using is correct, isn't it?

cheers
Avatar of elito

ASKER

I believe I found out the problem. Access creates a file with extension ldb that locks the table. Normally, Access automatically deletes this file when it is closed. However, there are cases where this  takes some time so whilst this file exists, the table remains locked.
I´m afraid the only solution is to port the data base to sql server or to Oracle.

Any further comment on this?
You should still be able to use Access with great success.  It isn't as good as SQL server in my opinion but your problem shouldn't be happening....maybe you could create a new MDB and import your data...just incase the old mdb is corrupt or something.
Avatar of elito

ASKER

No, my mdb file is not corrupted.
ASKER CERTIFIED SOLUTION
Avatar of robbert
robbert

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
elito,

These questions are still open and our records show you logged in recently. Please resolve them appropriately as soon as possible. Continued disregard of your open questions will result in the force/acceptance of a comment as an answer; other actions affecting your account may also be taken. I will revisit these questions in approximately seven (7) days. Please note that the recommended minimum for an "Easy" question is 50 points.

https://www.experts-exchange.com/jsp/qShow.jsp?ta=cplusprog&qid=20143225
https://www.experts-exchange.com/jsp/qShow.jsp?ta=mfc&qid=20256254
https://www.experts-exchange.com/jsp/qShow.jsp?ta=mfc&qid=20079951
https://www.experts-exchange.com/jsp/qShow.jsp?ta=mfc&qid=20013179
https://www.experts-exchange.com/jsp/qShow.jsp?ta=asp&qid=20081494
https://www.experts-exchange.com/jsp/qShow.jsp?ta=asp&qid=20078523
https://www.experts-exchange.com/jsp/qShow.jsp?ta=xml&qid=20240193

EXPERTS: Please leave your thoughts on this question here.

Thanks,

Netminder
Community Support Moderator
Experts Exchange
Force/accepted by

Netminder
Community Support Moderator
Experts Exchange