EvanL
asked on
DB without LDB?
Is there any way to set up an MS Access db where the .ldb file is not created?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well, I'm using an MDB for the backend of an ASP driven database on the net. No matter what I do, I can't seem to get the code to release the connection and recordset after filling an HTML table. The entire net database is read-only, so record locking doesn't really matter. Nothing will be written to the MDB file. I was hoping to get rid of the LDB altogether. The main problem, is that after I make one call to the MDB, the LDB is created, and won't go away. So, I can't delete the MDB to replace it with a more recent version.
The .ldb file is not your problem. It won't prevent you from deleting the .mdb.
If you are still connected to the DB in a way that is causing you this trouble, you have some other problem, and you should fix that part...
Can you tell us the code, (etc.), that is "filling an HTML table?"
Brian
If you are still connected to the DB in a way that is causing you this trouble, you have some other problem, and you should fix that part...
Can you tell us the code, (etc.), that is "filling an HTML table?"
Brian
ASKER
That I do know.. The MDB file can't be deleted until the LDB has closed, from what I can tell. Do you think the ASP part of this is appropriate for this Topic Area? If so, I'll post it.. If not, I can delete this question, or award the points, and take the ASP part to the ASP forum.
It's not that the ldb closes. The ldb contains information about who is using the data base, how they are using it, etc.
You cannot delete the MDB because the machines file system has it as being open. The LDB is not doing this, (indeed it cannot!).
Something has ahold of the db, (which, of course, the ldb would reflect), and until that process, (which is not the ldb), releases it, and tells the file system to close the .MDB, you won;t be able to delete it. This is the same stroy with text files, etc., and they have no associated .LDB files...
Let's see what you got; post on, Evan...
Brian
You cannot delete the MDB because the machines file system has it as being open. The LDB is not doing this, (indeed it cannot!).
Something has ahold of the db, (which, of course, the ldb would reflect), and until that process, (which is not the ldb), releases it, and tells the file system to close the .MDB, you won;t be able to delete it. This is the same stroy with text files, etc., and they have no associated .LDB files...
Let's see what you got; post on, Evan...
Brian
ASKER
I used MS Access 2000 to set up a simple table and query. I exported the query to ASP, specifying the DSN that's set up on my IIS server.
At first, when I opened the ASP query from my web browser, the HTML page was generated perfectly. No problems. Then, I tried to delete the MDB file that the DSN points to. I was unable to delete it, as the server told me it was still in use.
I examined the ASP code generated by MS Access 2000, and realized that the recordset and connection variables were never closed and set to nothing. Hence (I believe) the reason I could not delete the MDB file as it was still "in use".
I opened the ASP query, and closed the connection and recordset, and set them equal to nothing. I was then able to delete the MDB file.
Once I tried this all again in the same manner, I was unable to get an ASP query to run. The HTML produced from the ASP was a few hundred empty rows that kept on going until the server request timed out. Now I'm unable to delete the MDB file again.
Here's the code I've been using:
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset
<TITLE>list_c3</TITLE>
</HEAD>
<BODY>
<%
Session.timeout = 2
If IsObject(Session("CACI_con
Set conn = Session("CACI_conn")
Else
Set conn = Server.CreateObject("ADODB
conn.open "CACI","",""
Set Session("CACI_conn") = conn
End If
%>
<%
If IsObject(Session("list_c3_
Set rs = Session("list_c3_rs")
Else
sql = "SELECT DataTable.TPEWNum, DataTable.ProgTitle, DataTable.LeadService FROM DataTable WHERE (((DataTable.TPEWNum) Like '*c3*')) "
Set rs = Server.CreateObject("ADODB
rs.Open sql, conn, 3, 3
If rs.eof Then
rs.AddNew
End If
Set Session("list_c3_rs") = rs
End If
%>
<TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=0><FONT FACE="Arial" COLOR=#000000><CAPTION><B>
<THEAD>
<TR>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>TPEW Number</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Program Title</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Lead Service</FONT></TH>
</TR>
</THEAD>
<TBODY>
<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
%>
<TR VALIGN=TOP>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HT
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HT
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HT
</TR>
<%
rs.MoveNext
loop%>
</TBODY>
<TFOOT></TFOOT>
</TABLE>
</BODY>
</HTML>
<%
rs.Close
Set rs = nothing
conn.Close
Set Connection = nothing
%>
At first, when I opened the ASP query from my web browser, the HTML page was generated perfectly. No problems. Then, I tried to delete the MDB file that the DSN points to. I was unable to delete it, as the server told me it was still in use.
I examined the ASP code generated by MS Access 2000, and realized that the recordset and connection variables were never closed and set to nothing. Hence (I believe) the reason I could not delete the MDB file as it was still "in use".
I opened the ASP query, and closed the connection and recordset, and set them equal to nothing. I was then able to delete the MDB file.
Once I tried this all again in the same manner, I was unable to get an ASP query to run. The HTML produced from the ASP was a few hundred empty rows that kept on going until the server request timed out. Now I'm unable to delete the MDB file again.
Here's the code I've been using:
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset
<TITLE>list_c3</TITLE>
</HEAD>
<BODY>
<%
Session.timeout = 2
If IsObject(Session("CACI_con
Set conn = Session("CACI_conn")
Else
Set conn = Server.CreateObject("ADODB
conn.open "CACI","",""
Set Session("CACI_conn") = conn
End If
%>
<%
If IsObject(Session("list_c3_
Set rs = Session("list_c3_rs")
Else
sql = "SELECT DataTable.TPEWNum, DataTable.ProgTitle, DataTable.LeadService FROM DataTable WHERE (((DataTable.TPEWNum) Like '*c3*')) "
Set rs = Server.CreateObject("ADODB
rs.Open sql, conn, 3, 3
If rs.eof Then
rs.AddNew
End If
Set Session("list_c3_rs") = rs
End If
%>
<TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=0><FONT FACE="Arial" COLOR=#000000><CAPTION><B>
<THEAD>
<TR>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>TPEW Number</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Program Title</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Lead Service</FONT></TH>
</TR>
</THEAD>
<TBODY>
<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
%>
<TR VALIGN=TOP>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HT
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HT
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HT
</TR>
<%
rs.MoveNext
loop%>
</TBODY>
<TFOOT></TFOOT>
</TABLE>
</BODY>
</HTML>
<%
rs.Close
Set rs = nothing
conn.Close
Set Connection = nothing
%>
EvanL,
You have some problems with your session variables (Kind of like global variables for a particular user of your website). Since you are closing the connection you do not want to save the connection in a session variable because the next time the page is loaded, it will try to access a closed database.
The purpose of using session variables for this is to improve performance on repetitive page access and to use the same database connection on subsequent pages without opening it again. If you just need to access the database on this one page you can get rid of the session stuff.
Keep the code that is in the else statement and remove the Session(..) stuff.
If you need more help on this let me know.
Phil
You have some problems with your session variables (Kind of like global variables for a particular user of your website). Since you are closing the connection you do not want to save the connection in a session variable because the next time the page is loaded, it will try to access a closed database.
The purpose of using session variables for this is to improve performance on repetitive page access and to use the same database connection on subsequent pages without opening it again. If you just need to access the database on this one page you can get rid of the session stuff.
Keep the code that is in the else statement and remove the Session(..) stuff.
If you need more help on this let me know.
Phil
ASKER
I'll give that a try.. Thanks Phil! Looks like I'll be giving out a lot of points to a lot of different people here..
Here is an explanation of what is happening in your comments:
....At first, when I opened the ASP query from my web browser, the HTML page was generated perfectly. No problems. Then, I tried to delete the MDB file that the DSN points to. I was unable to delete it, as the server told me it was still in use.
I examined the ASP code generated by MS Access 2000, and realized that the recordset and connection variables were never closed and set to nothing. Hence (I believe) the reason I could not delete the MDB file as it was still "in use". ...
You are right, the database is still open because the rs and conn were never closed. This is the way this page is supposed to work when access sets it up (For convenience and performance). The connection will eventually close when the users Session timesout which you will find as a setting in IIS.
....I opened the ASP query, and closed the connection and recordset, and set them equal to nothing. I was then able to delete the MDB file. ...
This closed the connection to the database, but the Session varibles still exist, and they are set to nothing now.
....Once I tried this all again in the same manner, I was unable to get an ASP query to run. The HTML produced from the ASP was a few hundred empty rows that kept on going until the server request timed out. Now I'm unable to delete the MDB file again. ...
Since the IsObject(Session("CACI_con
Hope this helps!
Phil
....At first, when I opened the ASP query from my web browser, the HTML page was generated perfectly. No problems. Then, I tried to delete the MDB file that the DSN points to. I was unable to delete it, as the server told me it was still in use.
I examined the ASP code generated by MS Access 2000, and realized that the recordset and connection variables were never closed and set to nothing. Hence (I believe) the reason I could not delete the MDB file as it was still "in use". ...
You are right, the database is still open because the rs and conn were never closed. This is the way this page is supposed to work when access sets it up (For convenience and performance). The connection will eventually close when the users Session timesout which you will find as a setting in IIS.
....I opened the ASP query, and closed the connection and recordset, and set them equal to nothing. I was then able to delete the MDB file. ...
This closed the connection to the database, but the Session varibles still exist, and they are set to nothing now.
....Once I tried this all again in the same manner, I was unable to get an ASP query to run. The HTML produced from the ASP was a few hundred empty rows that kept on going until the server request timed out. Now I'm unable to delete the MDB file again. ...
Since the IsObject(Session("CACI_con
Hope this helps!
Phil
ASKER
Phil:
The connection lines now look like:
<%
sql = "SELECT DataTable.TPEWNum, DataTable.ProgTitle, DataTable.LeadService FROM DataTable WHERE (((DataTable.TPEWNum) Like '*c3*')) "
Set rs = Server.CreateObject("ADODB
rs.Open sql, conn, 3, 3
Set Session("list_c3_rs") = rs
%>
Should I kill that last "Set Session" line?
The connection lines now look like:
<%
sql = "SELECT DataTable.TPEWNum, DataTable.ProgTitle, DataTable.LeadService FROM DataTable WHERE (((DataTable.TPEWNum) Like '*c3*')) "
Set rs = Server.CreateObject("ADODB
rs.Open sql, conn, 3, 3
Set Session("list_c3_rs") = rs
%>
Should I kill that last "Set Session" line?
ASKER
Here's the full code:
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset
<TITLE>list_c3</TITLE>
</HEAD>
<BODY>
<%
Session.timeout = 2
If IsObject(Session("CACI_con
Set conn = Session("CACI_conn")
Else
Set conn = Server.CreateObject("ADODB
conn.open "CACI","",""
Set Session("CACI_conn") = conn
End If
%>
<%
If IsObject(Session("list_c3_
Set rs = Session("list_c3_rs")
Else
sql = "SELECT DataTable.TPEWNum, DataTable.ProgTitle, DataTable.LeadService FROM DataTable WHERE (((DataTable.TPEWNum) Like '*c3*')) "
Set rs = Server.CreateObject("ADODB
rs.Open sql, conn, 3, 3
Set Session("list_c3_rs") = rs
End If
%>
<TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=0><FONT FACE="Arial" COLOR=#000000><CAPTION><B>
<THEAD>
<TR>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>TPEW Number</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Program Title</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Lead Service</FONT></TH>
</TR>
</THEAD>
<TBODY>
<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
%>
<TR VALIGN=TOP>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HT
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HT
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HT
</TR>
<%
rs.MoveNext
loop%>
</TBODY>
<TFOOT></TFOOT>
</TABLE>
</BODY>
</HTML>
<%
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset
<TITLE>list_c3</TITLE>
</HEAD>
<BODY>
<%
Session.timeout = 2
If IsObject(Session("CACI_con
Set conn = Session("CACI_conn")
Else
Set conn = Server.CreateObject("ADODB
conn.open "CACI","",""
Set Session("CACI_conn") = conn
End If
%>
<%
If IsObject(Session("list_c3_
Set rs = Session("list_c3_rs")
Else
sql = "SELECT DataTable.TPEWNum, DataTable.ProgTitle, DataTable.LeadService FROM DataTable WHERE (((DataTable.TPEWNum) Like '*c3*')) "
Set rs = Server.CreateObject("ADODB
rs.Open sql, conn, 3, 3
Set Session("list_c3_rs") = rs
End If
%>
<TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=0><FONT FACE="Arial" COLOR=#000000><CAPTION><B>
<THEAD>
<TR>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>TPEW Number</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Program Title</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Lead Service</FONT></TH>
</TR>
</THEAD>
<TBODY>
<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
%>
<TR VALIGN=TOP>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HT
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HT
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HT
</TR>
<%
rs.MoveNext
loop%>
</TBODY>
<TFOOT></TFOOT>
</TABLE>
</BODY>
</HTML>
<%
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
Just a side note-- you can actually prevent the creation of a locking file by setting the NTFS permissions on the directory that houses the database to read only and setting your database file's attribute bit to read only. It's normally used when you distribute a database on CD.
As has already been mentioned the .LDB isn't your problem.
As has already been mentioned the .LDB isn't your problem.
EvanL,
Yes, the last Set Session should be removed. (Sorry for the wait)
This is what I think it should look like:
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset
<TITLE>list_c3</TITLE>
</HEAD>
<BODY>
<%
Set conn = Server.CreateObject("ADODB
conn.open "CACI","",""
%>
<%
sql = "SELECT DataTable.TPEWNum, DataTable.ProgTitle, DataTable.LeadService FROM DataTable WHERE (((DataTable.TPEWNum) Like '*c3*')) "
Set rs = Server.CreateObject("ADODB
rs.Open sql, conn, 3, 1 'This is to make it read only
%>
<TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=0><FONT FACE="Arial" COLOR=#000000><CAPTION><B>
<THEAD>
<TR>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>TPEW Number</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Program Title</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Lead Service</FONT></TH>
</TR>
</THEAD>
<TBODY>
<%
'On Error Resume Next 'These lines shouldn't be necessary
'rs.MoveFirst 'Not really necessary and it causes errors when rs is empty (Reason for Resume Next??)
do until rs.eof 'I like until
%>
<TR VALIGN=TOP>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HT
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HT
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HT
</TR>
<%
rs.MoveNext
loop
%>
</TBODY>
<TFOOT></TFOOT>
</TABLE>
</BODY>
</HTML>
<%
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
Yes, the last Set Session should be removed. (Sorry for the wait)
This is what I think it should look like:
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset
<TITLE>list_c3</TITLE>
</HEAD>
<BODY>
<%
Set conn = Server.CreateObject("ADODB
conn.open "CACI","",""
%>
<%
sql = "SELECT DataTable.TPEWNum, DataTable.ProgTitle, DataTable.LeadService FROM DataTable WHERE (((DataTable.TPEWNum) Like '*c3*')) "
Set rs = Server.CreateObject("ADODB
rs.Open sql, conn, 3, 1 'This is to make it read only
%>
<TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=0><FONT FACE="Arial" COLOR=#000000><CAPTION><B>
<THEAD>
<TR>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>TPEW Number</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Program Title</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Lead Service</FONT></TH>
</TR>
</THEAD>
<TBODY>
<%
'On Error Resume Next 'These lines shouldn't be necessary
'rs.MoveFirst 'Not really necessary and it causes errors when rs is empty (Reason for Resume Next??)
do until rs.eof 'I like until
%>
<TR VALIGN=TOP>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HT
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HT
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HT
</TR>
<%
rs.MoveNext
loop
%>
</TBODY>
<TFOOT></TFOOT>
</TABLE>
</BODY>
</HTML>
<%
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
ASKER
abaldwin gets the points for the first correct answer.
"Thank you" points for BrianWren and Phil2Free are waiting in the MS Access common area.
Thanks for your help!
"Thank you" points for BrianWren and Phil2Free are waiting in the MS Access common area.
Thanks for your help!
I, too, ask: "Why do you ask?"