Access 2010 Modifying Tables

After working with your database, you might find that you need to make some changes to the tables that store your data. Access makes it easy to modify your tables to suit your database's needs.
In this lesson, you'll learn how to create and rearrange table fields. You'll also learn how to ensure that your table data is correctly and consistently formatted by setting validation rules, character limits, and data types in your fields. Finally, we will direct you to additional options for doing simple math functions within your tables.

Sponsored Links:

Related Topics

How to add fields, tables and forms
The Microsoft Office Access 2007 relational database manager enables information workers to quickly track and report information with ease thanks to its interactive design capabilities that do not require deep database knowledge. In this Microsoft Access video tutorial, you'll learn how to add and modifying fields to a pre-existing table, and applying these changes to an associated form. To get started adding and modifying fields, tables and forms in Access, take a look!

Create user logon in 2010 and store that value on data entry form
Is there a way in Access 2010 to have users log on to the database? I also need their log on name to display and store when they are adding or modifying records on a form

Access 2010 Query Modifying the Is Null Query
I have an access 2010 query that has the following headings init. I hope you can see the picture if not then the headings are;

Call Back Date - Stage - Contract End Date - Days Till Out of Contract. It also has a few other headings but these are the main headings.

I am trying to merge an Is Null with a

Linking Access 97 tables in Access 2010
was hoping somebody could help me.

I hope this has not been answered somewhere before but I could not find it.

Our office is stuck using access 97. For obvious reasons I would like to follow the rest of the world into this century and move to 2010.

Problem is that over the years many people have created many MDBs and not of them them knew what they were doing! It is all a bit messy.

If we start converting databases we will inevitably end up with 2010 front ends linked to 97 tables. I know it is possible to do this and I have done some testing and everything looks okay but does anybody know if this will cause any unexpected side effects? Anything like supported data types across the different versions or anything like that

Question on removing permissions from tables after 97-->2010 conversion
Our organization has several 97 access databases scattered throughout and are getting updated to 2010. The update has gone fairly well, however, as we go through these individual databases, the users ask that we provide them with changes that have not been implemented since the DBs were new.

The business does not have a full time DBA, and has contracted work like that in the past, but I would prefer not to call on such a person for what appears to be simple changes (adding records to tables, etc.).

and I foolishlymentioned that I have some experience with Access, so I have been tasked with all Access problems.

Herein lies the problem: Currently I am working on converting a table and find that the DBA who built these DBs not only hid the nav pane, but apparently has locked the tables for editing as well.

I can get the tables to show with F11 fine, but I need to get around those permissions set on the tables. I am unsure exactly how to go about it with 2010 not having permissions (from what I understand).

Access 2010 Working with Tables
While there are four types of database objects in Access 2010, tables are arguably the most important. Even when you're using forms, queries, and reports, you're still working with tables, since that's where all your data is stored. Tables are at the heart of any database, so it's important to understand how to use them.
In this lesson, you will learn how to open tables, create and edit records, and modify the appearance of your table to make it easier to view and work with.

Report Of Modified, Deleted, And New Records Over The Past Month, Access 2010
I am using Access 2010 on Windows 7, although some users of the DB will be using Windows XP.

I want to make a monthly listing added, deleted and modified records over the past month. We have a database of urban poor community locations (in one table) and various other tables of demographic data collected at those locations (such as eviction information, eviction threat information, relocation site survey information).

We have staff who regularly visit these urban poor communities and will be updating the database throughout each month, such as by adding new records about eviction threats, or modifying existing records based on new information fromcommunity members.

Occasionally, records will be deleted from some tables, for example when a community is evicted (in this case a record will be removed from the urban poor community profiles table and another added to the evictions table).

Access 2007 vs. Access 2010
My computers were recently upgraded from Windows XP and Office 2007 to Windows 7 and Office 2010. I am responsible for three Access databases that are used by some of our affiliates. I recently made changes to those files in Access 2010, and now our affiliates are receiving error messages when they use the files.

The files will not compact - they receive an error message that they do not have the correct version of Access to do that.

I had Access 2007 installed on one of my computers and receive the same error when I try to compact the databases. Thechanges I made included modifying queries, relinking a table from a SQL Server database, and changing a couple of macros.

They also receive a warning that the files are incompatible.

I made new files using Office 2007, imported the objects from the files I'd worked on in 2010, and asked one of our affiliates to test the file. Same issues except he did not receive the compatibility warning.

Date() anomaly
I have a very strange problem that has completely confused me.

Having used MANY tables dating back to 2003, etc in Access 2007 with NO problems with the 'Date()' function I am experiencing multiple failures in Access 2010.

When I first opened these Tables in 2010 the error message 'Unknown function 'Date' in validation expression or default value on tblXXXX' was shown and the Default Value of today's date did not work.

