Access 2010 - Compact and Repair

[hlewton]

Access.jpg

How do I compact and repair an Access database in Access 2010? I have searched the NET and it says to click in the Menus which I don’t have nor can I find a way to activate it. Also as the attachment shows there is “Compact and repair database” button to press when I am on the “Database Tools Tab” but when I press that button expecting to see a menu offering me the option of compaction and repairing it disappears and goes to the Home Tab. From all I have seen in tutorials this should have offered me the database utilities but it isn’t. So how can I get the database utilities active?

Thanks

[HansV]

The 'Compact and Repair Database' button on the Database Tools tab of the ribbon should directly compact the database, without displaying any menu or dialog.

[hlewton]

Boy that is interesting. Thanks for that.

Let me ask this. I have tried following these instructions:

"To convert an Access 2000 or Access 2002 - 2003 database (.mdb) to the .accdb file format, you must first open the database by using Access 2007, Access 2010, Access 2013, or Access 2016, and then save it in the .accdb file format.

Click File, and then click Open.

Click the Access 2000 or Access 2002 - 2003 database (.mdb) that you want to convert.

Note: If the Database Enhancement dialog box appears, the database is using a file format that is earlier than Access 2000. To continue, see the section Convert an Access 97 database to the .accdb format.

Click File, click Save As, and then click Save Database As.

Choose the Access file type, and then click Save As.

If any database objects are open when you click Save As, Access prompts you to close them prior to creating the copy. Click Yes to make Access close the objects, or click No to cancel the entire process. If needed, Access will also prompt you to save any changes.

In the Save As dialog box, type a file name in the File name box, and then click Save.

Access creates the copy of the database, and then opens the copy. Access automatically closes the original database."

To try to convert this database to an .accdb format but after I use the "Save database as" option the only file format I am offered is the .mdb one. How do I get it to offer me the .accdb format?

[HansV]

What happens if you select File > Save & Publish?

[hlewton]

That worked. I wonder why the NET did not tell me about that. I was even at Microsoft's site. Actually what I posted above was from there. Thank you very much.

[hlewton]

I guess I have to ask another thing. I have other databases that I was planning on converting to the .accdb format but one of them is used for mailing merges where I create mailing labels using queries inside the Access database. Will these merges still work with the new file extension?

[HansV]

Yes, the mail merge should still work, but if you have existing mail merge documents, you will have to specify the data source (the "recipients") again to point to the .accdb database instead of to the .mdb database.

[hlewton]

Sorry but I need some clarification. I have saved Word documents that I open and use to create my mailing labels document then use the newly created document as the one to print out thus keeping the mail merge document itself the same. These mail merge documents open with this:

MailMerge.jpg

The only options I see are the "Yes," "No," and "Help." How or where would I point this merge to the new .accdb database or would I need to the way I use the merges?

[hlewton]

I think I found it. By saying no to the dialog box I attached I believe I can then use mailing within Word to make the changes. I guess with the new .accdb extension I will have to make that association change with all my mail merges. True?

[HansV]

Yes, you'll have to do that for all existing mail merge documents that use a .mdb as data source.

[hlewton]

Thanks Hans for all your help.

[hlewton]

Well it seems I have come across another question. I have saved many table exports in an .mdb file to an .accdb file. Can I export tables from an .accdb file to a .mdb file?

[HansV]

Yes.
Select a table in the navigation pane.
On the External Data tab of the ribbon, in the Export group, click Access.
Browse to the target database and click Save, then click OK.
Access will display a dialog to let you specify how you want to export the table.
Click OK.
If your table contains features that aren't available in a .mdb database, such as multivalue, rich text or attachment fields, or data macros, you'll get a warning.

[hlewton]

Great. That seems the same as the saved exports I created going from .mdb to .accdb. I was hoping that was the case but I was a bit apprehensive not wanting to mess anything up. Thanks