ADODB.Connection not reflecting current data set fields; '[Microsoft][ODBC Excel Driver] Too few parameters....











up vote
0
down vote

favorite












Below mentioned sample procedure use to read data from input excel files saved in a folder. The SQl query has join operations from few tables (sheets) which are part of reportWorkbook. After running this procedure successfully on some files at some stage it throws an error '[Microsoft][ODBC Excel Driver] Too few parameters. Expected 11.' for next input file.



Sub RunQueryOnInputFiles()



Dim fldr            As Folder
Dim fil As File
Dim oConn As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim tmpWkb As Workbook
Dim reportWorkbook As Workbook
Dim sSql As String

Set reportWorkbook = Workbooks.Add
reportWorkbook.Save tmpFilePath

'Loop through all files in input folder

For Each fil In fldr.Files
Set tmpWkb = Workbooks.Open(fil.Path)
tmpWkb.Sheets(1).Range("A1").CurrentRegion.Copy
reportWorkbook.Sheets("TmpSheet").Range("A1").Paste xlPasteValues
'Paste input data
tmpWkb.Close SaveChanges:=False

Set oConn = OpenConnection(reportWorkbook.FullName)
sSql = GenrateSQL("SOME QUERY FROM SHEET [TmpSheet$]")
'Genrate SQL query from [TmpSheet$]`
Set oRs = GetRecordset(sSql, oConn)
reportWorkbook.Sheets("XYZ").Range("A1").CopyFromRecordset oRs
CloseConnection oConn
Next fils

End Sub


I debug the problem separately using below mentioned procedure



Sub testing()
Dim sSql As String
Dim x As ADODB.Connection
Dim r As Object
Dim fl As Object
sSql = "SELECT * FROM [TmpSheet$A1:U4]"
'considering reportWorkbook is in scope it is same workbook
Set x = OpenConnection(reportWorkbook.FullName)
GetRecordset sSql, x
Set r = GetRecordset(sSql, x)
For Each fl In r.Fields
Debug.Print fl.Name
Next
End Sub


Here, fl.Name is returning the names which belongs to previous file which is already processed and data on TmpSheet is already been overwritten. Running the same testing() procedure after a few minutes shows correct fields which are available in Tmpsheet.



It seems that connection cannot be refreshed. Please suggest some method which can enforce connection to be refresh in run time.










