Solved

Ms Access to SQL Server passthrough query

Posted on 2014-01-15
8
1,130 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

707 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

18 Experts available now in Live!

Get 1:1 Help Now