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!

Please Login or Register  to view this content.

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

[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]

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?

[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

[Firefly2012]

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

Please Login or Register  to view this content.

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

[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.

[OnErrorGoto0]

Is any of the text longer than say 911 characters?

[jo15765]

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

Please Login or Register  to view this content.

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).

[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)

Please Login or Register  to view this content.

[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.

[jo15765]

Something akin to this (untested)

Please Login or Register  to view this content.

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

[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

Please Login or Register  to view this content.

[jo15765]

On this line

Please Login or Register  to view this content.

I get a compile error of invalid or unqualified reference.

[OnErrorGoto0]

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

Please Login or Register  to view this content.

[jo15765]

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

Please Login or Register  to view this content.

[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

Please Login or Register  to view this content.

[jo15765]

I am getting a type mismatch error on this line:

Please Login or Register  to view this content.

[OnErrorGoto0]

Can you change that to

Please Login or Register  to view this content.

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

[snb]

Another way of checking:

stConn =

Please Login or Register  to view this content.

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

[jo15765]

Can you change that to

Please Login or Register  to view this content.

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??

[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

Please Login or Register  to view this content.

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

[jo15765]

Another way of checking:

stConn =

Please Login or Register  to view this content.

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

It is highlighting

Please Login or Register  to view this content.

And telling me variable not defined.

[OnErrorGoto0]

Add

Please Login or Register  to view this content.

[jo15765]

Going back to this line of code

Please Login or Register  to view this content.

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

[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

Please Login or Register  to view this content.