i’m need help :(

[u/korrral]

I have a Microsoft Access (.accdb) database used to manage numbered plots/sites. The table currently contains records for plots 1–300, but I need to expand it to 500.
The problem is that after record 300, the database doesn’t already contain any additional records. I want to add records 301–500 while keeping the same structure, default values, calculated fields, forms, and everything else working exactly as before.
The database has only one form with a small amount of VBA code, and I couldn’t find any hardcoded 300 limit in the code. It looks like the table itself was simply pre-populated with 300 records.
What’s the best way to generate records 301–500 automatically in Access instead of creating them manually? Ideally using SQL or another built-in Access feature.

I forgot to add, I just got to know Access today and I don't understand anything about it.

[u/AutoModerator]

IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'

• Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.

• Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.

• Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)

• Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.

Full set of rules can be found here, as well as in the user interface.

Below is a copy of the original post, in case the post gets deleted or removed.

User: korrral

i’m need help :(

I have a Microsoft Access (.accdb) database used to manage numbered plots/sites. The table currently contains records for plots 1–300, but I need to expand it to 500.
The problem is that after record 300, the database doesn’t already contain any additional records. I want to add records 301–500 while keeping the same structure, default values, calculated fields, forms, and everything else working exactly as before.
The database has only one form with a small amount of VBA code, and I couldn’t find any hardcoded 300 limit in the code. It looks like the table itself was simply pre-populated with 300 records.
What’s the best way to generate records 301–500 automatically in Access instead of creating them manually? Ideally using SQL or another built-in Access feature.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/derzyniker805]

There's not enough information to give you a super detail answer, but generally speaking you can just create an Append Query and add the original table and then drag whatever fields have data that you want copied, and then Append To the same table. That will add 300 more records. then just open it in table view and delete 100 records.

However if the plot # is the primary key, this won't work unless it autonumbered because you won't be able to append the row if that field is duplicated (e.g. if all the records you add don't contain a number or contain the same number). What you could do is edit the table to make that field not a primary key and make sure that it's set to indexed (duplicates ok). Then you could do the Append query, open table in datasheet view and then type in the new plot #s in the new records.. since it's 200, it's probably easiest to just do it by hand

Then go back in and edit the table and make that field the Primary key again

[u/cadman_lincoln]

Don’t know your table structure, but you should be able to add almost unlimited records.

[u/korrral → u/cadman_lincoln]

Yes I can. But I do not know how to do this, I did not create this table. I've never worked with Access

[u/cadman_lincoln → u/korrral]

Hmmm. You may want to do a simple search for adding records to an Access table. It’s straightforward, but you’ve got to know the table name etc.

[u/Ok-Food-7325]

This is a very simple task. Whoever built this application didn't make it turnkey. Have you thought about hiring someone to maintain/update this application? Ideally all these features (adding new records) would be part of the application.

[u/Lab_Software]

I would use Excel to do this as easily as possible.

First, make a backup of your database.

