?
Solved

MS Access 2010 project

Posted on 2012-04-13
17
Medium Priority
?
682 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
[X]
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
  • 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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 85
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 85
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 85
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

752 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