Unable write to SQL from VBA/Excel using ADO

I'm using VBA for working with SQL database, Select commands forking fine, problem is with modification of dbase - insert, delete. If I using external application for work with DBase, everything is OK, thats mean - my privilegs is OK. On example Select is done successfully, rest not...

Thanks for your help.

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

Answers


RecordSet.Open is only used to open a cursor (select) not execute some DML. For this you can use Connection.Execute. The last can also be used to create a RecordSet.

See for example [http://msdn.microsoft.com/en-us/library/ms807027.aspx]


Need Your Help

Is it possible to hook the API call that puts text on Mac OS?

objective-c macos api

There's a program called PPStream which is currently only available in Chinese, it allows for access to a myriad of ad-supported movies and TV series. The problems is that it is in Chinese and menu...

how to write connection string for this sql server 2008 in asp.net with c#

c# asp.net sql-server

This is my sql server connection page.. How can i write connection string for this in asp.net using c# ?