Solved

Ms Access to SQL Server passthrough query

Posted on 2014-01-15
8
1,158 Views
Last Modified: 2014-02-05
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?
0
Comment
Question by:RDLFC
[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
8 Comments
 

Author Comment

by:RDLFC
ID: 39783640
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
 
LVL 15

Expert Comment

by:Simon Ball
ID: 39784724
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
 
LVL 85
ID: 39785064
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 58
ID: 39785186
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
 
LVL 38

Expert Comment

by:PatHartman
ID: 39785373
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
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 250 total points
ID: 39785809
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
 
LVL 15

Accepted Solution

by:
Simon Ball earned 250 total points
ID: 39788105
Good call Jim P.  ODBC SqlServer Settings and ODBC 32/64 bit...
0
 

Author Closing Comment

by:RDLFC
ID: 39835649
I had to abandon my strategy and do a work around
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

626 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