|
| |
|
|
pd_it_guy
Posts: 191
Joined: 3/4/2008
Status: offline
 |
import HUGE Access file to FP - 8/14/2008 15:02:04
My boss just told me to set up an Intranet FP website, the purpose of which is to act as a front end to an entire VMX legacy alpha record system. View, search, etc.
I just converted the name/case file (about 70Mb), and it has over 479,000 records in it, in about 12 columns. It's presently on my desktop, and seems to open and close just fine, and in Access I can scroll through the nearly half million records. There are about 7 old files like this to be converted to Access.
So here are the questions before I venture forth:
-Is this getting too big for Access, or is it exactly what it was designed for
-Should I drop these mdb's right into the web itself (in the fpdb folder) or keep them in a safe place and have the FP web point to them. If I put them in the actual web how do I get the wizard to "see" it to make the connection since I am not starting with a form.
-to do the normal SQL stuff (search, select, update, delete) should I use the FP data connection (on the diet of course) or the ADO method. Or how?
Thanks in advance for all advice and direction provided.
|
|
|
|
TexasWebDevelopers
Posts: 202
Joined: 2/22/2002
From:
Status: offline
|
RE: import HUGE Access file to FP - 8/14/2008 15:36:12
Access the stand-alone app vs. Access the web db are related but different animals.
The limits are usually stated like this:
"Access is capable of storing tables up to 2 Gigabytes however, because your database can include linked tables in other files, its total size is limited only by your machines available storage capacity."
Access database general program specifications
Microsoft Access database (.mdb) file size 2 gigabytes.
Number of objects in a database 32,768
Modules 1,000
Number of concurrent users 255
Access database table specifications
Number of characters in a table name 64
Number of characters in a field name 64
Number of fields in a table 255
Number of open tables 2048 The actual number may be less because of tables open internally by Microsoft Access.
Table size 1 Gigabyte
Number of characters in a Text field 255
Number of characters in a Memo field 65,535 when entering data through the user interface; 1 gigabyte when entering data programmatically.
Size of an OLE Object field 1 Gigabyte
Access database query specifications
Number of enforced relationships 32 per table minus the number of indexes that are on the table for fields or combinations of fields that are not involved in relationships
Number of tables in a query 32
Number of fields in a recordset 255
Recordset size 1 Gigabyte
Form and report specifications
Number of characters in a label 2048
Number of characters in a text box 65,535
Number of printed pages in a report 65,536
So to your first question "Is this getting too big for Access" the answer is no. However the caveats are many. Is the database sitting on the same server as the web site? Is the web site sitting on a shared server? How well are your sql statements written? All of these will greatly impact the applications ability to run quickly. For instance, using SELECT DISTINCT ... will cause all of the db rows to be returned and then the duplicate rows to be discarded...a really poor way to call the data from a large recordset.
To the second question--security of the db is more a "hide and seek" game of obsfucation more than anything else. The general suggestion is to name the db folder something un-guessable (ditto for the db name itself) and then keep them out of the root web. IMHO you shouldn't be using wizards at this point to do anything.
Learn how to use simple code to open a db; create a record set; display data; close the db connection and recordset; You'll never go back to the wizards again:
<%Response.Buffer = True%>
<!-- #include file="adovbs.inc" -->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>db</title>
</head>
<body>
<%
Dim Conn,RS,strSQL,strConnection
strConnection="Provider=MICROSOFT.JET.OLEDB.4.0; " & "DATA SOURCE=" & Server.MapPath("/database_folder/database.mdb")
strSQL = "SELECT [field] FROM [table];"
set Conn = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
Conn.Open strConnection
set Rs.ActiveConnection = Conn
Rs.LockType = adLockOptimistic
Rs.CursorType = adOpenKeySet
Rs.Open strSQL
Do While Not rs.EOF
field=Rs("field")
%>
<!--display fields-->
field=<%=field%>
<%
rs.MoveNext
Loop
%>
</body>
</html>
<% Rs.Close
Set Rs = Nothing
Conn.Close
Set Conn = Nothing
end if%>
| |
|
|
|
pd_it_guy
Posts: 191
Joined: 3/4/2008
Status: offline
|
RE: import HUGE Access file to FP - 8/14/2008 16:17:42
Thanks TWD (once again) for the most through and detailed reply. I think you have answered most, if not all of my concerns and provided some excellent direction. We will be attentive to our queries. I dont think any of the tables are going to have to link to each other as they were made well before ODBC was ever devised and there are no common keys.
For now, I think we will stay with Access. The web server we use is new, pretty rugged, backed up on a regular basis, and archived copies exist on alternate media. We have a fairly well controlled and accountable environment so access security is not an issue.
As to the ADO, I will copy that part off and try to make it work. It seems like a lot fewer moving parts than the standard FP database query even when boiled down to basics.
Thank you again for sharing your time and wisdom. I will get to work and report back any issue.
| |
|
|
|
pd_it_guy
Posts: 191
Joined: 3/4/2008
Status: offline
|
RE: import HUGE Access file to FP - 8/26/2008 21:10:36
I hope your specs and limits were right. The legacy alpha db now has 8 tables, about 900,000 records in all, and the whole thing weighs in at a respectable 325 Mb. It is on the Intranet server in a folder, and not part of the actual web. We use FP for the database connection but that's about it- all strip down code, just the 3 gray include files, and the red. So far, it's performing very well.
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: import HUGE Access file to FP - 8/26/2008 21:57:03
quote:
We use FP for the database connection but that's about it- all strip down code, just the 3 gray include files, and the red. So far, it's performing very well.
Dude, run away from that fast before you get yourself in trouble.
Unless you're planning on only 2 or 3 people using that Access DB, expect problems using those ODBC and recordset connections. You WILL crash sooner or later.
The only way you should ever consider doing something like this in Access (IMO of course) is to use absolutely NO RECORDSET connections and NO DRW's. They just plain keep the DB connection open too long for those larger sized tables.
Now if you wrote all your data into memory using getstring or getrows, then yes, it might be OK but never with a DRW connection. If you understand how using recordsets and move.next how it keeps the DB open the whole time it's doing that, you'll understand what I'm talking about.
Also, Access might work fine right now but run just one or two big table updates without Compact and Repair and see how it will come to it's knees. Remeber that it still is in reality a flat file DB.
I am by no means against Access; I use it in some pretty heavy-duty situations and always write my ASP code using getrows so I can usually get about 40 times the raw performance of a DRW. But in this situation, I have to wave my arms and say "Warning! Danger, Will Robinson!"
Try MSDE (it's just SQL Server 'lite'). It's free, much faster and will do the job much better than Access. And you should only have to worry about dates moving from Access to MSDE.
The only problem you'll have is to convince your boss why you need to spend the extra time putting it on MSDE. And at least if you say so now, you'll be able to say "I told you so."
</$.02>
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
pd_it_guy
Posts: 191
Joined: 3/4/2008
Status: offline
|
RE: import HUGE Access file to FP - 8/27/2008 9:55:33
Thanks for the comments, and your point(s) are well taken. We may have some temporary salvation since it is only to perform records checks now and then, and usually by only a few people, but we sure don't need it to die out. Also, we still have the text files that came out of the old VAX to re-import, if need be.
It is probably time for MSDE or SQL server. And straight ADO. With the ADO modality, then, we no longer need those gray <!include!> files, or global.asa, right?
Would there be any benefit in breaking this 300+ Mb monster up into its 8 parts, as separate mdb's?
I have much to learn. It's time for me to do some reading. Hope the Access can hold out.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
