none
Saving a stored procedure from sql in MS Access 2010 local table using VBA RRS feed

  • Question

  • Hello there,

    I am not familiar with VBA and this is my first real attempt to try to write something in it.

    Problem:

    I have a stored procedure sitting on SQL server. I need to import the results of that stored procedure in Access 2010 local table.

    Soultion:

    After much reseraching, I found pieces of code that extract the results of SQL store procedure and with someone else's help was able to print the results in immediate window. What I really wanted though is to save those results in local table. I was trying to find a way to just save the entire data dump in one "iteration" by using one of the built in functions but was not able to find anything useful for me. So, I figured to save the data by field but I am getting an error: "item cannot be found in the collection corresponding to the requested name or ordinal." I suspect that it is due to the fact that my local table is completely empty - there is not a single entry there. Before I went on to create 137 columns, I wanted to see whether this was really a problem. Also, maybe, there is a much easier solution to do what I am trying to do? I open to suggestions.

    Anyways, I am attaching the code here as well. thanks.


    Public Sub createDataToAnalyze4()
       
        Dim objConnection As New ADODB.Connection
        Dim objRS As ADODB.Recordset
        Dim intColIdx As Integer
       
        Dim objCom As ADODB.Command
       
        Dim provStr As String
       
        Set objCom = New ADODB.Command
       
        objConnection.Provider = "sqloledb"
        provStr = "Data Source=rr1wwlilsql1;Initial Catalog=LID;Trusted_Connection=Yes"
        objConnection.Open provStr
       
        Dim db As Database
        Set db = CurrentDb()
        Dim rsTable2 As DAO.Recordset
        Set rsTable2 = db.OpenRecordset("LOCKIT_FROM_LID", dbOpenDynaset)
        'Set rstTable2 = CurrentDb.OpenRecordset("LOCKIT_FROM_LID")
       
        With objCom
            .ActiveConnection = objConnection
            .CommandText = "DUMP" '_FOR_LOCKIT"
            .CommandType = adCmdStoredProc
            .CommandTimeout = 300
            Set objRS = .Execute()
        End With
       
        With objRS
            Do While Not .EOF
                rsTable2.AddNew
                rstTable2!Field1 = !Field1
                rstTable2!Field2 = !Field2
                rstTable2!Field3 = !Field3
                rstTable2!Field4 = !Field4
                rstTable2!Field5 = !Field5
                rstTable2!Field6 = !Field6
                rstTable2!Field7 = !Field7
                rstTable2!Field8 = !Field8
                rstTable2!Field9 = !Field9
                rstTable2!Field10 = !Field10
                rstTable2!Field11 = !Field11
                rstTable2!Field12 = !Field12
                rstTable2!Field13 = !Field13
                rstTable2!Field14 = !Field14
                rstTable2!Field15 = !Field15
                rstTable2!Field16 = !Field16
                rstTable2!Field17 = !Field17
                rstTable2!Field18 = !Field18
                rstTable2!Field19 = !Field19
                rstTable2!Field20 = !Field20
                rstTable2!Field21 = !Field21
                rstTable2!Field22 = !Field22
                rstTable2!Field23 = !Field23
                rstTable2!Field24 = !Field24
                rstTable2!Field25 = !Field25
                rstTable2!Field26 = !Field26
                rstTable2!Field27 = !Field27
                rstTable2!Field28 = !Field28
                rstTable2!Field29 = !Field29
                rstTable2!Field30 = !Field30
                rstTable2!Field31 = !Field31
                rstTable2!Field32 = !Field32
                rstTable2!Field33 = !Field33
                rstTable2!Field34 = !Field34
                rstTable2!Field35 = !Field35
                rstTable2!Field36 = !Field36
                rstTable2!Field37 = !Field37
                rstTable2!Field38 = !Field38
                rstTable2!Field39 = !Field39
                rstTable2!Field40 = !Field40
                rstTable2!Field41 = !Field41
                rstTable2!Field42 = !Field42
                rstTable2!Field43 = !Field43
                rstTable2!Field44 = !Field44
                rstTable2!Field45 = !Field45
                rstTable2!Field46 = !Field46
                rstTable2!Field47 = !Field47
                rstTable2!Field48 = !Field48
                rstTable2!Field49 = !Field49
                rstTable2!Field50 = !Field50
                rstTable2!Field51 = !Field51
                rstTable2!Field52 = !Field52
                rstTable2!Field53 = !Field53
                rstTable2!Field54 = !Field54
                rstTable2!Field55 = !Field55
                rstTable2!Field56 = !Field56
                rstTable2!Field57 = !Field57
                rstTable2!Field58 = !Field58
                rstTable2!Field59 = !Field59
                rstTable2!Field60 = !Field60
                rstTable2!Field61 = !Field61
                rstTable2!Field62 = !Field62
                rstTable2!Field63 = !Field63
                rstTable2!Field64 = !Field64
                rstTable2!Field65 = !Field65
                rstTable2!Field66 = !Field66
                rstTable2!Field67 = !Field67
                rstTable2!Field68 = !Field68
                rstTable2!Field69 = !Field69
                rstTable2!Field70 = !Field70
                rstTable2!Field71 = !Field71
                rstTable2!Field72 = !Field72
                rstTable2!Field73 = !Field73
                rstTable2!Field74 = !Field74
                rstTable2!Field75 = !Field75
                rstTable2!Field76 = !Field76
                rstTable2!Field77 = !Field77
                rstTable2!Field78 = !Field78
                rstTable2!Field79 = !Field79
                rstTable2!Field80 = !Field80
                rstTable2!Field81 = !Field81
                rstTable2!Field82 = !Field82
                rstTable2!Field83 = !Field83
                rstTable2!Field84 = !Field84
                rstTable2!Field85 = !Field85
                rstTable2!Field86 = !Field86
                rstTable2!Field87 = !Field87
                rstTable2!Field88 = !Field88
                rstTable2!Field89 = !Field89
                rstTable2!Field90 = !Field90
                rstTable2!Field91 = !Field91
                rstTable2!Field92 = !Field92
                rstTable2!Field93 = !Field93
                rstTable2!Field94 = !Field94
                rstTable2!Field95 = !Field95
                rstTable2!Field96 = !Field96
                rstTable2!Field97 = !Field97
                rstTable2!Field98 = !Field98
                rstTable2!Field99 = !Field99
                rstTable2!Field100 = !Field100
                rstTable2!Field101 = !Field101
                rstTable2!Field102 = !Field102
                rstTable2!Field103 = !Field103
                rstTable2!Field104 = !Field104
                rstTable2!Field105 = !Field105
                rstTable2!Field106 = !Field106
                rstTable2!Field107 = !Field107
                rstTable2!Field108 = !Field108
                rstTable2!Field109 = !Field109
                rstTable2!Field110 = !Field110
                rstTable2!Field111 = !Field111
                rstTable2!Field112 = !Field112
                rstTable2!Field113 = !Field113
                rstTable2!Field114 = !Field114
                rstTable2!Field115 = !Field115
                rstTable2!Field116 = !Field116
                rstTable2!Field117 = !Field117
                rstTable2!Field118 = !Field118
                rstTable2!Field119 = !Field119
                rstTable2!Field120 = !Field120
                rstTable2!Field121 = !Field121
                rstTable2!Field122 = !Field122
                rstTable2!Field123 = !Field123
                rstTable2!Field124 = !Field124
                rstTable2!Field125 = !Field125
                rstTable2!Field126 = !Field126
                rstTable2!Field127 = !Field127
                rstTable2!Field128 = !Field128
                rstTable2!Field129 = !Field129
                rstTable2!Field130 = !Field130
                rstTable2!Field131 = !Field131
                rstTable2!Field132 = !Field132
                rstTable2!Field133 = !Field133
                rstTable2!Field134 = !Field134
                rstTable2!Field135 = !Field135
                rstTable2!Field136 = !Field136
                rstTable2!Field137 = !Field137

                rstTable2.Update
                .MoveNext
            Loop
        End With
       
       
        Dim sqlStatement As String
       
        objRS.Close
        Set objRS = Nothing
       
        objConnection.Close
        Set objConnection = Nothing
       
    End Sub



    • Edited by GKED Friday, January 25, 2013 2:43 PM
    Friday, January 25, 2013 2:34 PM