Range.CopyfromRecordset skipping some cells?

[Isaac]

Has anyone experienced an issue with Range.CopyFromRecordset basically skipping placing the data on certain cells if continguous cells are broken up? And how you solved? ADO recordset here coming back from sql server.

It looks like maybe what it's doing is once it comes across a legitimate Null , it refuses to copy in any data to the right of that, or something like that, but not positive that's what/why happening.

 

[June7]

I tested this with data pulled from SQLServer. Do not get that issue.

 

[Isaac]

I'll post a sanitized version of my code and maybe you can see if you notice any difference with cursor type, cursor location, any defaults, recordset etc

I'm going to have to post a screenshot (sorry) not text, because it's from my work computer

Note - when I don't specify the adCursorType, it is coming back as Forward Only (I checked and confirmed).

I also don't feel I've experienced this issue before, but I have tested it in SQL Server Mgmt Studio, exec sproc 'param','param','param' and it comes back with 567 records, 19 columns, about 95% of fields have values in them. When I do rng.CopyfromRecordset rs , (or even rng.CopyFromRecordset rsMaster, as I can't remember why I ever separated RS and RSMASTER anyway, but doing it either way now), it comes back with the same 567 records and 19 columns in Excel, but towards the right a lot of cells are just missing all their values. On a brand NEWLY createed worksheet in code, as you can see from above codes. Very frustrating, maybe I am missing something totally different here in my environment that I forgot...Hmmm....head scratcher.

 

[June7]

Are you running this code in Excel or Access?

My test was just saving data to existing workbook and sheet with Excel VBA. Also, it was pulling from linked table, not connection set in code.

If I could copy/paste your code I would test it.

However, I have my own simple procedure in Access to create workbook. Now testing pulling data directly from SQLServer. It writes first record to Excel then errors "Method 'CopyFromRecordset' of object 'Range' failed". The recordset has 16 records. No idea why it won't finish writing to workbook.

Ooops, had to exclude OLEObject and Attachment fields from query. Now it works just fine.

I have never used NextRecordset. I will have to learn something new. Ok, got it, doesn't seem relevant to issue.

My recordset open:
rs.Open "SELECT UmpID, FirstN, Home, Age FROM Umpires;", DC, adOpenKeyset, adLockOptimistic

 

[Isaac]

yes running in excel. ok, i'm going to go over it again. i must be missing something. i'll play around with the code trying your method of rs.open instead of my cmd.execute, as well as keyset and lock options. Thanks! eventually something will break through ...

 

[June7]

Ran code in Excel. Didn't change anything. Worked.

Don't think I've ever used the Connection Command approach. Here's my procedure:

Sub RStoNewWB()
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook

Set xlApp = CreateObject("Excel.Application")
Set xlWorkBook = Excel.Workbooks.Add
xlApp.Visible = True

Dim DC As New ADODB.Connection
Dim rs As New ADODB.Recordset
DC.Open "DRIVER=SQL Server;SERVER=localhost\SQLEXPRESS01;DATABASE=Test;Trusted_Connection=True"
rs.Open "SELECT UmpID, FirstN, Home, Age FROM Umpires;", DC, adOpenKeyset, adLockOptimistic

xlWorkBook.Worksheets(1).Name = "Test"
xlWorkBook.Worksheets(1).Range("A2").CopyFromRecordset rs
End Sub