Using Access (VBA) to connect to an SQL Server 2005 DB 1

[mishbaker]

I'm trying to connect to an SQL table on my SQL Server 2005.

My connection string is creating a valid connection and I can read the data on the table. Unfortunately I can't write/edit data on the SQL table.

Am I not doing something right?

Sub getSQL()
Dim conn As ADODB.Connection
Dim fld As Object
Dim strSQL As String
Dim rs As ADODB.Recordset

' Connect to SQL Database
    strCon = "Driver={SQL Native Client};Server=SERVARSC\SQLEXPRESS;Database=DigitalLib;Trusted_Connection=yes;"
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    conn.Open strCon
    
    If conn.State = adStateOpen Then
        MsgBox "Connection valid"
    ElseIf conn.State = adStateClosed Then
        MsgBox "Connection failed"
    Else
        MsgBox "something wrong here.."
    End If
    
    rs.Open "SELECT * FROM tblCatalog;", conn
    
' Actions on Recordset
    Do While Not rs.EOF
        Debug.Print rs.Fields(1)
        rs.MoveNext
    Loop
    rs.AddNew
    For x = 0 To rs.Fields.Count - 1
        rs.Fields(x) = "looped"
    Next x
    rs.Update
    
' Close SQL Database Connection
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing

End Sub

All lessons learned from the School of Hard Knocks........at least tuition is cheap.

 

[RoyVidar]

If you don't specify cursor and locktype, you're probably getting a readonly, forwardonly recordset

[tt]with rs
set .activeconnection = conn
.cursortype = adopenkeyset
.locktype = adlockoptimistic
.open "select * from tblCatalog", , , , adcmdtext
end with[/tt]

Roy-Vidar

 

[mishbaker]

Thanks. I'll give it a shot. I had atually tried setting the rs to adOpenDynamic and adLockOptimistic,but it gave me an error saying there was a conflict. I'll try to recreate and post it.

All lessons learned from the School of Hard Knocks........at least tuition is cheap.

 

[mishbaker]

The problem was Office 2k. When I tried the same thing in Office 2003 it works perfect. Thanks again!

All lessons learned from the School of Hard Knocks........at least tuition is cheap.