Solved

dsn-less linked tables write issue

Posted on 2014-10-01
17
182 Views
Last Modified: 2014-10-12
Access 2010 database with linked tables to a SQL Server 2005 DB.
Created a System ODBC connection to linkg 3 tables.
Using a DSN-less vba sub routine to link the tables at startup without needing the ODBC System DSN (Code to the Routine can be found here http://www.accessmvp.com/djsteele/DSNLessLinks.html)
Everything was working fine for about 2 weeks.
Now when I try and save a record it states there is a Write Conflict (which I know is not true).
When I try to go straight to the tables or the query it says "Connection failed" User is not associated with a trusted connection.
Since I am providing the userid and password in the Sub Routine, why am I now having this problem.
Any ideas?
0
Comment
Question by:GenesisTech
[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
  • 5
  • 3
  • +1
17 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 40355751
What is the DSN-less connection string you are constructing?  Please post example but be sure to change/obscure the server name, user name, password.
OM Gang
0
 

Author Comment

by:GenesisTech
ID: 40355756
Did you go to the link posted above? That has the code I am using to make the connection.
0
 
LVL 28

Expert Comment

by:omgang
ID: 40355766
The link describes a connection string for a MySQL db.  I'd like you to post exactly what you're using for the connection string to the MS SQL Server 2005 db.

OM Gang
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:GenesisTech
ID: 40355768
anyway, this is the how the string is created in the code....

strConnectionString = "ODBC;DRIVER={sql server};" & _
        "DATABASE=" & DatabaseName & ";" & _
        "SERVER=" & ServerName & ";" & _
        "UID=" & UID & ";" & _
        "PWD=" & PWD & ";"
0
 

Author Comment

by:GenesisTech
ID: 40355772
omgang,

You needed to go furthe rdown the page. The grey box at the very top says "if you are using mysql change the code to this". If you scroll down further you will see the entire sub routing that I am using.

THANK YOU FOR YOUR HELP ON THIS!
0
 
LVL 28

Expert Comment

by:omgang
ID: 40355773
As an example, here's a connection string I use in an Access 2010 app to connect to a SQL Server 2012 db

ODBC;DRIVER=SQL Server;SERVER=SeverName\ZECHARIAH;DATABASE=TheDatabase;Trusted_Connection=Yes

OM Gang
0
 
LVL 28

Expert Comment

by:omgang
ID: 40355779
With your connection string your not using a Trusted Connection but, instead passing credentials for SQL Server authentication.  If it worked previously but isn't now I'd suspect the something in the Server or Database got changed.

OM Gang
0
 

Author Comment

by:GenesisTech
ID: 40355804
Hmmmm. Nothing has changed on the server or the database.

Interesting set of additional information however. I removed the linked tables and added them back using the System DSN and I still am getting a write conflict.

So, it has nothing to do with the code for the dsn-less connection. There is something else going on.

I still am not sure where to look though.
0
 
LVL 28

Expert Comment

by:omgang
ID: 40355839
Do you have access to the server?  You need to make sure the Login for the specified user has not become disabled, password expired, etc.
OM Gang
0
 

Author Comment

by:GenesisTech
ID: 40355959
Yes I have access to the server. It is my server and I am the admin and the sql dba.

The login that I am using is NOT disabled.
0
 
LVL 85
ID: 40355963
<no points wanted>

I'd agree with OM Gang - something on the server has changed. If you can. open SQL Server Management Studio and see if you can connect to that server using the same credentials, and then see if you can open the database. If so, try a SELECT query to determine if you have read permissions, then try to make a minor change to one of the records. This will help you to determine if your login has sufficient permissions to login and work on the server.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40356377
Try with:

DRIVER=SQL SERVER;

That works here.

/gustav
0
 

Author Comment

by:GenesisTech
ID: 40366228
Update on the situation.
The issue is NOT with the DSN-Less connection.
After some further troubleshooting I have discovered the following:
1) The connection is still valid and functional.
2) I can open the linked table and make changes to it.
3) When I open the query (behind the form) I CANNOT make changes to it. It ALWAYS states "Write Conflict" "The record has been changed by another user....."
4) This query used to allow me to make updates and now does not - any ideas on how to fix it or where I go from here?

Thanks!
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40366237
It sounds like you will have to modify the query ...

/gustav
0
 

Accepted Solution

by:
GenesisTech earned 0 total points
ID: 40366612
Interesting....

I have discovered that if the underlying query has sorting set on 1 of the fields, it will not let me make changes via the form.

Once I removed the sort option, changes began working again.

Odd.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40367694
Well, that was what I suggested, though not in detail, as we didn't have the query.

/gustav
0
 

Author Closing Comment

by:GenesisTech
ID: 40375459
I found the solution on my own. Nobody suggestions had anything to do with the real cause.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

688 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