Writing to a table in SQL server via MS Acess problem

[hardrock]

Hi all, I have been using the code below to create new records in an MS Access table called dbo_suppliercontact, and this has been working fine. Last week, we moved this table onto our works SQL server, so everyone can access it. I am able to link the table to my Access project via ODBC, and i am able to read the table ok. The Problem is when i attempt to create a new record, the code bums out at rst!.Addnew. Could someone kindly advise how i need to modify the code to allow me to write to the SQL server "dbo_suppliercontact" table. The IT guys have checked the table on the sQl server and say i have full write permission to it.

Thanks
**********************
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("dbo_SupplierContact")

rst.AddNew
rst!Supplier = Supcode
rst!Emailaddress = Emailad
rst!CopyAddress = CopyAd
rst!BlindAddress = BlindAd
rst.Update
Set rst = Nothing
Set qd = Nothing

 

[boblarson]

First of all -

The table in SQL Server needs to have a primary key assigned before you can add/update in Access.

Second, how are you linking to the tables? Which version of SQL Server are you using, and which ODBC driver are you using?

 

[hardrock]

Hi Bob, thanks for the reply.

On point 1. I will ask the server admin tomorrow if he has set me up a primary key.

On point 2. I log into the sql server Using a ODBC connection.I have linked the table direct to my ms access application. I am able to open the table in Access, but all the cells appear locked, so i cant change any value. Our IT administrator says i have full read/write access to it, but clearly i don't think i have?

 

[Banana]

If you delete the linked table and recreate a new linked table, do you get a little dialog asking for unique identifier?

Also to be crystal clear... are you using an Access project (e.g. .adp file) or an Access database (e.g. a .mdb file)?

 

[boblarson]

Also, again I will ask (to go with Banana's questions) -

Which version of SQL Server (2000, 2005, or 2008)?

And which ODBC Driver are you using.

 

[hardrock]

Hi Guys,

The driver is SQLSRV32.DLL Ver 2000.85.1132.00

And it's SQL server 2000.

The connection to the SQL server is done by making the connection in
Control Panel/Administrive Tools/Data Sources ODBC/
In the system DSN tag, i just put my server name and password and the connection is ok. I have called this conneciton MYWORK

In my access mdb file,i goto into tables,and then i link the ODBC table
"dbo_suppliercontact" from the MYWORK connection, i get a dialog box that appears to log into the server, (user / password) and the table then appears in my mdb.

I can open the table ok, but cannot write to it.

Thanks

 

[boblarson]

If you go into Access and go to the table and right-click on it and go to Design view (say yes, when it tells you it is linked and can't be changed) and see if there is a KEY icon for the primary key field. Again, it needs to have a field identified which uniquely identifies a record. The table in SQL Server should have a primary key and your table should show which one it is if you linked properly. If you linked and a dialog popped up asking you to identify the PK field, and you either didn't or selected the wrong one, it will also not be able to write to the table.

 

[hardrock]

Hi Bob, absolutely spot on! it was a missing primary key that was causing me the issue. Once that was established, then writing to the table was solved.
Many thanks to you all the other guys that contribute to this great forum.

 

[boblarson]

Glad we could assist -