Solved

MS Access 2010 project

Posted on 2012-04-13
17
665 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
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…

932 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

9 Experts available now in Live!

Get 1:1 Help Now