Solved

Ms Access to SQL Server passthrough query

Posted on 2014-01-15
8
1,135 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
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 84
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
 
LVL 57
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 34

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Export  Access Query To Excell 16 43
Listbox not remembering select items when come back to the record 5 27
Track name AutoCorrect info 14 46
access 7 0
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

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

16 Experts available now in Live!

Get 1:1 Help Now