Excel DAO import not working

[jo15765]

I am running this code to import from about 20 different databases. It works fine on all but 2!! This is the debug error that I get.
Run Time error '-214767259 (80004005)'
Method 'CopyFromRecordset' of Object 'Range' failed


And here is the code that I am running, I checked the database name, the file path and the query name and they are all correct, so I am at a loss as to what caused this to only go wrong on 2!

Public Sub GetAccessDataDAO()

  c01 = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Test\Test.mdb;"

  With CreateObject("ADODB.Connection")
    .Open c01

    With CreateObject("ADODB.Recordset")
      .Open "Select * from qry_check_time", c01, 3
      Sheets(2).Rows(3).CopyFromRecordset .DataSource
      .Close
    End With

    .Close
  End With
End Sub

Edit:

It is also the exact same query from the other databases (of course just pulling from a different table) and this query will run w/o issues, if I open the database and run it, but for some reason the DAO just isn't taking or something like that. Also, the workbook has 2 sheets so that is not causing the issue either.

[Kyle123]

A a guess there are 2 reasons this could occur, firstly you are returning more rows/columns than your spreadsheet has. Secondly, your recordset contains certain OLE object fields that you can't import into Excel, from MSDN:

Note When using CopyFromRecordset, you should be aware that the ADO or DAO recordset you use cannot contain OLE object fields or array data such as hierarchical recordsets. If you include fields of either type in a recordset, the CopyFromRecordset method fails with the following error:
Run-time error -2147467259:
Method CopyFromRecordset of object Range failed.

p.s Also you are using ADO, not DAO

[jo15765]

A a guess there are 2 reasons this could occur, firstly you are returning more rows/columns than your spreadsheet has. Secondly, your recordset contains certain OLE object fields that you can't import into Excel, from MSDN

One of the query's is returning 135 rows of data, and the other is returning 211...I don't feel like that is to much for Excel to handle? Also, it is the exact query that is being run in other datbases as well, and it can pull those results with no issues?

[OnErrorGoto0]

Just an additional point: you might find debugging considerably easier if you used some variables that you could inspect in the locals window (I realise the author prefers to simply write the shortest code possible).

[Kyle123]

Indeed, that snippet has snb written all over it

[OnErrorGoto0]

A little surprising though, as the Connection object seems to serve no purpose there (as the connection string is being passed to the Recordset's Open method, not the Connection object) and removing it would save four lines.

[Firefly2012]

What version of Excel are you using OP? Is it xl2000 as listed in your profile or an earlier version?

[jo15765]

What version of Excel are you using OP? Is it xl2000 as listed in your profile or an earlier version?

Yes I am running Excel 2000

[Kyle123]

Then as I mentioned above it could be what is being stored in the database. Is there anything different stored in the tables that aren't working than the tables that are, for example images etc

[jo15765]

Then as I mentioned above it could be what is being stored in the database. Is there anything different stored in the tables that aren't working than the tables that are, for example images etc

No there is only text stored in the table. No pic's, hyperlinks, or anything like that.

[Firefly2012]

Is it returning more than a single column of data? I would think in that case you would need to modify to:

Sheets(2).Range("A3").CopyFromRecordset .DataSource

However, I haven't tested that specifying the whole row might cause a problem (don't have Access).

[jo15765]

Is it returning more than a single column of data? I would think in that case you would need to modify to:

Sheets(2).Range("A3").CopyFromRecordset .DataSource

However, I haven't tested that specifying the whole row might cause a problem (don't have Access).

It is returning whatever the query holds, it may be one row, or up to 200 (which could grow, that is the highest number as of now).

[OnErrorGoto0]

Is any of the text longer than say 911 characters?

[jo15765]

Is any of the text longer than say 911 characters?

Yes. There is a memo field that is just Notes that are entered, and sometimes those can get very very lengthy.

[OnErrorGoto0]

CopyFromRecordset will fail with that much text. You will need to loop I think for that.

[jo15765]

DOH! Can someone show me an example of a loop I could use that would pull in the data?

