Access 2010 Can't Get To Table Design Mode

[hlewton]

I am trying to make changes to the table in one of my Access databases but when trying to get to the design view I get the dialog box I will attach. There is no other program using this file. I created it in Access 2003 but have been using it in Access 2010 for a couple years now but this is the first time I tried making changes to the design. How can I get past this dialog box and get to the design view for this table?

Thanks

Table.jpg

[HansV]

Do you have a query, form or report open that is bound (directly or indirectly) to the table?

[hlewton]

I do have queries and forms but nothing was open at the time. I thought that may be the reason so I shutdown the Access database and tried again. Same results so I even rebooted and tried again only to get the same dialog box once more.

[HansV]

Since you have already done the things I was going to suggest, we'll continue to the next step:
- Create a blank new database.
- Import all database objects (tables, queries, forms, reports, macros and modules) from the 'problem' database into the new one.

[hlewton]

Not sure where to go from here but I did all the above and was able to open the table in the design view. I then tried exporting it back to the database I need it in and it still will not open in the design view. Opening the database1.accdb that the steps above did not open to the form I use when opening this database. Did I do something wrong?

[HansV]

Does that form remain open in the background, or hidden? That might explain why you cannot open the table in design view.

[hlewton]

How can I tell if this is happening?

[hlewton]

Hans That was it. I figured out what was happening after your last post. Thanks a lot.

[hlewton]

Since you have already done the things I was going to suggest, we'll continue to the next step:
- Create a blank new database.
- Import all database objects (tables, queries, forms, reports, macros and modules) from the 'problem' database into the new one.

When doing this, and I think I did it correctly, I noticed it saved the file with an .accdb extension. I played around with it trying to save it as a .mdb or .mdbx database but it didn't seem to offer me those options within Access. I did try renaming it to a filename.mdb and that seemed to work. Is there a better way I could have saved it in one of the two formats I mentioned other than having to rename the file?

Also while experimenting with this file and then one of my original databases I noticed that exporting a table, and probably other items as well, is quite different from what I am used to in Access 2003. It brought up a wizard. I did play around and export the table to both an existing database and also created an Excel workbook from exporting the table. So I am not asking how to export. I think I have that figured out. However, in both instances I did not choose to have Access's wizard remember the steps because I am not at all sure what that means. If it remembered the steps I took to export the table to another database would it remember that database or only the steps I took to get it to export to a database and then allow me to choose the database like I did in Access 2003?

[HansV]

Changing the extension from .accdb to .mdb may work for Access itself, but it will probably cause problems with other applications.
The option to save as .mdb can be found under File > Save & Publish:

S0667.png

A saved export can be re-run by clicking on Saved Exports in the Export group of the External Data tab. Select a previously saved export and click Run.
The saved export 'remembers' the destination.

S0668.png

If you click Excel, Access, Text file or similar in the Export group, you can specify the destination file.

[hlewton]

Thanks. I probably never would have found the way to save that file as a .mdb without your help.

[hlewton]

Since this still seems to be a design problem I hope it is OK to just continue this thread.

In the same database discussed above I have a report based on a query. When I look at the query in the datasheet view it is indexed or sorted correctly as far as alphabetically by the last name field I have in the table, which is what I want. When I look at it in the report view, which is based on the query I just mentioned, the first few seem to be listed alphabetically then all goes wrong and I have no idea what it is sorting on. I created this long ago and don't remember exactly what to check for to make it work as I want it to. Is there something in the properties or design of either the report of the query I should be looking into?

[HansV]

Reports ignore the sort order of their Record Source. You must specify the sort order in the report itself:
- Open the report in design view. This automatically activates the Design tab of the ribbon.
- Make sure that the 'Group & Sort' button in the 'Grouping & Totals' group of the ribbon is highlighted:

S0673.png

- This shows the 'Group, Sort and Total' pane at the bottom of the window:

S0674.png

- Click 'Add a sort'.
- Select the field you want to sort on:

S0675.png

- If necessary, click 'Add a sort' again to specify more fields to sort on.

[hlewton]

OK that worked but not exactly how I want it to. In the report the field that is printed out is not the same as the one I want the sort to be based on and the field I want it sorted on is not part of the printout of the report. The field I want it sorted on is, however, part of both the query and the table that the query is based on but I did not see any option to have it sort on that in the method above. Is what I want to do even possible?

Thanks

[HansV]

If the field that you want to sort on is included in the Record Source of the report, you should be able to select it in 'Add a sort', even if the field is not displayed on the report.

[hlewton]

If the field that you want to sort on is included in the Record Source of the report, you should be able to select it in 'Add a sort', even if the field is not displayed on the report.

It is definitely in the Record Source, which is the Query I mentioned, but even in the query it is not displayed and it for some reason is not an option to choose in "Add a sort" Not sure why that would be since reading your answer. I'll try displaying it in the Query and see if that helps.

[hlewton]

That seemed to work so apparently the field has to be displayed in the Record Source or Query, in this case, for some reason. I wonder how that would have worked had the record source been the table itself. OH well it is alphabetizing fine right now. Thanks for your help.

[HansV]

Only fields that have their Show check box ticked in the design of the query will be part of the record source of the report. Fields whose Show check box is cleared don't count, and hence they won't be listed when you click 'Add a sort'.
So displaying the field in the query is the correct solution.

[hlewton]

Thanks Hans but I have to tell you it worked, which you already know, but not without a little more confusion, at least on my part.

I copied the original file to the root of C: to make the changes and test them just in case it didn't work I would still have the original file. Well after it worked I just copied that corrected file into the Documents folder and chose replace. When I then opened it from the pinned application it still was not working as just changed. I actually had to unpin the file, delete it from the Documents folder, and then copy it again from the C: drive's root directory into the Documents before it worked. I then repined it but I have no idea why all these, what I would think are extra steps, had to be taken. Actually I am not at all certain the unpinning and repining were necessary but I did them along with deleting the original file just in case.

[HansV]

We'll probably never know...