×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

DSN-Less Connection Not Allowing New Records

DSN-Less Connection Not Allowing New Records

DSN-Less Connection Not Allowing New Records

(OP)
I'm attempting to link an Access 2002 database to an MSSQL server and need to do so without setting up ODBC connections on all user's computers.

I used the code specified in the Microsoft Article (http://support.microsoft.com/kb/892490). It pulls in the tables just fine, but I cannot add a new record.

To do so, I have to refresh the links, with an ODBC connection, and specify a Unique Field for the tables. Is there a way to specify this in the code that connects the tables?

Thank you and any help is appreciated.

RE: DSN-Less Connection Not Allowing New Records

Specify a primary key on the tables in SQL Server. Then relink them.

Duane
Hook'D on Access
MS Access MVP

RE: DSN-Less Connection Not Allowing New Records

(OP)
That worked, thank you.

If I may ask another question, would you remove the linked tables from the database when the user exits the database? If so, do you know what code would be used to do so?

RE: DSN-Less Connection Not Allowing New Records

(OP)
Would there be an issue that the connection wouldn't close properly, or leave records locked when the database is closed? Also, is it possible to make sure that no record locks are left in place when a form that uses one of the linked tables is closed?

Thanks,

RE: DSN-Less Connection Not Allowing New Records

Closing the database (or even bound forms) should release all locks. I haven't experienced anything different.

Duane
Hook'D on Access
MS Access MVP

RE: DSN-Less Connection Not Allowing New Records

(OP)
Thanks for the replies Duane. I'm still having a heck of a time with one piece of this. I was able to get the connection to MSSQL to work. Some of the users connect to MS Access through a Citrix client. They can open, add and edit data. I am running into a problem with a checkbox.

If it was checked, then I uncheck the box (via the Citrix connection) it hangs and finally returns an ODBC call failed error. When I close the database it states that it can't save due to being locked.

Might there be data type issues (the MSSQL type is a bit) causing some conflict when changing from -1 (yes) to 0 (no)?

RE: DSN-Less Connection Not Allowing New Records

(OP)
As an update, we've tried updating the data type from bit to int and it didn't work either. Same thing regarding the ODBC Call Failed after unchecking a checkbox.  

RE: DSN-Less Connection Not Allowing New Records

Did you relink after changing the data type to int? Do you have a default value set for the field?

I try to avoid using Y/N fields in any of my applications. I typically use INT and set the default to either -1 or 0. When I query the field, I always use "= 0" for false or "<> 0" for true.

Duane
Hook'D on Access
MS Access MVP

RE: DSN-Less Connection Not Allowing New Records

(OP)
Well, it doesn't appear to be limited to the checkbox. The Update isn't completing before the form reopens and executes a Select. It's creating a lock. Is there a way to explicitely commit a change through the VB in Access other than accmdSaveRecord?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close