Access Link to SQL

[ninash]

Hi All
I have an Access 2000 DB that has linked tables to a SQL DB.

The SQL DB is active and I can read from the tables ... But.

I need to write to and append data to these tables.

I have full admin rights to the SQL DB but still can't make any form of data write to it....

Any clues on where I am going wrong?.

Ta
Tony

 

[drctx]

to edit data you need a primary key set for the table or when you link to the sql table select a unique record identifier.

 

[ninash]

Thank you very much drctx
That took care of the ability to write to the database but now I get a type mismatch error trying to write to the SQL database.

I have listed my code below but I can't see why the select line would throw up this error as it is only selecting data from the database.

Private Sub AddClientButton_Click()
Dim rstAddClient As Recordset
Dim Message, Title, Default As String
Dim NewClientName As String

Message = "Input name of client in proper case format"
Title = "New Client Entry"
Default = "Client Name"
'Display inputbox with message, title and default value.
NewClientName = InputBox(Message, Title, Default)

If NewClientName = "Client Name" Then
NewClientName = ""
End If

If NewClientName <> "" Then
Set rstAddClient = CurrentDb.OpenRecordset("select [Client Name], [ClientCounter] from [dbo_Client]")
'Add New Record Here
With rstAddClient
.AddNew
'Field references to rstSQ to update Questionaire table
![Client Name] = NewClientName
![ClientCounter] = 4
.Update
End With
rstAddClient.Close
End If

MsgBox NewClientName

End Sub

The number 4 will be replaced with a max + 1 Statement to ensure a unique counter number is used.

Thanks for any help you can give.

Tony