Should I store all data?

[rjvega]

I'm creating a database to store information regarding the storage and
history of some production tools. I also want the users to be able to use
the database to create an order sheet. Most of the information in the form,
however, is not needed for historical records.

I created a form where users enter information for a new tool. My original
plan was to have only some of this data write to the main data table. I then
wanted to have this form generate a report which would in turn become the
order sheet. I want the information that is not stored in the table to
appear on this report.

The reason I chose not to keep some information (which are comprised of 15
text fields) is to limit the size of the database. My question is, assuming
we have about 4000 - 5000 records, how much am I gaining by not saving these
records, and am I creating more trouble for myself by trying to create a
report with unsaved data?

 

[Brendan Reynolds]

Let's try it and see ...

Public Sub CreateTestData()

Dim strSQL As String
Dim lngLoop As Long
Dim lngRecords As Long

'careful with these next lines - don't run them if you
'have a table with this name you don't want to lose

'On Error Resume Next
'strSQL = "DROP TABLE TestTable"
'CurrentProject.Connection.Execute strSQL
'On Error GoTo 0

strSQL = "CREATE TABLE TestTable ("
For lngLoop = 1 To 15
strSQL = strSQL & "Field" & Format$(lngLoop, "00") & " varchar(50),
"
Next lngLoop
strSQL = Left$(strSQL, Len(strSQL) - 2) & ")"
CurrentProject.Connection.Execute strSQL

For lngRecords = 1 To 4000
strSQL = "INSERT INTO TestTable VALUES("
For lngLoop = 1 To 15
strSQL = strSQL & "'" & String$(50, "X") & "', "
Next lngLoop
strSQL = Left$(strSQL, Len(strSQL) - 2) & ")"
CurrentProject.Connection.Execute strSQL
Next lngRecords

End Sub

Imediately after running this code, the size of the MDB on my system was
8,192 KB.

Not exactly huge by todays standards, is it?

 

[Jerry Whittle]

4000-5000 records? Not much. Aside from Memo fields and a few other things, a
record can be a maximum of 2,000 characters so that isn't much even if you
fill all 5000 records to the maximum size.

Keep the data. You'll be glad that you did some day.

 

[Craig Alexander Morrison]

ADPs have been deprecated and are of little use if you are using a REAL
grown-up database like DB2.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Small Business Solutions Provider

 

[John Vinson]

My question is, assuming
we have about 4000 - 5000 records, how much am I gaining by not saving these
records, and am I creating more trouble for myself by trying to create a
report with unsaved data?

Yes... you're just creating more trouble for yourself.

When you have 4,000,000 to 5,000,000 records, you may want to start
seriously considering SQL/Server and/or data weeding - but if you need
the data for one report today, you very likely will need it for
another report next year.

John W. Vinson[MVP]

 

[Steve Schapel]

Craig,

ADPs have been deprecated ...

This is the second time I have seen this recently. Have you seen some
announcement to this effect? Certainly, many Access developers,
including myself, would regard ADPs as a solution to a non-existent
problem, and see little use for them. Nevertheless, they seem to be
alive and well in Access 2007, and no sign of deprecation as far as I know.