Too many records? Or something else

[James Bowyer]

A database I created a while ago is now having problems with new records, they are saving properly, and show up in the tables ok, but when you try and go back into them, they are showing up as blank. However, earlier records aren't. I'm beginning to think that there may be too many records for Access to cope with, but I though there was no limit (within reason). There are currently just over 1300 rows in this table, and 42 columns, of which one is a memo field, which could potentially be gotten rid of.

Is this a problem anyone else has had?

[zmbd]

- What version of access are you using?
--- If you are using ACC2007/2010, then what format is the database file?
- What is the size of the database file itself?
- Is the database split into front/back-ends (if you don't know, go to the linked-table manager, if there are any linked tables, then there is a split).

Those memo fields can take up alot of room. Also any "attachment " field with pictures/files stored within the database will take up space quickly.

[James Bowyer]

Access 2010, MDB file format. 15MB back end db size, but thats lots of tables, no attached files.

[zmbd]

That's a quite small backend.
I have one approaching 1GB with 10K entries across 10 or so tables.

Let's try the old standard of compact and repair first as often the simplest things will fix the issue:
1) Close all of the front ends.
2) Reboot the PC you're working with... this will ensure that you have no open locks to the file. Normally not required; however, I've had occations that when weird things start happening to the DB a reboot is the way to go.
(Please have all of the other client PCs reboot too to ensure that all locks have been cleared.)
3) BACKUP THE BACKEND! - DO NOT open Access to do this. Make the copy with a simple right-click-drag-n-drop to a new folder or copy-n-paste, etc... just do not use Access to do this backup at this point.

I can not stress enough how importaint it is to keep and maintain regular backups. I have this built into all of my split databases to make a backup when the last person exits and the first person starts the day.

4) Open the backend directly. Ribbon>Database Tools>Compact and Repair.
5)Close your database.
6) Optional: Now I don't know if this is really needed; however, I tend to do a reboot at this point too. This way I know that all of the locks have been released.

If this doesn't fix the issue, then we will need to know the number of tables, queries, and forms in the front end.

(BTW: 1 -6 on your frontend wouldn't hurt either.)

[James Bowyer]

Its on a terminal services server, so rebooting is going to be fun (but necessary!), completely forgot about compact and repair, so I'll try that first, on both front and back ends.

[James Bowyer]

Long time between posts, but, we've compacted and repaired both front and back end, and are now trying to use it on a non terminal services machine, but no matter what machine we use, some forms still open blank, despite them appearing fine in the tables. Any other reasons why this should happen?

[zmbd]

1) And I just love the 20 question thing....
simple thing:
Open the front-end
Backstage
Options
Current Database
(scroll to bottom)
Caching Web Services and SharePoint tables
Select Use the cache format that is .... and later
Select Clear Cache on Close

2) Are the forms based on a query and are you absolutly certain that there hasn't been some change in the data?
This is the number one reason that things like this occur.

If that doesn't work:
3) Decompile and recompile the front end ACCDB file.
Create a short cut
For a Windows7(64) OS in the target for the shortcut:

"C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" /decompile

The main thing is the switch

Drag and drop the Front End file ontop of this new shortcut
after it opens
C&R the front end again
Open the VBA and recompile
Save
reopen the front end as normal

If that isn't the case then:
4) Create a new ACCDB file for the front end, import everything into this new front end.

5) do the same for the back end as was done for the front end in (4)

As I've said, I have very large databases split over a LAN (some are pushing the 2GB limit in the back ends (^_^) ) and I've never seen the issue you are having, ofcourse, all of mine are direct LAN or Desktop.