MS Access 2010 project

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
dory550Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vastoCommented:
You don't have "select" permissions
0
dory550Author Commented:
Vasto
Thank you for your input
User in question (plesksa) has sysadmin server role
Dory
0
ggzfabCommented:
I've experienced the same for tables without a unique index.
Just adding a unique index to the table did fix this.
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Anthony PerkinsCommented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dory550Author Commented:
Thank u guys
Dory
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
ggzfabCommented:
A PK is an unique index....
0
Anthony PerkinsCommented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
ggzfabCommented:
@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
Anthony PerkinsCommented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
ggzfabCommented:
@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
Anthony PerkinsCommented:
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
ggzfabCommented:
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
Anthony PerkinsCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.