Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DSN-Less Connection Not Allowing New Records

Status
Not open for further replies.
nobull613

nobull613

Technical User
Jun 6, 2003
76
US
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 ( 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.
 
Sort by date Sort by votes
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?
 
Upvote 0 Downvote
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,
 
Upvote 0 Downvote
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)?
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JayRo71
  • Locked
  • Question
Importing only unique records from excel tables 1
Replies
10
Views
939
SkipVought
S
monkeysee
  • Locked
  • Question
Field to count specific records
Replies
3
Views
646
dhookom
dhookom
TrekBiker
  • Locked
  • Question
Prevent new data being used when using Archive backend
Replies
0
Views
4K
TrekBiker
TrekBiker
oxicottin
  • Locked
  • Question
Creating tables and relationships for new database
Replies
2
Views
4K
MajP
MajP
Eutychus
  • Locked
  • Question
ODBC connection to SQL Server tables for runtime version of Access
Replies
4
Views
353
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top