Limit the maximum number of records in a database?

[DaveO]

Hi,

Is it possible to limit the amount of records in a database? I have a table which I am using to store default parameters for the application and I dont wish this table to ever contain more than 1 record.

Dave

 

[SydneyGeek]

It's not possible to limit the number of records directly but you can easily ensure (in code) that you only ever have a single record in Parameters.
Can't give any more help until you provide some more details -- how is the Parameters table connected to the rest of the application? Is it directly related to anything (probably not a good idea) or an isolated table to which you can refer?
Also, what version of Access are you using? This needs some code, and I tend to work in DAO rather than the newer ADO -- more familiar with the object model.

 

[DaveO]

Thanks,

Not entirely sure what you mean by ADO and DAO, as This is my first serious foray int Access, however, I seem to have got the effect I want by setting the form for the table to Allow Additions=no, Allow Edits=yes, allow Deletions=no

Dave

 

[mdmilner]

DAO and ADO are the object models for writing VBA code within MS applications. Open up a module (or make one) within Access, select view then Object Browser. What you'll find is a somewhat confusing online list of Objects and Properties that you can look at or manipulate programmatically.

My guess is, you're writing code within Access and just didn't know that you were either using DAO or ADO. Not sure how else you could be pulling application parameters.

If it helps, I've done the same thing a few times and wrote this up to extract my own little magic into other functions. This is using DAO

Public Function FindDefaults(ByVal MyDefaults As String) As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select * from tblDefaults", dbOpenSnapshot)

With rst
    .FindFirst "TypeOfDefault='" & MyDefaults & "'"
    If !TypeOfDefault = MyDefaults Then
        FindDefaults = !DefaultInfo
    Else
        FindDefaults="NotFound"   'Part of Error routine
    End If
End With

Set rst = Nothing
Set dbs = Nothing

End Function

And this to maintain it:

Public Function SetDefaults(MyDefaults As String, strEntry As String, strMod As Integer)
Dim dbs As DAO.Database
Dim rs As DAO.Recordset

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("Select * from tblDefaults", dbOpenDynaset)

With rs
  .FindFirst "TypeOfDefault='" & MyDefaults & "'"
  If !TypeOfDefault = MyDefaults Then
      .Edit
      .Fields(strMod).Value = strEntry
      .Update
    Else         'If can not find it, create a new one
      .AddNew
      .Fields(0).Value = MyDefaults
      .Fields(strMod).Value = strEntry
      .Update
  End If
End With

Set rs = Nothing
Set dbs = Nothing

End Function

Feel free to borrow - it's pretty straightforward.

Mike

 

[bat17]

one method would be to add a field to the table, set it to a number type, set Indexed to Yes(No duplicates), set its default value to 0, set Required to Yes, set its validation rule to "=0".
This will only allow one record.

HTH

Peter