You are compressing too many operations into one statement here:
.Range("A" & a + 1).CopyFromRecordset cn.Execute(Sql)
Let me decompose this statement into steps a bit:
1) (Synchronously) Execute the string in "Sql" as a (presumably) recordset-returning statement on the ADO Connection "cn" <-- this is actually a complex multi-step procedure in an of itself, prone to numerous errors at each possible step, and with many options left unused which may improve the process or assure capture of all the possible data
2) pass the returned recordset as a parameter into the CopyFromRecordset method on the (calculated address) Excel range to populate the rows and columns with the returned data (note: not the headers) <-- this too can be an error-prone method
Step one presumes the type of recordset and record-locking to be employed from the connection object's default settings, along with default choices for the type of cursor used, which may not be correct in all cases (and the ADO recordset objects default settings, since you specified none of the many possible parameters for both objects in your code (your connection string parameters affect the underlying OLEDB driver, primarily).
Also, you create a recordset object in memory, RCS, and then never actually USE it (in the code shown). You should use it, and it's inefficient, generally, DIM the ADO objects "As New" - better to just DIM then and then let the subsequent code create them as required (for example the .Execute method of the connection object can and will create a new recordset object in memory for you automatically, and even if you did a "Set RCS = C.Execute(Sql)" before the .CopyFromRecordset line, what would actually happen is that the _existing_ recordset object in memory would be lost/destroyed, and a new one created to hold the incoming data, and at a different memory address).
There are many possible points of failure for the code as written, but you're kind of covering some of them over by overly-compacting your code. While it may well be "tighter" code this way, in this case it is not
"better" code since you can't trace the points of failure nearly as well.
One possible point of failure that should concern you most is that ADO recordsets won't necessarily fully populate on being opened, but upon opening may contain only partial data, especially if there is a large # of data pages involved in the returned recordset. There are quite number of other possible errors that the recordset and connection objects can spawn here, and it may be more difficult to determine the exact point of failure this way.
dear all,
i need import data in text files (alldocs) into spreadsheet excel (BCGD) using ado. But this code have a error. Please help me find the reason and fix for me.
Thanks in advance!