[OnErrorGoto0]

Something akin to this (untested)

Public Sub GetAccessDataADO()
    Dim stConn As String
    Dim rst As Object
    Dim x As Long
    Dim y As Long

    x = 3
    stConn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Test\Test.mdb;"

    Set rst = CreateObject("ADODB.Recordset")
    With rst
        .Open "Select * from qry_check_time", stConn, 3
        Do Until .EOF
            For y = 1 To .Fields.Count
                Sheets(2).Cells(x, y).Value = .Fields(y - 1).Value
            Next y
            .MoveNext
            x = x + 1
        Loop
        .Close
    End With

End Sub

[jo15765]

Something akin to this (untested)

Public Sub GetAccessDataADO()
    Dim stConn As String
    Dim rst As Object
    Dim x As Long
    Dim y As Long

    x = 3
    stConn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Test\Test.mdb;"

    Set rst = CreateObject("ADODB.Recordset")
    With rst
        .Open "Select * from qry_check_time", stConn, 3
        Do Until .EOF
            For y = 1 To .Fields.Count
                Sheets(2).Cells(x, y).Value = .Fields(y - 1).Value
            Next y
            .MoveNext
            x = x + 1
        Loop
        .Close
    End With

End Sub

Running the above code, I get a RunTime Error 7 'Out of Memory' after it pulls in about 45 of 135 rows needed for the complete query

[jo15765]

In one database the code I was using pulled in the memo field and it has 1100 characters in it?! Strange!!!!

I will test the above code, thank you.

[OnErrorGoto0]

I do not know why that might be, I'm afraid, and do not have 2000 to test with.

[jo15765]

Possibly then bc this PC I am on is a huge POC! Thank you for the help!

[OnErrorGoto0]

That could be - plus Excel 2000 was extremely limited in the amount of memory it could use.

On the other hand, the fact that both methods fail on this particular query could be an indication of another issue. Do you have any error values in the query?

[jo15765]

Error Values?

[OnErrorGoto0]

Yes. For example if your query does calculations and you try to divide by zero, you get an error as the result.

[jo15765]

Ah okay. No there is nothing of that sort in the query. It is returning just basic data, like Name Address Phone # etc etc, no calculations or anything like that.

[OnErrorGoto0]

Oh well.

Can you just test this version

Public Sub GetAccessDataADO()
    Dim stConn As String
    Dim rst As Object
    Dim x As Long
    Dim y As Long
    Dim vaData

    x = 3
    stConn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Test\Test.mdb;"

    Set rst = CreateObject("ADODB.Recordset")
    rst.Open "Select * from qry_check_time", stConn, 3
    
    If Not rst.EOF Then
        vaData = TransposeGetRows(.GetRows())
        rst.Close
        For x = LBound(vaData, 1) To UBound(vaData, 1)
            For y = LBound(vaData, 2) To UBound(vaData, 2)
                Sheets(2).Cells(2 + x, y).Value = vaData(x, y)
            Next y
        Next x
    Else
        rst.Close
    End If

End Sub
Function TransposeGetRows(varData) As Variant
   Dim lngRow As Long, lngCol As Long
   Dim varOut()
   ReDim varOut(1 To UBound(varData, 2) + 1, 1 To UBound(varData, 1) + 1)
   For lngRow = LBound(varData, 2) To UBound(varData, 2)
      For lngCol = LBound(varData, 1) To UBound(varData, 1)
         varOut(lngRow + 1, lngCol + 1) = varData(lngCol, lngRow)
      Next lngCol
   Next lngRow
   TransposeGetRows = varOut
End Function

[jo15765]

On this line

 vaData = TransposeGetRows(.GetRows())

I get a compile error of invalid or unqualified reference.

[OnErrorGoto0]

Apologies - I rewrote some of it at the last minute. That should be

vaData = TransposeGetRows(rst.GetRows())

[jo15765]

Still getting an Out of Memory error. It will highlight this line of code:

Sheets(2).Cells(2 + x, y).Value = vaData(x, y)

