Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Ms Access to SQL Server passthrough query

Posted on 2014-01-15
8
Medium Priority
?
1,166 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 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 40

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 750 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 750 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

916 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