Insert into z VBA/Excel přes ADO

Question

Zdravím,

přes ADO načítám do excelu data z SQL databáze - vše (Select) bez problémů. Problém nastává v okamžiku, kdy se pokusím databázi změnit Insert into, Delete. Pokud použiji externí program pro práci s databází, je vše bez problémů, tzn. mám dostatečná oprávnění pro změny v databázi apod. V uvedeném příkladu projde Select, Insert ani Delete ne...

Díky za nápady...

JB

Public LinkID As ADODB.Connection
Public QueryID As ADODB.Recordset
Private Record() As String
Public Row As Long
Public NumRows As Long
Public Function connect(Optional server As String = "", Optional uid As String = "", Optional pwd As String = "", _
    Optional dbname As String = "") As Boolean
    Dim connStr As String
    If (server = "") Then server = "DBServer"
    If (uid = "") Then uid = "User1"
    If (pwd = "") Then pwd = "1234"
    If (dbname = "") Then dbname = "Database1"
    If (uid = "") Then
        connStr = "DRIVER={SQL Server};SERVER=" & server & ";Trusted_Connection=Yes;APP=Office 2007 App;DATABASE=" & dbname
    Else
        'connStr = "DRIVER={SQL Server};SERVER=" & server & ";UID=" & uid & ";PWD=" & pwd & ";APP=Office 2007 App;DATABASE=" & dbname
        connStr = "Provider=SqlOleDb;Data Source=DBServer;Initial Catalog = Database1;UID=" & uid & ";PWD=" & pwd & ";Options=-1;"
    End If
    If (LinkID Is Nothing) Then
        Set LinkID = New ADODB.Connection
        On Error Resume Next
        LinkID.Open connStr
        On Error GoTo 0
        If (LinkID.State = 0) Then
            ErrorNo = Err.Number
            ErrorTxt = Err.Description
        End If
    End If
    connect = LinkID.State
End Function
Public Function query(Optional queryStr As String = "") As Boolean
    If (queryStr = "") Then Exit Function
    If Not (connect) Then Exit Function
    If (QueryID Is Nothing) Then
        Set QueryID = New ADODB.Recordset
    ElseIf (QueryID.State) Then
        free_result
    End If
    On Error Resume Next
    QueryID.Open queryStr, LinkID, adOpenForwardOnly, adLockOptimistic, -1 ', adLockBatchOptimistic
    On Error GoTo 0
    Row = 0
    If (QueryID.State = 0) Then
        ErrorNo = Err.Number
        ErrorTxt = Err.Description
    End If
    NumRows = count_records
    query = QueryID.State
End Function
Public Sub free_result()
    If Not (QueryID Is Nothing) Then
        QueryID.Close
    End If
End Sub
Public Function count_records() As Integer
    count_records = 0
    If Not (QueryID Is Nothing) Then
        If (QueryID.State) Then
            While (Not QueryID.EOF)
                count_records = count_records + 1
                QueryID.MoveNext
            Wend
            If (count_records) Then
                QueryID.Requery
            End If
        End If
    End If
End Function
Sub Test()
    query "SELECT * FROM Table1 WHERE Empl = 'Tom'"
    query "INSERT INTO Table1 (EMPL)Values ('Tod')"
    query "DELETE FROM Table1 WHERE Empl = 'Tod'"
End Sub

Answer 1

To přece lze spustit v debug režimu. Co přesně říká chybové hlášení?

Answer 2

Zdravím, vyřešeno...

problémem byla moje blbost...

recordset jako takový - REcordset.Open - je pouze read only, takže zápis fakt neprojde.

Jo, s coonection.EXECUTE už je to něco jiného...

JB