Export Excel to SQL VBA: Could not find stored procedure

Question

I am trying to automate the export of my data from excel to SQL via VBA. I don't have much knowledge in VBA and Excel tells me the following error (see below). Where should I create that procedure? In SQL? How should that one be designed? (the xxx in the following code, I put them)

Sub testexportsql()
    Dim cn As ADODB.connection
    Dim ServerName As String
    Dim DatabaseName As String
    Dim TableName As String
    Dim UserID As String
    Dim Password As String
    Dim rs As ADODB.recordset
    Dim RowCounter As Long
    Dim NoOfFields As Integer
    Dim StartRow As Long
    Dim EndRow As Long
    Dim ColCounter As Integer

    Set rs = New ADODB.recordset

    ServerName = "xxx" ' Enter your server name here
    DatabaseName = "DATAWAREHOUSE" ' Enter your  database name here
    TableName = "dbo.AlbertaFire_import" ' Enter your Table name here
    UserID = "sa" ' Enter your user ID here
    ' (Leave ID and Password blank if using windows Authentification")
    Password = "xxx" ' Enter your password here
    NoOfFields = 331 ' Enter number of fields to update (eg. columns in your worksheet)
    StartRow = 2 ' Enter row in sheet to start reading  records
    EndRow = 200 ' Enter row of last record in sheet

     '  CHANGES
    Dim shtSheetToWork As Worksheet
    Set shtSheetToWork = ActiveWorkbook.Worksheets("Sheet2")
     '********

    Set cn = New ADODB.connection

    cn.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & _
    ";Uid=" & UserID & ";Pwd=" & Password & ";"

    rs.Open TableName, cn, adOpenKeyset, adLockOptimistic

     'EndRow = shtSheetToWork.Cells(Rows.Count, 1).End(xlUp).Row
    For RowCounter = StartRow To EndRow
        rs.AddNew
        For ColCounter = 1 To NoOfFields
        'On Error Resume Next
            rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter)
        Next ColCounter
        Debug.Print RowCounter
    Next RowCounter
    rs.UpdateBatch

     ' Tidy up
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

rs.Open TableName, cn, adOpenKeyset, adLockOptimistic

Run-time error '-2147217900 (80040e14)':
[Microsoft][OBDC SQL Sever Driver][SQL Server] Could not find stored procedure 'dbo.AlbertaFire_import'

Mike · Accepted Answer · 2019-07-06 13:14:51Z

1

I tried to reproduced the error. The code was working fine as long the table on the SQL server exist. If the table doesn't exist I get the same error code but as description "automation-error".

I guess the table doesn't exist on your server. Create the table AlbertaFire_import and try. If it works you maybe need to delete old records before you import new data. You can do this with "Execute" a bit SQL:

cn.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & ";Uid=" & UserID & ";Pwd=" & Password & ";"

cn.Execute "delete from " + TableName

rs.Open TableName, cn, adOpenKeyset, adLockOptimistic

I hope it helps...

answered Jul 6, 2019 at 13:14

Mike

3322 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

nimavancouver Over a year ago

Thank you ! indeed I had a typo mistake in it. I had another issue after that which I raised here: stackoverflow.com/questions/56939569/… . I will be very thankfull to hear from you again. Just a small question regarding your code, where should I put it? Just after " ' Tidy up " ?

Mike Over a year ago

the cn.Execute line between your already existing lines cn.Open and rs.Open

Mike Over a year ago

your welcome. don't forgett to mark the answer as correct so I get the points. click on the triangle above the 0 left to the answer... ;)

nimavancouver Over a year ago

sure! i didnt know about that :D

ASH · Accepted Answer · 2019-07-20 11:44:16Z

There are several ways to do something like this.

Sub sql_login()

    '******************************************************
    ' Connection info to log into SQL Server
    '******************************************************
    Dim ServerName As String
    Dim dbname As String
    Dim uname As String
    Dim pword As String

    ServerName = "your_server_name"
    dbname = "Northwind"
    'uname = "**************"
    'pword = "**************"

    '******************************************************
    ' Calls the SQLConnect to query batch information
    '******************************************************
    Call SQLConnect(ServerName, dbname) ', uname, pword)

End Sub


Sub SQLConnect(ServerName As String, dbname As String) ', uname As String, pword As String)
    '******************************************************
    ' Logs into SQL Server to get actual batch information
    '******************************************************
    Dim Cn As adodb.Connection
    Set Cn = New adodb.Connection

    'On Error GoTo ErrHand
        With Cn
            .ConnectionString = "your_server_name;Database=Northwind;Trusted_Connection=True;"
        End With

    '******************************************************
    ' Calls the the SQL Query
    '******************************************************
    Call sql_query(Cn)

End Sub


Sub sql_query(Cn As adodb.Connection)

    '******************************************************
    ' Performs SQL Query
    '******************************************************
    Dim RS As adodb.Recordset
    Dim sqlString As String
    Set RS = New adodb.Recordset

    sqlString = "Select * From Northwind.dbo.TBL"
    RS.Open sqlString, Cn, adOpenStatic, adLockOptimistic
    Cn.Execute (sqlString)

    Dim fld As adodb.Field

    '******************************************************
    ' Create Field Headers for Query Results
    '******************************************************
    i = 0
    With Worksheets("sheet1").Range("A1")
        For Each fld In RS.Fields
            .Offset(0, i).Value = fld.Name
            i = i + 1
        Next fld
    End With

    '******************************************************
    ' Copy Query Results into Excel
    '******************************************************
    Worksheets("sheet1").Range("A1").CopyFromRecordset RS

End Sub

Or . . .

Sub InsertInto()

'Declare some variables
Dim cnn As adodb.Connection
Dim cmd As adodb.Command
Dim strSQL As String

'Create a new Connection object
Set cnn = New adodb.Connection

'Set the connection string
cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Northwind;Data Source=your_server_name"
'cnn.ConnectionString = "DRIVER=SQL Server;SERVER=your_server_name;DATABASE=Northwind;Trusted_Connection=Yes"


'Create a new Command object
Set cmd = New adodb.Command

'Open the Connection to the database
cnn.Open

'Associate the command with the connection
cmd.ActiveConnection = cnn

'Tell the Command we are giving it a bit of SQL to run, not a stored procedure
cmd.CommandType = adCmdText

'Create the SQL
strSQL = "UPDATE TBL SET JOIN_DT = '2013-01-22' WHERE EMPID = 2"

'Pass the SQL to the Command object
cmd.CommandText = strSQL


'Execute the bit of SQL to update the database
cmd.Execute

'Close the connection again
cnn.Close

'Remove the objects
Set cmd = Nothing
Set cnn = Nothing

End Sub

There are a few other things you can do as well, all related to the methodologies mentioned above.

Collectives™ on Stack Overflow

Export Excel to SQL VBA: Could not find stored procedure

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related