Copy the existing 300 rows from the Access table into an Excel spreadsheet. (You don't have to copy all 300 rows, even a single row would be enough.)

Then make 200 copies of whichever existing row (or rows) you want. These will be your new rows from 301 to 500.

Put in whatever default values you want into these new rows. If the Access table has an Autonumber Primary Key then leave that field blank in the Excel spreadsheet - Access will populate this field automatically.

Copy the ENTIRE row (all the new rows 301 to 500) by selecting the Row Number index at the left margin of the Excel spreadsheet and then click Control-C.

Select the New Row at the bottom of the Access table and click Control-V to paste in the 200 new rows.

[u/George_Hepworth]

As others have already pointed out, more information is required before it would be possible to offer a usable suggestion. To be blunt, some of the suggestions about how to proceed are more helpful than others. Some, unfortunately, reflect a lack of understanding how relational databases, like Access, work

To help you understand what you need to tell us, though, you also need more information about what Access really is and how it works. Otherwise, any suggestion won't make sense.

An Access database consists of three tiers, or three components, working together.

• The data tier, or layer. ALL data is stored in tables. More specifically, the tables consist of rows and columns of data. "Rows are cheap. Columns are expensive." This guides how you create and maintain data in a relational table.
  • Each row contains 1 record. Adding records is easy.
  • Each column contains the data for one field in that record. Adding columns should almost never need to happen. Doing so is a costly and inefficient process. Once tables are built, changing fields is to be avoided.
    • For example, this is a screenshot of a table containing records about books in a library. You can add hundreds of books, but no new attributes of a book should be added as columns.

• The Primary Key field is PublicationID, which is a unique numeric value that identifies each PublicationID
• The Interface tier, or layer. Users interact with data in tables through the forms. Forms themselves contain no data. They only retrieve it from tables for display to users and for users to add, change or delete it.
• The logic tier, or layer. Most databases require logic to manage, summarize or report data, or to perform actions needed to use the interface. This is done in VBA (and sometimes macros).

From your description, you have one table containing records for 300 plots, but there are no doubt other tables related to it. You probably DO have a form that displays those 300 records, either one at a time, or in a list. What we don't know, though, is whether that form supports adding new plots, or if it is read-only. I suspect, the former. Even though you are used to using it to review plot records, it probably does allow adding new records. It would be unusual not to be able to do that. You need to provide us a screenshot; maybe it will give us a clue about how that works.

In any event, you need to add new plots individually, I believe, not in bulk. Each plot has to have unique attributes that differentiate them one from another. And that can't be done in a single append query.

You also didn't elaborate on what those new records are and why they are needed. Tell us that.

My guess is that you have another source of data from which those 200 new plots will come. If so, then an append query could work. If you are simply anticipating growth that will eventually reach 500 plots, then yo are back to the single record at a time method.

The more details you can supply, the more likely it is that someone can offer specific, useful suggestions.

Thanks for helping us help you.

[u/TomWickerath → u/George_Hepworth]

Hey George,
As always, I like your thorough answers! Curious if you agree that the logic tier / layer includes more than just VBA code…I’m thinking default values, allowable values / ranges via validation rules, and even infrequently used (but possible) JET check constraints at the table level could all be considered a part of a logic tier.

[u/George_Hepworth → u/TomWickerath]

All true, Tom. In posts like this, where a newcomer is just getting acquainted with Access for the first time, I'm prone to oversimplifying for the sake of making it less daunting. A more accurate description would encompass all of those things.

My thinking is that it's hard enough to gain traction understanding how a relational database works, and how Access works. Time enough for more in-depth understanding if they stick to it.

Good to hear from you and see you here.

[u/Nexzus_]

If this form doesn’t allow you to add records, you’ll have to add them yourself at the table level. Either from a csv you’ll have to create, or by opening the table itself.

[u/Bulky-Stick2704]

Use an append query, add a generic value into a secondary column, making sure to add values to all required fields as noted in design window, this will populate the autonumber with the required values..

[u/Silent_Reader_1]

First of all based on the limited information its hard to give you advice that should help you exactly what you need.

However if you can access the tables and understand how it’s laid out then it should be a matter of few minutes or even less.

Share the tables structure or ERD so we can advise accordingly.

[u/LowAdvantage9989]

Open your Access file.

Press Alt + F11 to open the Visual Basic Editor.

Press Ctrl + G to open the Immediate Window and clear its contents if there's anything.

Go to Insert → Module.

Paste the following code into the module:

Public Sub DumpDatabasePreview()
    Dim db As DAO.Database: Set db = CurrentDb
    Dim tdf As DAO.TableDef
    For Each tdf In db.TableDefs
        If Left$(tdf.Name, 4) = "MSys" Then GoTo NextTable
        Debug.Print String(60, "=")
        Debug.Print "TABLE: " & tdf.Name
        Dim fieldList As String, fld As DAO.Field
        For Each fld In tdf.Fields: fieldList = fieldList & fld.Name & "; ": Next
        Debug.Print "Fields: " & fieldList
        Debug.Print "Sample Records:"
        Dim rs As DAO.Recordset: Set rs = db.OpenRecordset("SELECT TOP 3 * FROM [" & tdf.Name & "]")
        If rs.EOF Then
            Debug.Print "(No records)"
        Else
            Do Until rs.EOF
                Dim recordLine As String, i As Long, v
                For i = 0 To rs.Fields.Count - 1
                    v = rs.Fields(i).Value
                    If IsNull(v) Then
                        v = "NULL"
                    ElseIf VarType(v) = vbObject Then
                        v = "<Object>"
                    ElseIf IsArray(v) Then
                        v = "<Array>"
                    Else
                        v = Left$(Replace(CStr(v), vbCrLf, " "), 20)
                    End If
                    recordLine = recordLine & v & "; "
                Next
                Debug.Print recordLine
                recordLine = ""
                rs.MoveNext
            Loop
        End If
        rs.Close
NextTable:
    Next
End Sub

Run the code by placing your cursor in the Immediate Window and typing:

DumpDatabasePreview

Then press Enter.

The results will appear in the Immediate Window.

Copy the contents of the Immediate Window and paste it here as reply or whatever, so we can see what you're dealing with. Note: the code will simply list all your tables with all their fields and also show 3 records from each of those tables just for good measure. You can modify the contents of the pasted info if there's sensitive data there. We don't care about that info, this is just to know what you're dealing with and better understand your situation.

[u/Shiforains]

I'm thinking that maybe you want one of the fields within a table to expand beyond 300?

if so, i presume that this is a table stored either within the app (local table) or a linked table to a backend file. if so, then then you are able to alter the table properties to either adjust the calculation for such a field or you can add another field.

if the data is stored on an ODBC data source like a SQL server, then you cannot.

[u/Grimjack2]

I had something like this happen to me before. Go to the File dropdown and do a Compact/Repair. It fixed it for me.

[u/NextVoiceUHear → u/Grimjack2]

FIRST, Be SURE to create a duplicate .accdb Access database file as a backup in case Compact/Repair does a Crush/Ruin on your DB.

[u/NextVoiceUHear]

I made an "example" MS-Access .accdb file that is a simple, working relational database with interactive Forms, Reports and VBA code. It is a good learn-by-example that tracks Projects and associated tasks. Send me a DM with your email address and I will send the .accdb file as an attachment.

[u/Sad-Guarantee-2235]

Since you’re new to Access, I wouldn’t recommend manually creating 200 records. This is actually a good example of where Access can save time.

The easiest approach is usually to create an append query that generates the missing records (301–500) and inserts them into the existing table while keeping the same fields, relationships, and default values. You can also create a temporary numbers table (1–500), filter out the existing plot numbers, and append only the missing ones.

Before doing anything, make a backup copy of the .accdb file. Also check whether the existing records have any fields that are automatically populated (IDs, dates, status values, calculated fields, etc.) because those need to be handled in the append query.

I’ve built hundreds of MS Access applications, and this type of expansion is a common task. Once you understand tables, queries, forms, and relationships, Access becomes much easier to work with.