Ms Access to SQL Server passthrough query

I have a strange situation.  I created and access app that connects to an SQL server via recordsets and passthrough queries.

it works fine on two pcs within the organisation with no problems at all, but on the other pcs it only updates the SQL tables via the recordsets but if i try to run a passthrough query or exectue a store procedure via the recordset it fails.

i am using the same sql ID and password with all PCs (the two that work and the others that fail)

I am thinking this is a permission issue but I do know what permissions to grant on the server given the sql ID works fine with 2 pcs.

can someone shed some light on this?
RDLFCAsked:
Who is Participating?
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.

 
RDLFCAuthor Commented:
ok this issue has bother me the entire day and I found what may be a potential solution but will not be able to try i until tomorrow but I am putting it out there to see if anyone came across it before.

I did not create a key field in the table that is not be updated by the stored procedure and I read that an ODBC connection will not be able to append if no key field is present.  Can this be the case?

Another factor is that the two computer that work are the only ones with MS Server Management Studio installed on them.  

I will update this question when I test what I hope is the solution tomorrow.
0
 
Simon BallCommented:
Sounds like an ODBC settings error?

The passthrough uses an odbc connection "dsn" stored on the PC... could be the odbc drivers are updated when sql server management studio is installed on the pc...to a newer version, which is required for the update via passthrough to work?

also check the settings in the ODBC DSN's for the server carefully on all the pc's.
control panel - admin tools odbc data sources...

Could be the underlying version of SQL which is selected first when you make a new DSN...or settings deeper in.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
In general, Access will require you to provide the necessary Primary Key fields in your SQL in order for the query to be updateable. With a query using multiple tables, that can be tricky, since you have to be very careful which fields are included, and which are not.

Note that sometimes you must also create a TimeStamp or RowNumber field in the table in order for Access to work with it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Add the Timestamp field to the underlying table(s) if you can.  Access (JET) will look for that first as a unique key to drive it's cursor.

A "Timestamp" field is not what the name implies; what it gives you is row versioning on a table in SQL, which is perfect for JET to use as a key.

And yes, it's critcal that JET have a unique key to work with.   Even if local view/queries, if a row cannot be identified in an underlying table, a recordset won't be updateable.

 What most aren't aware of however is that you can actually assign an index on the client side (in the Access database) if your in a position where you cannot modify the SQL database, and JET will use that even though the table is linked.

 The other thing that may be in play is permissions as your thinking; what type of SQL security are you using?   Windows or SQL Server?  or mixed?

 The other possible issue is the connection; are you using a DSN, and if so, what driver?   SQL Server or the SQL Native driver?

Jim.
0
 
PatHartmanCommented:
Here's an example of DDL to add a pseudo index to a linked view or table that doesn't have a unique index/primary key defined.

CREATE UNIQUE  INDEX PrimaryKey
ON FP_plans (pid asc, stop asc, start asc, dc asc, prov asc, svc asc, order asc, vfpid asc)

Keep in mind that if you use this technique, you must include as many columns as it takes to ensure uniqueness so if you are looking at a view, you need to include the PK from ALL tables in the join.  Although I have no direct experience with this, I am told that if your pseudo index is not unique, you could corrupt the SQL Server table by trying to update it.  I don't want to find out if that is true so I am very careful.

Also, whenever a BE table changes, in addition to refreshing the link to the table,  you must also relink your pass through queries and views that use the changed table.  And then you'll need to rerun the DDL to create the pseudo indexes.
0
 
Jim P.Commented:
And the other question is what is the OS of the clients? And if Win 7 are any 64 bit or are they all 32 bit?
0
 
Simon BallCommented:
Good call Jim P.  ODBC SqlServer Settings and ODBC 32/64 bit...
0

Experts Exchange Solution brought to you by ConnectWise

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
 
RDLFCAuthor Commented:
I had to abandon my strategy and do a work around
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.