Access Database Question

	Home Register Search Help Login

Sponsors

Hosting from $3.99 per month!

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions. dd

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums

Recent Posts

Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

Access Database Question

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> Web Development >> ASP and Database >> Access Database Question

Page: [1]


eschulze Posts: 79 Joined: 1/5/2004 Status: offline	Access Database Question - 1/20/2005 17:21:43 We are using an access database as our content manager. The database contains only 730 records (stories) at this time. It seems to be unable to handle the load though. I am getting timed out when trying to list the records using a DRW and interestingly enough I am also showing the database in my FrontPage folder view as a .ldb (Locked database). We have had no problems in the past with the datbase and it holds all of our site's content. Can anyone give me any advice as to what is going on with it or how to fix it. Also, I have never used SQL database, but our host offers MySQL Disk Space and Microsoft SQL Disk Space. Is it easy to use and learn?


eschulze Posts: 79 Joined: 1/5/2004 Status: offline	RE: Access Database Question - 1/20/2005 17:23:11 As a follow-up ... could I create a second table in the database for the older stories that would alleviate some of the problem? (in reply to eschulze)


BeTheBall Posts: 6385 Joined: 6/21/2002 From: West Point Utah USA Status: offline	RE: Access Database Question - 1/20/2005 18:01:12 The issues you mention seem more related to concurrent users than db size. Access is quite limited in the number of concurrent calls it can handle. If you expect a fair amount of traffic, I would certainly recommend going with SQL or mySQL. _____________________________ Duane Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs. (in reply to eschulze)


eschulze Posts: 79 Joined: 1/5/2004 Status: offline	RE: Access Database Question - 1/21/2005 9:03:02 Is there any easy way to migrate my information over to SQL? If so, can someone please advise? (in reply to BeTheBall)


clum1 Posts: 762 From: Glasgow, Scotland Status: offline	RE: Access Database Question - 1/21/2005 9:48:23 Not sure about SQL, but we successfully migrated a couple of large (50Mb+) Access dbs over to MySQL with MdbToMySQL. If I remember correctly, it went pretty smoothly, and now all my new db development is done on this excellent - free - platform. Calum _____________________________ kenilweb.com; simple, effective web design "So I said to the Gym instructor "Can you teach me to do the splits?" He said "How flexible are you?". I said "I can't make Tuesdays"." Tim Vine (in reply to eschulze)


eschulze Posts: 79 Joined: 1/5/2004 Status: offline	RE: Access Database Question - 1/21/2005 10:18:33 Here's the "million dollar question" Can the FrontPage DRW be used with a database in MySQL or SQL? I would suppose I would need to change the connection in the global.asa file, but would I have to recreate all the pages I have done using the DRW? (in reply to clum1)


clum1 Posts: 762 From: Glasgow, Scotland Status: offline	RE: Access Database Question - 1/21/2005 10:27:42 Not sure about this, but if you check the articles here and on w3Schools.com you'll be able to code asp yourself in no time! calum _____________________________ kenilweb.com; simple, effective web design "So I said to the Gym instructor "Can you teach me to do the splits?" He said "How flexible are you?". I said "I can't make Tuesdays"." Tim Vine (in reply to eschulze)


eschulze Posts: 79 Joined: 1/5/2004 Status: offline	RE: Access Database Question - 1/21/2005 10:39:57 I'm not too concerned about coding .asp, its the turn time that concerns me. I was hoping that I could make the change quickly and change over to pure asp code in time. The site is live right now and I was hoping to see if there was a quick fix moving to SQL but leaving the pages coded as they are for the time-being. (in reply to clum1)

rdouglass

Posts: 9280
From: Biddeford, ME USA
Status: offline

RE: Access Database Question - 1/21/2005 11:07:40

quote:

I'm not too concerned about coding .asp, its the turn time that concerns me.

Once you've converted 1 DRW over to arrays, the rest go very quickly. I've said many times before that you can support 10 times the number of users on an Access DB just by 'tossing' the DRW and coding arrays with ASP. Here's an example of a DRW turned into array-based.

Here's the DWR:

