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.

Sponsored Links:

Related Topics

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).


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?!


report filter not working in Access 2010 SP1
Windows auto installed Office 2010 SP 1 and now my Access 2010 reports no longer work. It is a Access 2003 mdb file running in Access 2010. It was working fine till SP 1 was installed.

DoCmd.OpenReport strReportName, acViewPreview, , strSQL

the report opens fine, but no longer filters like it used to. (In my case I am filtering dates, using the # characters.)


linked table refresh in 2010 not working in 2000
for any ideas . I have an access db that links to a Pervasive database. I administer the database in 2010 version but one of my user is still on 2000 (he wont change so thats not an option). I had to change the ODBC for a small group of tables. The links are working on my end but not his - he gets an ODBC error


linked table refresh in 2010 not working in 2000
I have an access db that links to a Pervasive database. I administer the database in 2010 version but one of my user is still on 2000 (he wont change so that's not an option).

I had to change the ODBC for a small group of tables. The links are working on my end but not his - he gets an ODBC error.


Access 2010 adp bugs
I had a working adp that started life in Access 2003, was successfully upgraded to Access 2007, and has now been "upgraded" to Access 2010. A number of issues have arisen, all of which seem like Access 2010 bugs to me.

The Access 2007 version and the Access 2010 version are both connecting to the same SQL Server 2008 database. The issues only exist in Access 2010.

* A form which used Me.OrderBy to sort the recordsource crashed Access 2010 after working fine in Access 2003 and 2007.
* Settings for a form's InputParameters which worked fine in Access 2003 and 2007 now crashed Access 2010.
* Access 2010 incorrectly decided that a foreign key id field in a form "cannot be updated because it is an autonumber". In this particular case, the problem does NOT occur if the record source is "Select P.* From dbo.Payment P", which is the table containing an actorID foreign key.


Access 2010 stopped working
I'm running Access 2010 on Win7 x64. I use it very rarely, but it has worked in the past. "Microsoft Access has stopped working" and given the option to close the program or search online for a solution (which yields nothing).

Failing that, is it possible touninstall/reinstall just Access? It's part of my Office 2010 Pro installation and doesn't show up by itself in the uninstall programs dialog.


Not able to build an ADP in MS Access 2010
I have a ADP project in MS Access 2003,I have migrated this application to MS Access 2010. After doing few code changes it is working fine in MS Access 2010, but we are facing issue in building the MS Access 2010 ADP.

I am able to import the Forms,Modules and On selecting the run macros to Initialise the Database in MS Access 2010(while building ADP),it is giving runtime error'29060'"file not found".


Office 2010 ODBC linking question
Just got Ofc2010 & Windows7 machine. Had to setup ODBC connections to an Oracle DB again with a later version of Oracle. Had to re-LINK all my Access tables to the Oracle DB tables. All going well so far, but my queries using the linked tables are still not working.


Access 2010 User Levels
However I have managed to build a database for a small business I am working for. The issue is once I complete the database there will be 4 people accessing it on a daily basis but Only 2 of us will be able to access of the tables.

How can I create a login or security to only allow certain users to see certain parts of the database? I have read afew tutorials on this, but there does not seem to be too much information with Access 2010 unless it's providing read only and admin edit options.


Help access 2010 to 2003 vba and sql problem!
And a sql statement in the criteria part of my query.

Everything was working fine in access 2007 and 2010, when I converted it in access 2007 to access 2003 format when I open my form it keeps asking for a parameter, which is point to the textbox that the query criteria is based on.

I am supposed to implement this on our computers at work tomorrow, but I've got to get it working in access 2003 format.

The 2007/2010 and the 2003 access files. A professional could probably look over it in 5-10 min and be able to tell me what is wrong.


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


Access 2003 to 2010
We are moving to Office 2010 and have found that some of our Access databases do not work as they used to. This particular database has a form, which is used for data entry, and has two subforms as part of the data entry form.

Each part of the form is entered into a different table. There is an auto number field to tie the three tables together. One of the tables links to the main form in a one-to-many format for transactions.

The other table is linked one-to-one. It is the one-to-one subform that is not working in 2010.

During data entry the information can be entered in the main form and the subforms with no problems. The problem is that the subform does not display the information after moving to another record.

The data is in the tables correctly. but the form does not display the information from the one-to-one table.


Object descriptions - Access 2010
I'm working on a large documentation project. I inherited several large Access db's (created in 2007, upscaled to 2010 prior to my inheriting them) and I need to print out each object and its associated description. I thought I could query one of the system tables to obtain this but I don't see this info stored there. Was wondering if there was a way to do this with VBA? Did several searches here on this site (as well as a few others and Google) but did not see anything of note.


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


DLL msain.dll missing. Installed Access 2010 and Office Home and student 2010.
Have done repair of Access 2010 and uninstall and reinstall still error. Office home and student 2010 is working fine.


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.


Access 2010 error when referencing sub report in open event
I have been working on migration of large ADP project from Access XP to Access 2010 and have been faced with many problems.

One of the problem is that there is thrown error when referencing sub report in its Open event.

I have a report called Main and it contains subreport control called sbrLine. In sbrLine sub report control I have placed report Line.

Open event of Line report contains following code:

MsgBox (Me.Parent.sbrLine.Report.Name)

It was working in Access XP, but it fails with error in Access 2010.

The error is #2455 "You entered an expression that has an invalid reference to the property Form/Report."

Is this a bug in Access 2010?


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.


Merging 2 tables together
and I have a bit problem

I have 2 tables with products. One is products actualy produced and second is products planned in budget. 80% of products are same, but there can be products which have not been produced yet, or which have been produced but not planned in budget.

Now I need to put these 2 tables together, best would be query, because table with actuals is updated every month, so I can compare budget with actuals

I use access 2010

I have been working with it like 2-3 weeks, so I need very simple solution. I have been programming in VBA in Excel, but not yet in access