Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MS Access 2010 project

Posted on 2012-04-13
17
673 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

856 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