<table border="1">
  <thead>
    <tr>
      <td><b>UserID</b></td>
      <td><b>UserName</b></td>
    </tr>
  </thead>
  <tbody>
    <!--webbot bot="DatabaseRegionStart" startspan
    s-columnnames="UserID,UserName,Password,aps_uid,x_lastname,x_firstname,x_email,EmailUse,AddDatetime,UpdateDatetime,AccessLevel,Admin,ActiveAccount,AccountExpires,RemoteAddress,LoginCount,x_company,x_address,x_city,x_state,x_zip,x_phone,x_fax,u_id,isManager,dept,dateOfHire,PTODays,shiftNumber,lunchCode,deptB,directPhone,keyCode,PTOCarryover"
    s-columntypes="3,202,202,202,202,202,202,11,135,135,202,11,11,135,202,3,202,202,202,202,202,202,202,3,202,202,135,3,3,3,202,202,129,4"
    s-dataconnection="CAIntNet" b-tableformat="TRUE" b-menuformat="FALSE"
    s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="FALSE"
    b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE"
    i-ListFormat="0" b-makeform="TRUE" s-recordsource="tblUsers"
    s-displaycolumns="UserID,UserName" s-criteria s-order
    s-sql="SELECT * FROM tblUsers" b-procedure="FALSE" clientside
    SuggestedExt="asp" s-DefaultFields s-NoRecordsFound="No records returned."
    i-MaxRecords="256" i-GroupSize="0" BOTID="0"
    u-dblib="fpweb:///_fpclass/fpdblib.inc"
    u-dbrgn1="fpweb:///_fpclass/fpdbrgn1.inc"
    u-dbrgn2="fpweb:///_fpclass/fpdbrgn2.inc" tag="TBODY"
    local_preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left" width="100%"><font color="#000000">Database Results regions will not preview unless this page is fetched from a Web server with a web browser. The following table row will repeat once for every record returned by the query.</font></td></tr>"
    preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left" width="100%"><font color="#000000">This is the start of a Database Results region. The region will not work unless the page has a file extension of ".asp".</font></td></tr>" --><!--#include file="fpweb:///_fpclass/fpdblib.inc"-->
<%
fp_sQry="SELECT * FROM tblUsers"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=2 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="CAIntNet"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="fpweb:///_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" I-CheckSum="59889" endspan -->
    <tr>
      <td><!--webbot bot="DatabaseResultColumn" startspan
        s-columnnames="UserID,UserName,Password,aps_uid,x_lastname,x_firstname,x_email,EmailUse,AddDatetime,UpdateDatetime,AccessLevel,Admin,ActiveAccount,AccountExpires,RemoteAddress,LoginCount,x_company,x_address,x_city,x_state,x_zip,x_phone,x_fax,u_id,isManager,dept,dateOfHire,PTODays,shiftNumber,lunchCode,deptB,directPhone,keyCode,PTOCarryover"
        s-column="UserID" b-tableformat="TRUE" b-hasHTML="FALSE" clientside
        local_preview="<font size="-1"><<</font>UserID<font size="-1">>></font>"
        preview="<font size="-1"><<</font>UserID<font size="-1">>></font>" --><%=FP_FieldVal(fp_rs,"UserID")%><!--webbot
        bot="DatabaseResultColumn" I-CheckSum="16626" endspan -->
      </td>
      <td><!--webbot bot="DatabaseResultColumn" startspan
        s-columnnames="UserID,UserName,Password,aps_uid,x_lastname,x_firstname,x_email,EmailUse,AddDatetime,UpdateDatetime,AccessLevel,Admin,ActiveAccount,AccountExpires,RemoteAddress,LoginCount,x_company,x_address,x_city,x_state,x_zip,x_phone,x_fax,u_id,isManager,dept,dateOfHire,PTODays,shiftNumber,lunchCode,deptB,directPhone,keyCode,PTOCarryover"
        s-column="UserName" b-tableformat="TRUE" b-hasHTML="FALSE" clientside
        local_preview="<font size="-1"><<</font>UserName<font size="-1">>></font>"
        preview="<font size="-1"><<</font>UserName<font size="-1">>></font>" --><%=FP_FieldVal(fp_rs,"UserName")%><!--webbot
        bot="DatabaseResultColumn" I-CheckSum="17279" endspan -->
      </td>
    </tr>
    <!--webbot bot="DatabaseRegionEnd" startspan b-tableformat="TRUE"
    b-menuformat="FALSE" u-dbrgn2="fpweb:///_fpclass/fpdbrgn2.inc"
    i-groupsize="0" clientside tag="TBODY"
    local_preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left" width="100%"><font color="#000000">This is the end of a Database Results region.</font></td></tr>"
    preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left" width="100%"><font color="#000000">This is the end of a Database Results region.</font></td></tr>" --><!--#include file="fpweb:///_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" I-CheckSum="23298" endspan -->
  </tbody>
</table>

and here's the array-based one:

