DSN-Less Connection Not Allowing New Records

[nobull613]

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.

 

[dhookom]

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

Duane
Hook'D on Access
MS Access MVP

 

[nobull613]

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?

 

[dhookom]

I don't see any reason to "unlink" the tables.

Duane
Hook'D on Access
MS Access MVP

 

[nobull613]

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,

 

[dhookom]

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

 

[nobull613]

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)?

 

[nobull613]

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.

 

[dhookom]

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

 

[nobull613]

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?

 

[dhookom]

To save changes in a form, use:

Me.Dirty = False

Duane
Hook'D on Access
MS Access MVP