dsn-less linked tables write issue

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?
GenesisTechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
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
GenesisTechAuthor Commented:
Did you go to the link posted above? That has the code I am using to make the connection.
0
omgangIT ManagerCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

GenesisTechAuthor Commented:
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
GenesisTechAuthor Commented:
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
omgangIT ManagerCommented:
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
omgangIT ManagerCommented:
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
GenesisTechAuthor Commented:
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
omgangIT ManagerCommented:
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
GenesisTechAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<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
Gustav BrockCIOCommented:
Try with:

DRIVER=SQL SERVER;

That works here.

/gustav
0
GenesisTechAuthor Commented:
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
Gustav BrockCIOCommented:
It sounds like you will have to modify the query ...

/gustav
0
GenesisTechAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
Well, that was what I suggested, though not in detail, as we didn't have the query.

/gustav
0
GenesisTechAuthor Commented:
I found the solution on my own. Nobody suggestions had anything to do with the real cause.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.