share|improve this question




























    up vote
    0
    down vote

    favorite












    Below mentioned sample procedure use to read data from input excel files saved in a folder. The SQl query has join operations from few tables (sheets) which are part of reportWorkbook. After running this procedure successfully on some files at some stage it throws an error '[Microsoft][ODBC Excel Driver] Too few parameters. Expected 11.' for next input file.



    Sub RunQueryOnInputFiles()



    Dim fldr            As Folder
    Dim fil As File
    Dim oConn As ADODB.Connection
    Dim oRs As ADODB.Recordset
    Dim tmpWkb As Workbook
    Dim reportWorkbook As Workbook
    Dim sSql As String

    Set reportWorkbook = Workbooks.Add
    reportWorkbook.Save tmpFilePath

    'Loop through all files in input folder

    For Each fil In fldr.Files
    Set tmpWkb = Workbooks.Open(fil.Path)
    tmpWkb.Sheets(1).Range("A1").CurrentRegion.Copy
    reportWorkbook.Sheets("TmpSheet").Range("A1").Paste xlPasteValues
    'Paste input data
    tmpWkb.Close SaveChanges:=False

    Set oConn = OpenConnection(reportWorkbook.FullName)
    sSql = GenrateSQL("SOME QUERY FROM SHEET [TmpSheet$]")
    'Genrate SQL query from [TmpSheet$]`
    Set oRs = GetRecordset(sSql, oConn)
    reportWorkbook.Sheets("XYZ").Range("A1").CopyFromRecordset oRs
    CloseConnection oConn
    Next fils

    End Sub


    I debug the problem separately using below mentioned procedure



    Sub testing()
    Dim sSql As String
    Dim x As ADODB.Connection
    Dim r As Object
    Dim fl As Object
    sSql = "SELECT * FROM [TmpSheet$A1:U4]"
    'considering reportWorkbook is in scope it is same workbook
    Set x = OpenConnection(reportWorkbook.FullName)
    GetRecordset sSql, x
    Set r = GetRecordset(sSql, x)
    For Each fl In r.Fields
    Debug.Print fl.Name
    Next
    End Sub


    Here, fl.Name is returning the names which belongs to previous file which is already processed and data on TmpSheet is already been overwritten. Running the same testing() procedure after a few minutes shows correct fields which are available in Tmpsheet.



    It seems that connection cannot be refreshed. Please suggest some method which can enforce connection to be refresh in run time.










    share|improve this question


























      up vote
      0
      down vote

      favorite









      up vote
      0
      down vote

      favorite











      Below mentioned sample procedure use to read data from input excel files saved in a folder. The SQl query has join operations from few tables (sheets) which are part of reportWorkbook. After running this procedure successfully on some files at some stage it throws an error '[Microsoft][ODBC Excel Driver] Too few parameters. Expected 11.' for next input file.



      Sub RunQueryOnInputFiles()



      Dim fldr            As Folder
      Dim fil As File
      Dim oConn As ADODB.Connection
      Dim oRs As ADODB.Recordset
      Dim tmpWkb As Workbook
      Dim reportWorkbook As Workbook
      Dim sSql As String

      Set reportWorkbook = Workbooks.Add
      reportWorkbook.Save tmpFilePath

      'Loop through all files in input folder

      For Each fil In fldr.Files
      Set tmpWkb = Workbooks.Open(fil.Path)
      tmpWkb.Sheets(1).Range("A1").CurrentRegion.Copy
      reportWorkbook.Sheets("TmpSheet").Range("A1").Paste xlPasteValues
      'Paste input data
      tmpWkb.Close SaveChanges:=False

      Set oConn = OpenConnection(reportWorkbook.FullName)
      sSql = GenrateSQL("SOME QUERY FROM SHEET [TmpSheet$]")
      'Genrate SQL query from [TmpSheet$]`
      Set oRs = GetRecordset(sSql, oConn)
      reportWorkbook.Sheets("XYZ").Range("A1").CopyFromRecordset oRs
      CloseConnection oConn
      Next fils

      End Sub


      I debug the problem separately using below mentioned procedure



      Sub testing()
      Dim sSql As String
      Dim x As ADODB.Connection
      Dim r As Object
      Dim fl As Object
      sSql = "SELECT * FROM [TmpSheet$A1:U4]"
      'considering reportWorkbook is in scope it is same workbook
      Set x = OpenConnection(reportWorkbook.FullName)
      GetRecordset sSql, x
      Set r = GetRecordset(sSql, x)
      For Each fl In r.Fields
      Debug.Print fl.Name
      Next
      End Sub


      Here, fl.Name is returning the names which belongs to previous file which is already processed and data on TmpSheet is already been overwritten. Running the same testing() procedure after a few minutes shows correct fields which are available in Tmpsheet.



      It seems that connection cannot be refreshed. Please suggest some method which can enforce connection to be refresh in run time.










      share|improve this question















      Below mentioned sample procedure use to read data from input excel files saved in a folder. The SQl query has join operations from few tables (sheets) which are part of reportWorkbook. After running this procedure successfully on some files at some stage it throws an error '[Microsoft][ODBC Excel Driver] Too few parameters. Expected 11.' for next input file.



      Sub RunQueryOnInputFiles()



      Dim fldr            As Folder
      Dim fil As File
      Dim oConn As ADODB.Connection
      Dim oRs As ADODB.Recordset
      Dim tmpWkb As Workbook
      Dim reportWorkbook As Workbook
      Dim sSql As String

      Set reportWorkbook = Workbooks.Add
      reportWorkbook.Save tmpFilePath

      'Loop through all files in input folder

      For Each fil In fldr.Files
      Set tmpWkb = Workbooks.Open(fil.Path)
      tmpWkb.Sheets(1).Range("A1").CurrentRegion.Copy
      reportWorkbook.Sheets("TmpSheet").Range("A1").Paste xlPasteValues
      'Paste input data
      tmpWkb.Close SaveChanges:=False

      Set oConn = OpenConnection(reportWorkbook.FullName)
      sSql = GenrateSQL("SOME QUERY FROM SHEET [TmpSheet$]")
      'Genrate SQL query from [TmpSheet$]`
      Set oRs = GetRecordset(sSql, oConn)
      reportWorkbook.Sheets("XYZ").Range("A1").CopyFromRecordset oRs
      CloseConnection oConn
      Next fils

      End Sub


      I debug the problem separately using below mentioned procedure



      Sub testing()
      Dim sSql As String
      Dim x As ADODB.Connection
      Dim r As Object
      Dim fl As Object
      sSql = "SELECT * FROM [TmpSheet$A1:U4]"
      'considering reportWorkbook is in scope it is same workbook
      Set x = OpenConnection(reportWorkbook.FullName)
      GetRecordset sSql, x
      Set r = GetRecordset(sSql, x)
      For Each fl In r.Fields
      Debug.Print fl.Name
      Next
      End Sub


      Here, fl.Name is returning the names which belongs to previous file which is already processed and data on TmpSheet is already been overwritten. Running the same testing() procedure after a few minutes shows correct fields which are available in Tmpsheet.



      It seems that connection cannot be refreshed. Please suggest some method which can enforce connection to be refresh in run time.







      excel vba excel-vba adodb






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 20 at 13:41









      Pᴇʜ

      20.1k42650




      20.1k42650










      asked Nov 20 at 12:58









      Sonu Kumar

      675




      675
























          1 Answer
          1






          active

          oldest

          votes

















          up vote
          0
          down vote













          From the post of your abbreviated code, the challenge you are facing is querying the very workbook you are processing which always reflects the snapshot of last saved instance.



          Consider incorporating a Workbook.Save method after processing each Excel file within the loop.



          For Each fil In fldr.Files                                              
          ...

          reportWorkbook.Save
          Next fils


          Additionally, consider querying the current workbook in the loop to avoid the overhead of opening the file and copying and pasting values.



          For Each fil In fldr.Files                                              
          ' QUERY CURRENT WORKBOOK
          Set oConn = OpenConnection(fil.Path)
          sSql = GenrateSQL("SELECT * FROM [Sheet1$]")
          Set oRs = GetRecordset(sSql, oConn)
          reportWorkbook.Sheets("TmpSheet").Range("A1").CopyFromRecordset oRs
          oRs.Close()
          CloseConnection oConn

          ' QUERY MASTER WORKBOOK
          Set oConn = OpenConnection(reportWorkbook.FullName)
          sSql = GenrateSQL("SOME QUERY FROM SHEET [TmpSheet$]")
          Set oRs = GetRecordset(sSql, oConn)
          reportWorkbook.Sheets("XYZ").Range("A1").CopyFromRecordset oRs
          oRs.Close()
          CloseConnection oConn

          reportWorkbook.Save
          Next fils


          Additional Notes:




          • It appears you are overwriting data in XYZ sheet with each iteration of loop if that is the intention. Add or change sheet to avoid this overwriting.


          • If your sheets have headers, be sure to copy recordset starting in A2 and loop field names starting in A1 (like your testing() subroutine). Or use static headers in row 1.


          • For bottom solution to query looped workbook, if sheet is not known in advance you will need to open document in loop to get sheet name and pass it into first SQL query.







          share|improve this answer





















          • XYZ sheet is vary based on input file.
            – Sonu Kumar
            Nov 20 at 17:44











          Your Answer






          StackExchange.ifUsing("editor", function () {
          StackExchange.using("externalEditor", function () {
          StackExchange.using("snippets", function () {
          StackExchange.snippets.init();
          });
          });
          }, "code-snippets");

          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "1"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          convertImagesToLinks: true,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: 10,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53393531%2fadodb-connection-not-reflecting-current-data-set-fields-microsoftodbc-excel%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes








          up vote
          0
          down vote













          From the post of your abbreviated code, the challenge you are facing is querying the very workbook you are processing which always reflects the snapshot of last saved instance.



          Consider incorporating a Workbook.Save method after processing each Excel file within the loop.



          For Each fil In fldr.Files                                              
          ...

          reportWorkbook.Save
          Next fils


          Additionally, consider querying the current workbook in the loop to avoid the overhead of opening the file and copying and pasting values.



          For Each fil In fldr.Files                                              
          ' QUERY CURRENT WORKBOOK
          Set oConn = OpenConnection(fil.Path)
          sSql = GenrateSQL("SELECT * FROM [Sheet1$]")
          Set oRs = GetRecordset(sSql, oConn)
          reportWorkbook.Sheets("TmpSheet").Range("A1").CopyFromRecordset oRs
          oRs.Close()
          CloseConnection oConn

          ' QUERY MASTER WORKBOOK
          Set oConn = OpenConnection(reportWorkbook.FullName)
          sSql = GenrateSQL("SOME QUERY FROM SHEET [TmpSheet$]")
          Set oRs = GetRecordset(sSql, oConn)
          reportWorkbook.Sheets("XYZ").Range("A1").CopyFromRecordset oRs
          oRs.Close()
          CloseConnection oConn

          reportWorkbook.Save
          Next fils


          Additional Notes:




          • It appears you are overwriting data in XYZ sheet with each iteration of loop if that is the intention. Add or change sheet to avoid this overwriting.


          • If your sheets have headers, be sure to copy recordset starting in A2 and loop field names starting in A1 (like your testing() subroutine). Or use static headers in row 1.


          • For bottom solution to query looped workbook, if sheet is not known in advance you will need to open document in loop to get sheet name and pass it into first SQL query.







          share|improve this answer





















          • XYZ sheet is vary based on input file.
            – Sonu Kumar
            Nov 20 at 17:44















          up vote
          0
          down vote













          From the post of your abbreviated code, the challenge you are facing is querying the very workbook you are processing which always reflects the snapshot of last saved instance.



          Consider incorporating a Workbook.Save method after processing each Excel file within the loop.



          For Each fil In fldr.Files                                              
          ...

          reportWorkbook.Save
          Next fils


          Additionally, consider querying the current workbook in the loop to avoid the overhead of opening the file and copying and pasting values.



          For Each fil In fldr.Files                                              
          ' QUERY CURRENT WORKBOOK
          Set oConn = OpenConnection(fil.Path)
          sSql = GenrateSQL("SELECT * FROM [Sheet1$]")
          Set oRs = GetRecordset(sSql, oConn)
          reportWorkbook.Sheets("TmpSheet").Range("A1").CopyFromRecordset oRs
          oRs.Close()
          CloseConnection oConn

          ' QUERY MASTER WORKBOOK
          Set oConn = OpenConnection(reportWorkbook.FullName)
          sSql = GenrateSQL("SOME QUERY FROM SHEET [TmpSheet$]")
          Set oRs = GetRecordset(sSql, oConn)
          reportWorkbook.Sheets("XYZ").Range("A1").CopyFromRecordset oRs
          oRs.Close()
          CloseConnection oConn

          reportWorkbook.Save
          Next fils


          Additional Notes:




          • It appears you are overwriting data in XYZ sheet with each iteration of loop if that is the intention. Add or change sheet to avoid this overwriting.


          • If your sheets have headers, be sure to copy recordset starting in A2 and loop field names starting in A1 (like your testing() subroutine). Or use static headers in row 1.


          • For bottom solution to query looped workbook, if sheet is not known in advance you will need to open document in loop to get sheet name and pass it into first SQL query.







          share|improve this answer





















          • XYZ sheet is vary based on input file.
            – Sonu Kumar
            Nov 20 at 17:44













          up vote
          0
          down vote










          up vote
          0
          down vote









          From the post of your abbreviated code, the challenge you are facing is querying the very workbook you are processing which always reflects the snapshot of last saved instance.



          Consider incorporating a Workbook.Save method after processing each Excel file within the loop.



          For Each fil In fldr.Files                                              
          ...

          reportWorkbook.Save
          Next fils


          Additionally, consider querying the current workbook in the loop to avoid the overhead of opening the file and copying and pasting values.



          For Each fil In fldr.Files                                              
          ' QUERY CURRENT WORKBOOK
          Set oConn = OpenConnection(fil.Path)
          sSql = GenrateSQL("SELECT * FROM [Sheet1$]")
          Set oRs = GetRecordset(sSql, oConn)
          reportWorkbook.Sheets("TmpSheet").Range("A1").CopyFromRecordset oRs
          oRs.Close()
          CloseConnection oConn

          ' QUERY MASTER WORKBOOK
          Set oConn = OpenConnection(reportWorkbook.FullName)
          sSql = GenrateSQL("SOME QUERY FROM SHEET [TmpSheet$]")
          Set oRs = GetRecordset(sSql, oConn)
          reportWorkbook.Sheets("XYZ").Range("A1").CopyFromRecordset oRs
          oRs.Close()
          CloseConnection oConn

          reportWorkbook.Save
          Next fils


          Additional Notes:




          • It appears you are overwriting data in XYZ sheet with each iteration of loop if that is the intention. Add or change sheet to avoid this overwriting.


          • If your sheets have headers, be sure to copy recordset starting in A2 and loop field names starting in A1 (like your testing() subroutine). Or use static headers in row 1.


          • For bottom solution to query looped workbook, if sheet is not known in advance you will need to open document in loop to get sheet name and pass it into first SQL query.







          share|improve this answer












          From the post of your abbreviated code, the challenge you are facing is querying the very workbook you are processing which always reflects the snapshot of last saved instance.



          Consider incorporating a Workbook.Save method after processing each Excel file within the loop.



          For Each fil In fldr.Files                                              
          ...

          reportWorkbook.Save
          Next fils


          Additionally, consider querying the current workbook in the loop to avoid the overhead of opening the file and copying and pasting values.



          For Each fil In fldr.Files                                              
          ' QUERY CURRENT WORKBOOK
          Set oConn = OpenConnection(fil.Path)
          sSql = GenrateSQL("SELECT * FROM [Sheet1$]")
          Set oRs = GetRecordset(sSql, oConn)
          reportWorkbook.Sheets("TmpSheet").Range("A1").CopyFromRecordset oRs
          oRs.Close()
          CloseConnection oConn

          ' QUERY MASTER WORKBOOK
          Set oConn = OpenConnection(reportWorkbook.FullName)
          sSql = GenrateSQL("SOME QUERY FROM SHEET [TmpSheet$]")
          Set oRs = GetRecordset(sSql, oConn)
          reportWorkbook.Sheets("XYZ").Range("A1").CopyFromRecordset oRs
          oRs.Close()
          CloseConnection oConn

          reportWorkbook.Save
          Next fils


          Additional Notes:




          • It appears you are overwriting data in XYZ sheet with each iteration of loop if that is the intention. Add or change sheet to avoid this overwriting.


          • If your sheets have headers, be sure to copy recordset starting in A2 and loop field names starting in A1 (like your testing() subroutine). Or use static headers in row 1.


          • For bottom solution to query looped workbook, if sheet is not known in advance you will need to open document in loop to get sheet name and pass it into first SQL query.








          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 20 at 15:57









          Parfait

          48.9k84168




          48.9k84168












          • XYZ sheet is vary based on input file.
            – Sonu Kumar
            Nov 20 at 17:44


















          • XYZ sheet is vary based on input file.
            – Sonu Kumar
            Nov 20 at 17:44
















          XYZ sheet is vary based on input file.
          – Sonu Kumar
          Nov 20 at 17:44




          XYZ sheet is vary based on input file.
          – Sonu Kumar
          Nov 20 at 17:44


















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Stack Overflow!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.





          Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


          Please pay close attention to the following guidance:


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53393531%2fadodb-connection-not-reflecting-current-data-set-fields-microsoftodbc-excel%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          404 Error Contact Form 7 ajax form submitting

          How to know if a Active Directory user can login interactively

          TypeError: fit_transform() missing 1 required positional argument: 'X'