[OnErrorGoto0]

What are the values of X, y and vaData(x, y) at the time?

[jo15765]

x = 62
y = 24

[OnErrorGoto0]

Revised version of the main code with an added error handler just to see how long the data is when the error occurs

Public Sub GetAccessDataADO()
    Dim stConn As String
    Dim rst As Object
    Dim x As Long
    Dim y As Long
    Dim vaData

    On Error GoTo oops

    x = 3
    stConn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Test\Test.mdb;"

    Set rst = CreateObject("ADODB.Recordset")
    rst.Open "Select * from qry_check_time", stConn, 3
    
    If Not rst.EOF Then
        vaData = TransposeGetRows(.GetRows())
        rst.Close
        For x = LBound(vaData, 1) To UBound(vaData, 1)
            For y = LBound(vaData, 2) To UBound(vaData, 2)
                Sheets(2).Cells(2 + x, y).Value = vaData(x, y)
            Next y
        Next x
    Else
        rst.Close
    End If
    
    Exit Sub
    
oops:
    MsgBox Err.Description & vbCrLf & "Current item length = " & Len(vaData(x, y))
End Sub

[jo15765]

I am getting a type mismatch error on this line:

MsgBox Err.Description & vbCrLf & "Current item length = " & Len(vaData(x, y))

[OnErrorGoto0]

Can you change that to

MsgBox vaData(x, y)

to see what the data is? I'm curious about the Type Mismatch there.

[jo15765]

Can you change that to

MsgBox vaData(x, y)

Still a type mismatch...In my limited coding knowledge, could it be because we have x and y declared as long and in that piece of code we are trying to make them be a string??

[snb]

Another way of checking:

stConn =

sub snb()
  with CreateObject("ADODB.Recordset")
    .Open "Select * from qry_check_time", "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Test\Test.mdb;"
    c01=.getstring(,.recordcount,"|",vbcr)
    .close
  end with

  with createobject("Word.document")
   .content=c01
   .windows(1).activate
   .windows(1).Visible=true
   .saveas "C:\Test\Test.doc"
   .close 0
  end with
End sub

Now you can see what has been imported from the database.

[jo15765]

Another way of checking:

stConn =

sub snb()
  with CreateObject("ADODB.Recordset")
    .Open "Select * from qry_check_time", "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Test\Test.mdb;"
    c01=.getstring(,.recordcount,"|",vbcr)
    .close
  end with

  with createobject("Word.document")
   .content=c01
   .windows(1).activate
   .windows(1).Visible=true
   .saveas "C:\Test\Test.doc"
   .close 0
  end with
End sub

Now you can see what has been imported from the database.

It is highlighting

co1

And telling me variable not defined.

[OnErrorGoto0]

No, but it implies that whatever you are importing cannot be converted to a string, which probably explains why all the code is failing.
How about

MsgBox Typename(vaData(x, y))

You can also test snb's code, though I suspect it may fail for the same reason.

[jo15765]

Going back to this line of code

MsgBox Err.Description & vbCrLf & "Current item length = " & Len(vaData(x, y))

I get a msgBox that shows
Out of Memory
Current Item Length = 1094

[OnErrorGoto0]

Add

Dim c01 as String

[OnErrorGoto0]

I thought you were getting a type mismatch there?

I cannot see an issue with 1094 characters unless perhaps it starts with an = or other operator symbol? (1024 is the maximum formula length)

[jo15765]

It was my silly fault, I left out the "." between the database name and mdb. Let me check all the entries in the database and see if someone type in a crazy character or something that is throwing it.

Could the field beginning with a "-" be causing the error? It is interpreting it as a calculation?

[snb]

I vaguely remember that having "ID" in the first field in the first record creates problems (MS has an article about it).

[jo15765]

Removing the "-" from the beginning of the fields is now letting the query pull in the data as needed. Thanks guys for the continued support, and patience.

[OnErrorGoto0]

You could also just prefix with an apostrophe in the code

Sheets(2).Cells(x, y).Value = "'" & .Fields(y - 1).Value