Nelze provést Insert, Delete VBA/Excel, ADO – MS SQL – Fórum – Programujte.com

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

Zdravím,

myslím že by to mohlo být tím že používáš recordset s parametrem commandText.

Radši používám objekt ADOBD.Command 

Dim oCmd As New ADODB.Command

 Pro insert a delete bych to řešil takto

With oCmd
        .ActiveConnection = LinkID

        .CommandText = queryStr      
        .CommandType = adCmdText
        .Execute
End With

#2 Zed_1
nemohlo by, je :-)

Blbce stačí postrčit, dík