Solved

MS Access 2010 project

Posted on 2012-04-13
17
663 Views
Last Modified: 2012-04-23
Hi Guys
I am trying to use Access 2010 project db as a front-end to MsSql 2008-r2 that is located on a dedicated server which we use for our website.
I am having a problem.
I am connecting with the Sql server and the tables pop up on my access 2010 screen.
But when I click to open any table, just column-heads are being displayed...no records.  (see attached).
this behavior is repeated with all the database on this remote server.
Does anyone know why this is happening?
Thanks
Dory
AccessProject.pdf
0
Comment
Question by:dory550
  • 5
  • 5
  • 4
  • +2
17 Comments
 
LVL 18

Expert Comment

by:vasto
ID: 37844956
You don't have "select" permissions
0
 

Author Comment

by:dory550
ID: 37845646
Vasto
Thank you for your input
User in question (plesksa) has sysadmin server role
Dory
0
 
LVL 5

Expert Comment

by:ggzfab
ID: 37847081
I've experienced the same for tables without a unique index.
Just adding a unique index to the table did fix this.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 37847363
Just adding a unique index to the table did fix this.
Actually, I suspect it is a tad more specific than that and requires a Primary Key on all tables linked.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 37850253
IIRC, the lack of a PK index would just trigger errors when the user tries to edit or add data. They would still be able to see the data.

If the suggestions from others don't work, then try this:

What happens if you move the db to another machine and try it there? Does the same behavior occur?

What happens if you build a new database, and link to the same server? Does the same behavior occur?

Be sure that you've fully updated Office/Access, as well as Windows.
0
 

Author Comment

by:dory550
ID: 37877229
Thank u guys
Dory
0
 
LVL 84
ID: 37877509
dory550:

Thanks for the points, but what was the actual solution here? Did you find tha that adding a Primary Key value was the solution, or was there something else?

Scott
0
 
LVL 5

Expert Comment

by:ggzfab
ID: 37877640
A PK is an unique index....
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37878863
A PK is an unique index....
No, by definition in SQL a Primary Key is a CONSTRAINT and not an INDEX.  

I suspect you are confusing the fact that SQL Server implementation makes use of a unique index in order to enforce uniqueness in a Primary Key.

This is from SQL Server's BOL:
A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. You can create a primary key by defining a PRIMARY KEY constraint when you create or modify a table.

A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints guarantee unique data, they are frequently defined on an identity column.

When you specify a PRIMARY KEY constraint for a table, the Database Engine enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries.


But perhaps you were trying to make a point that I am missing...
0
 
LVL 84
ID: 37879098
My point was that I'm not sure how my comment contributed to the solution because the solution is not clearly defined.

I would assume the answer was to add a Primary Key constraint to the table, but that's not clear here - and my comment was not really relevant to the Primary Key "solution", if that was indeed the solution.
0
 
LVL 5

Expert Comment

by:ggzfab
ID: 37879489
@acperkins

Your BOL describes:
"When you specify a PRIMARY KEY constraint for a table, the Database Engine enforces data uniqueness by creating a unique index"

So what's wrong with the shorter statement that a PK is a unique index.

It's just strange that the internal unique database key of the row isn't used by Access / MS SQL to allow updating any table.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37882637
So what's wrong with the shorter statement that a PK is a unique index.
Because logically it is not correct.  You are describing a particular technical implemention of a CONSTRAINT.

But if your point was that a Primary Key uses a unique index, then that is true.  However it is much more than that.  Unlike a Unique index, a Primary Key does not support NULLs and there can only be one.
0
 
LVL 84
ID: 37882713
It's just strange that the internal unique database key of the row isn't used by Access / MS SQL to allow updating any table.
There has long been a disconnect between SQL Primary Keys and Access mapping them correctly. In most cases it'll do it, but I've seen plenty of times where a properly formed, properly linked SQL table will "lose" the Primary Key field. In cases like this you can either recreate it from code (on the linked table, mind you) or just delete the link and re-build it, being sure to properly build the PK during that rebuild.
0
 
LVL 5

Expert Comment

by:ggzfab
ID: 37883080
@acperkins
Just tested a table with a unique index being no Primary key and an Access .adp will allow inserts/updates.
So your assumption in the marked answer comment "Actually, I suspect it is a tad more specific than that and requires a Primary Key on all tables linked." proves to be wrong on my MS SQL 2005 database table.
Feel free to test it yourself.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37883577
proves to be wrong on my MS SQL 2005 database table.
Fair enough.  

Now will you agree that a Primary Key is a CONSTRAINT and not an INDEX?
0
 
LVL 5

Expert Comment

by:ggzfab
ID: 37883747
Never discussed that, for me a primary key is technically a (type of/realised with) unique index and the other way around isn't true by default :-)

That MS SQL uses a CONSTRAINT to define a PK is just SQL syntax for me logically seen there are more constraints on a table like a valuelist for a tablefield or a period range for a datefield or even the fact that a datatype dictates only numbers.
http://www.w3schools.com/sql/sql_constraints.asp

I did use a PK when solving my problem with this issue, but when posting I assumed that just a unique index would be enough for Access to identify a row, thus my "unique index" comment. Glad I'm now sure, did learn something from this :-)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37883938
Never discussed that
Huh?

for me a primary key is technically a (type of/realised with) unique index
Sorry, but I am afraid you are wrong.

That MS SQL uses a CONSTRAINT to define a PK is just SQL syntax
Let me repeat it once again as I think you are missing the point: In SQL (not only T-SQL, but PL/SQL and other SQL dialects, as well) by definition a Primary Key is a Table CONSTRAINT not an INDEX.  In the specific case of SQL Server it enforces a Primary Key CONSTRAINT with a unique index.
Please take the time to read the SQL-92 reference (in case you are not aware most SQL dialects derive from this version):
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

In any case, I guess we will have to agree to disagree.

Good luck.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now