I have now had to disable the Deafult Value of Date() in these tables.

Interestingly, NOT all of my tables are affected but the majority alas are.

There were NO changes made to the tables, the original WORKING databases were simply opened in 2010 after an upgrade.

Why do I always seem to get such niggles / issues after upgrades?!

Access 2010 database 1 of 12 tables locked after converting to Access 2010
Unable to add records or edit one table of an Access database with 12 tables after converting to Access 2010

The Table in question has 24670 records and is in the centre of the structure. It records the base data by reference to a date and uses the other tables for the common data.

All other functions of the database work providing reports,

It is as if this one table has been locked in some way.

The database had no problems under Access 2007 and new records could be added to the table in question

Import & Link HTML Datasheet In Access 2010
Access 2010 lets user import web datasheet which could be very useful, especially when you are dealing with data tables saved in HTML format. Through importing them to Access 2010 database you can easily start defining data types and apply constraints. Apart from importing data table, you can also link web datasheet with Access 2010 database table which enables smooth synchronization between data tables.

Upgrading from 2000 to 2010
Is there a good reference to explain some of the differences in the 2010 version of MS Access? I have a database in 2000 that has ODBC calls to download multiple tables, execute a query, and output the data to a new table but each time I run it in 2010, Access crashes. I have gone through the save and publish steps but it still does not work.

Import Text File In Access 2010
Access 2010 include a feature of importing raw text data in to tables, this could be very helpful when you are just writing down numbers, text, etc in text editors because they are easy and simple to use. This post will elaborate how to import and organize Text document in to Access 2010.

Access 2010 function
Does anybody have an option or workaround for the "GoTo Field box" function that was in Access XP/2003 and dropped in 2007/2010? I work for a large international consulting firm and not having this ability in Access 2010 is making our migration efforts even more difficult. We work with some very large Access tables with many fields per table

Access 2010 Multi User Databse Set up
What would be the steps to follow to take an existing Access 2010 accdb and split out the data tables from the forms, reports, queries, etc.?

Access 2K to Access 2010 conversion
But I would just like to ask something about conversion of MS Access 2000 to MS Access 2010.

I don't really use MS Access for programming but just for the backend as my database for front end apps. but currently, I have this project in converting an app done in Access2K and needs to be converted to Access 2010.

How do you go about it in conversion?

This app uses MS SQL 2000 as its database. Only the forms are built in MS Access. tables and queries are linked to MS SQL.

What is needed is to see if there would be a real difference in speed. Is there a tool that I could use or do I really have to rebuilt every form?

Killing ImportError tables in Access 2010 .mde
Mde db version installed on their local drives that is linked to tables in a backend .mdb database on the network. They import data to the tables from Excel files.

If they try to import the data twice, it generate a $TB_ImportError table in their local .mde db. Pretty soon, when they're trying to import, the search for the proper table toimport to in the wizard drop-down list is almost unreachable below all the $TB_ImportError tables that have been created.
In older versions of Access I could hit F11 on their local versions and see the .mde table listing and delete these error tables. Is there ANY way to do this in Access 2010 without writingcode and re-compiling and distributing the front end?

Work with a database you didn't create
Learn how to work with a Microsoft Access database you didn't create by determining its structure, adding and viewing data, and modifying objects in the database.
After completing this course you will be able to:
1) Determine the contents and structure of a database you didn't create.
2) Get started with using the database by entering and viewing data.
3) Customize a database you didn't create by adding and modifying data and objects in tables, forms, queries, and reports.
(Microsoft Office Access 2003)

Unhide tables in back end....(Access 2010)
I am trying to unhide my tables (in my Access 2010 database back end).

In the Navigation Options, when I click Show Hidden Objects I do see my hidden tables in a light grey, but they are still "hidden" in essence (when I look at Table Property it is checked.but will not let me uncheck".

Note: the table names of the hidden tables are greyed out in the navigation pane

Break link between Access 2010 table and a linked list in SharePoint 2010
Does anyone know how to "unlink" a table after linking it to a SharePoint list in MS Access 2010? I created an Access 2010 database and then linked the tables in it to a SharePoint site using the SharePoint button under Database Tools on the menu ribbon.

All that worked perfectly. However, now I need to unlink the tables without deleting them.

When I use the More Options > Delete List menu option on the right-click menu of one of the linked tables, a message appears that says "You are about to delete the list and all its data from the SharePoint Site.

Do you want to continue?". That sounds like what I want it to do (remove the list from SharePoint but keep the table in Access). But when I proceed,not only does it remove the list, it also deletes the table in MS Access!

Is there no way to break the link between an Access Table and a SharePoint list once established?