<table border="1">
  <thead>
    <tr>
      <td><b>UserID</b></td>
      <td><b>UserName</b></td>
    </tr>
  </thead>
  <tbody>
  <%
	myDSN = "DSN=xxx;uid=xxx;pwd=xxxx;database=xxxxt"
	mySQL = "SELECT * FROM tblUsers"
	set conntemp=server.createobject("adodb.connection")
	conntemp.open myDSN
	set rstemp=conntemp.execute(mySQL)

	IF  rstemp.eof THEN
	   response.write ("<tr><td>ERROR 127:NO USERLISTQ1  " & userSQL & "</td></tr>")
		rstemp.close
		set rstemp=nothing
		conntemp.close
		set conntemp=nothing
	ELSE
		alldata=rstemp.getrows
		rstemp.close
		set rstemp=nothing
		conntemp.close
		set conntemp=nothing
		FOR i = 0 to ubound(alldata,2)
    %>
    	<tr>
      		<td><%=alldata(0,i)%>
      		</td>
      		<td><%=alldata(1,i)%>
      		</td>
    	</tr>
    	<%
    	NEXT
    END IF%>
  </tbody>
</table>

Much smaller and faster than any DRW. SQL Server support can be costly and (IMO anyways) converting to mySQL will require a greater learning curve. And you'll still be using the DRW

.

Again, once you learn arrays, you'll never want to go back. You get rid of issues like trying to nest DRW's and stuff like that. Yes, people are correct when they say that Access is limited, but efficient coding for the DB can vastly increase the performance.

And look at the code. If you have no issues coding ASP, then you should be able to recognize much of what I've posted. I hope it helps (and convert you back over from the Dark Side...

)

EDIT: I removed my SQL Server info. OOPS!

_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to eschulze)


dpf Posts: 7126 Joined: 11/12/2003 From: India-napolis Status: offline	RE: Access Database Question - 1/21/2005 11:48:09 rdouglas: do I understand correctlyu that after you pull the data into the array (alldata) , that you can utilize the array after the connection and recordset are closed? thanks _____________________________ Dan (in reply to rdouglass)


eschulze Posts: 79 Joined: 1/5/2004 Status: offline	RE: Access Database Question - 1/21/2005 11:56:43 rdouglass ... where can I find more information about arrays ... and how they compare to the DRW? Also, what is the advantage to the array over the Spooky Diet? Can I assume it is because you can access the array after the connection is closed? (in reply to dpf)


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: Access Database Question - 1/21/2005 11:59:16 quote: rdouglas: do I understand correctlyu that after you pull the data into the array (alldata) , that you can utilize the array after the connection and recordset are closed? Absolutely! That is one of the biggest reasons to use arrays; close that DB connection to free it up for someone else. _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to eschulze)


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: Access Database Question - 1/21/2005 12:06:04 quote: rdouglass ... where can I find more information about arrays ... and how they compare to the DRW? Coupla' good sites: http://www.learnasp.com/learn/ (lotsa' good ASP here) http://www.aspfaqs.com/aspfaqs/ShowCategory.asp?CatID=1 (pretty good FAQ) DRW's are definitely easier for the beginner but IMO are extremely limited in how you can manipulate the data. Try putting a DRW dropdown inside a DRW results - . Also, as I stated earlier, in terms of performance, DRW's are so full of bloat (and use open recordsets) that they are very slow compared to arrays at least if you grab the data and close the connection ASAP. See, that's why the .ldb issue; Access is bieng held open the whlole time the DRW is drawing the table. With an array, you can grab all the data, close the connection, and then draw the table. That help any? _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to rdouglass)


Spooky Posts: 26617 Joined: 11/11/1998 From: Middle Earth Status: offline	RE: Access Database Question - 1/21/2005 19:05:09 The advantage of using arrays is server side. Its more efficient to get the information from the provider in one hit, rather than asking it several (thousand) times. The time to display the html output improves as a result, but not as dramatically as the Spooky diet. The spooky diet is more about download speeds than it is about server efficiency (which to be honest, from a server perspective, the diet changes the server processing very little) In real world terms, an example would be the Snitz forum. When I converted it from .movenext to getrows, the processing that used to take about 2.5 seconds, then took 0.2 seconds (pure processing time) This frees up the server resources immensely, but in reality does not do much for percieved user / browser speed. For heavy user sites, its all about server efficiency first. In terms of display speed - ( the spooky diet) perceived user speed is dramatically changed. Imagine trying to download a html page that weighs in at 500k compared with one that is 1/10 that size? We are talking minutes of waiting time. _____________________________ If you arent part of the solution, then there is good money to be made prolonging the problem ��k� Database / DRW help Check out the Tutorials for Frontpage Databases Forum FAQ' s Developers Edition Spooky Login User Registration and Management tool Personalized Frontpage Hosting (in reply to rdouglass)