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
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
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.
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
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.
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 .Connectio n")
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
For example:
Set objConn = Server.CreateObject("ADODB
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
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(DatabaseVirtu alFilename , Username, Password, byref Connection)
'Initialize
OpenDatabase = False
Dim DatabaseFilename
DatabaseFilename = Server.MapPath(DatabaseVir tualFilena me)
Dim ConnectionString
ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" &_
"DBQ=" & DatabaseFilename & ";DefaultDir=;" &_
"UID=" & Username & ";" &_
"PWD=" & Password & ";"
Set Connection = Server.CreateObject("ADODB .Connectio n")
Connection.ConnectionTimeo ut = 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?
##########################
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(DatabaseVirtu
'Initialize
OpenDatabase = False
Dim DatabaseFilename
DatabaseFilename = Server.MapPath(DatabaseVir
Dim ConnectionString
ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" &_
"DBQ=" & DatabaseFilename & ";DefaultDir=;" &_
"UID=" & Username & ";" &_
"PWD=" & Password & ";"
Set Connection = Server.CreateObject("ADODB
Connection.ConnectionTimeo
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
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
DisplayTable Connection, databasetable
End if
Connection.Close
set Connection = nothing
CheckError
End If
##########################
Any idea?
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
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
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?
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.
ASKER
No, my mdb file is not corrupted.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
Netminder
Community Support Moderator
Experts Exchange