Connecting to/Updating SQL Srv d/b from Office XP 1

[onedunpark]

Hello,

My firm has just installed a SQL Server based accounting package and I've been instructed to integrate this with our Word templates. Basic desktop setup is as follows:

Win2K Pro
Office XP (referencing DAO 3.6)
SQL Server ODBC driver installed

This code is being run from Word.

I can successfully connect to and read from my test DB using the code below.
I can't however, add new rows to the same table. I can do so manually in SQL Server, so I don't think it is a permissions thing.

I keep getting the '3027' message about the database or object being read-only. I'm aware this might actually be a Word/VBA problem, but hoped the experts here would be able to help, if at all possible


All replies and advice gratefully received.

Thanks

Steven


The table structure is:

TableName: STEVENTESTFROMWORD
PRIKEY int, 4, NOT NULL (This column defined as a Primary Key)
COL2, char 10, NULL
COL3,char10, NULL

Code Below
-------------

Sub testconn()

Dim vwkspc As Workspace
Dim vdbcon As Connection
DefaultType = dbUseODBC

' Initialise the program workspace
Set vwkspc = Workspaces(0)

Dim vstrConnect As String
vstrConnect = _
"ODBC;DRIVER={sql server};" & _
"DATABASE=TESTDB;" & _
"SERVER=DATASERVER;" & _
"Trusted_Connection=Yes"
Set vdbcon = vwkspc.OpenConnection _
("", dbDriverNoPrompt, False, vstrConnect)

Dim vrset As Recordset

Set vrset = vdbcon.OpenRecordset _
("Select PRIKEY,COL2,COL3 from STEVENTESTFROMWORD", dbOpenDynaset)

With vrset
Do While Not .EOF
Debug.Print , .Fields(0), .Fields(1), .Fields(2)
.MoveNext
Loop
End With

With vrset
.AddNew
!PRIKEY = 1
!COL2 = "VALUE1"
!COL3 = "VALUE2"
.Update
End With


End Sub

 

[MissyEd]

A couple of things to check:

1. Your connection string. Make sure none of the parameters for openconnection are setting the connection to read only.
2. Check your recordset SET statement, I think you also need dbseechanges - but Im running 97, so things may have changed.

Set vrset = vdbcon.OpenRecordset _
("Select PRIKEY,COL2,COL3 from STEVENTESTFROMWORD", dbOpenDynaset, dbSeeChanges)

Missy Ed - Bolton, UK

 

[chrissie1]

why use dao to connect to sqlserver? thats wasting resources
try ADO it was made for that purpose
and then you dont need the odbc driver

"What a wonderfull world" - Louis armstrong

 

[janeatwork]

chrissie1, I'm very interested in your comments, how would one use ADO to connect to SQL Server without using an ODBC driver. Give an example please. I use ADO all the time but use ODBC. Thanks

 

[chrissie1]

something like this should work just fine

Private Sub connectioninado()
Dim con_main As New ADODB.Connection
Dim rst_temp As New ADODB.Recordset

Set con_main.ConnectionString = "driver={SQL Server};server=srv;uid=sa;pwd=pwd;database=Pubs"
con_main.Open
Set rst_temp.ActiveConnection = con_main
rst_temp.Open "Select * from table"
If rst_temp.RecordCount > 0 Then
txtbox1 = rst_temp.Fields("columnname&quot
End If
rst_temp.Close
con_main.Close
End Sub "What a wonderfull world" - Louis armstrong