Solved

DB without LDB?

Posted on 2000-03-01
15
404 Views
Last Modified: 2012-05-04
Is there any way to set up an MS Access db where the .ldb file is not created?  
0
Comment
Question by:EvanL
  • 7
  • 3
  • 3
  • +2
15 Comments
 
LVL 4

Accepted Solution

by:
abaldwin earned 15 total points
ID: 2574577
I don't believe so.  This is the locking file that access uses to track changes and locks that are placed on records and other objects.  

Why are you needing to do this?  Are you trying to run a database from a CD?

Andy
0
 
LVL 9

Expert Comment

by:BrianWren
ID: 2574605
Nope.  It is essential to the operation of the DB.

I, too, ask:  "Why do you ask?"
0
 
LVL 1

Author Comment

by:EvanL
ID: 2574735
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.
0
 
LVL 9

Expert Comment

by:BrianWren
ID: 2574750
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
0
 
LVL 1

Author Comment

by:EvanL
ID: 2574774
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.
0
 
LVL 9

Expert Comment

by:BrianWren
ID: 2574785
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
0
 
LVL 1

Author Comment

by:EvanL
ID: 2574859
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=windows-1252">
<TITLE>list_c3</TITLE>
</HEAD>
<BODY>
<%
Session.timeout = 2
If IsObject(Session("CACI_conn")) Then
    Set conn = Session("CACI_conn")
Else
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "CACI","",""
    Set Session("CACI_conn") = conn
End If
%>
<%
If IsObject(Session("list_c3_rs")) Then
    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.Recordset")
    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>list_c3</B></CAPTION></FONT>

<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.HTMLEncode(rs.Fields("TPEWNum").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("ProgTitle").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("LeadService").Value)%><BR></FONT></TD>

</TR>
<%
rs.MoveNext
loop%>
</TBODY>
<TFOOT></TFOOT>
</TABLE>
</BODY>
</HTML>
<%
rs.Close            
Set rs = nothing
conn.Close          
Set Connection = nothing
%>


0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 2

Expert Comment

by:phil2free
ID: 2574918
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
0
 
LVL 1

Author Comment

by:EvanL
ID: 2574927
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..
0
 
LVL 2

Expert Comment

by:phil2free
ID: 2574936
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_conn")) will be true, the script uses it.  Now the page is trying to work with a closed database which caused it to get confused.

Hope this helps!

Phil
0
 
LVL 1

Author Comment

by:EvanL
ID: 2575019
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.Recordset")
    rs.Open sql, conn, 3, 3
    Set Session("list_c3_rs") = rs
%>

Should I kill that last "Set Session" line?
0
 
LVL 1

Author Comment

by:EvanL
ID: 2575029
Here's the full code:

<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252">
<TITLE>list_c3</TITLE>
</HEAD>
<BODY>
<%
Session.timeout = 2
If IsObject(Session("CACI_conn")) Then
    Set conn = Session("CACI_conn")
Else
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "CACI","",""
    Set Session("CACI_conn") = conn
End If
%>
<%
If IsObject(Session("list_c3_rs")) Then
    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.Recordset")
    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>list_c3</B></CAPTION></FONT>

<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.HTMLEncode(rs.Fields("TPEWNum").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("ProgTitle").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("LeadService").Value)%><BR></FONT></TD>

</TR>
<%
rs.MoveNext
loop%>
</TBODY>
<TFOOT></TFOOT>
</TABLE>
</BODY>
</HTML>
<%
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 2575061
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.
0
 
LVL 2

Expert Comment

by:phil2free
ID: 2577121
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=windows-1252">
<TITLE>list_c3</TITLE>
</HEAD>
<BODY>
<%

      Set conn = Server.CreateObject("ADODB.Connection")
      conn.open "CACI","",""

%>
<%

      sql = "SELECT DataTable.TPEWNum, DataTable.ProgTitle, DataTable.LeadService  FROM DataTable  WHERE (((DataTable.TPEWNum) Like '*c3*'))   "
      Set rs = Server.CreateObject("ADODB.Recordset")
      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>list_c3</B></CAPTION></FONT>

<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.HTMLEncode(rs.Fields("TPEWNum").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("ProgTitle").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("LeadService").Value)%><BR></FONT></TD>

</TR>
<%
      rs.MoveNext
      loop
%>
</TBODY>
<TFOOT></TFOOT>
</TABLE>
</BODY>
</HTML>
<%
      rs.Close
      Set rs = Nothing
      conn.Close
      Set conn = Nothing
%>

0
 
LVL 1

Author Comment

by:EvanL
ID: 2587650
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!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now