DAO to pull an Access Query into Excel

[jo15765]

How would I set up via DAO to pull an Access Query into Excel? The reason I am wanting to use DAO is because in the query there is a LARGE memo field, and any other way I try to bring in the query it cuts off the Memo field.

[OnErrorGoto0]

Here is a very simple example

Please Login or Register  to view this content.

It requires you to set a reference to the DAO library.

[jo15765]

The query string..

Could I pull that directly from the SQL view from Access?

[OnErrorGoto0]

Just use the name of the view/query.

[jo15765]

That imports the data, but it is still cutting my memo field off...

[OnErrorGoto0]

Assuming you are not grouping on the memo field in your query, I suspect you may have to loop through the data rather than using CopyFromRecordset. GetRows may be an option, but you need to be aware that the resulting array is transposed from what you might expect.

[jo15765]

I am not grouping on anything. The only parameter other than "Not Null" I am using is, I am sorting Date descending. How would I loop through the data?

[OnErrorGoto0]

Roughly

Please Login or Register  to view this content.

or populate an array with the data and write that out to the sheet. If you have a lot of data it will be much faster.

[jo15765]

I think the largest that the query gets is 200 records.

Where would I add the code you posted above?

[snb]

it cuts off the Memo field

What do you mean: is not visible ? or 'stops the importing process' ?
Excel only shows a limited amount of characters in a cell, while that cell contains much more.
You can check that using msgbox len(sheet1.cells(1))

[OnErrorGoto0]

I think the largest that the query gets is 200 records.

Where would I add the code you posted above?

It replaces the

Please Login or Register  to view this content.

line.

[jo15765]

The memo field may actually contain 500 characters, while Excel only imports 250 (of course those numbers are not accurate just using for examples sake)

The way I have been importing the data, is copying from the query, right clicking in Excel, choosing paste special, then selecting CSV and it pastes all characters from the Memo field.

[jo15765]

It replaces the

Please Login or Register  to view this content.

.

WHen I use this code...I get variable not defined on j and n --- My question is what data type should they be? I defined them as String and then received a type mismatch on N

[OnErrorGoto0]

They should both be Long data types. Apologies - I omitted the declaration lines from the top of my code sample.

[jo15765]

I get a debug error of invalid operation on

Please Login or Register  to view this content.

[OnErrorGoto0]

Ah - remove that line - I forgot I was using a forward only recordset.

[jo15765]

Argh, it's still not pulling inn all the data from my memo field in access.

[Kyle123]

I don't think you can loop through datasets with this:

Please Login or Register  to view this content.

Try changing it to:

Please Login or Register  to view this content.

[OnErrorGoto0]

Does the query do anything to the memo field other than return it?

[Kyle123]

oops was looking at the wrong page :s

[OnErrorGoto0]

I don't think you can loop through datasets with this:

Please Login or Register  to view this content.

Try changing it to:

Please Login or Register  to view this content.

You can - but only once.

[Kyle123]

Ah right ok, my DAO is pretty limited I use ADO

[OnErrorGoto0]

Should be the same in ADO. If you use a firehose cursor for example, it's quick but you can only iterate the records once.

[jo15765]

It only returns the memo field. The only thing that could be considered quirky is the fact that I have date sorted descending.

[OnErrorGoto0]

I would probably need to see the database but it may also be a limitation of Office 2000, which I do not have.

[OnErrorGoto0]

@kyle123

In other news, your avatar has brightened my day considerably. A little like Baloo after a serious diet.

Just noticed: thank you for the rep, by the way.

[jo15765]

db1.zipdb1.zipLet me attach a database with garbage data, so you can test

[Kyle123]

@OnError brightens mine too, it frequently needs brightening


This gave me the full text of the memo field:

Please Login or Register  to view this content.

[jo15765]

I am getting Variable Not defined on

Please Login or Register  to view this content.

[Kyle123]

Try changing it to 3

[jo15765]

Now I am getting a debug error of Uknown is not a valid path Highlighting the cn.Open

Just to make sure I have it correct...

Please Login or Register  to view this content.

[Kyle123]

You need to change the file path to where your db is as well as the name of your db

[snb]

desired result with:

Please Login or Register  to view this content.

or using 'early binding':

Please Login or Register  to view this content.

[jo15765]

I had the Default Directory mapped wrong. I forgot one stipulation...how would I add into it where Date is Not null?

[jo15765]

SNB -- the copy recordset option would always cut off my memo field in the database, so at least the times I have tested it, option would not work.

[snb]

You didn't test my code, because I got the full 'comment' from your database: 475 characters long, what will be shown in the messagebox.
The crux is in adding the 3 in the instruction 'open' of the recordset.

Please Login or Register  to view this content.

[jo15765]

I get a compile error of variable not defined on the co1 line of your code snb

[snb]

Yes, of course: remove 'Option Explicit'

[jo15765]

It's still cutting off the data in my memo field using that code.

[snb]

So how come ? cfr. the attachment.

And what is being shown in the message Box ?

[jo15765]

The message box shows
14

Troyb@nist.gov

what is cfr the attachment?

That's very peculiar that my sample I provided works, but when I try to run it on my real database it doesn't....

[snb]

check in the real database what is the content of the 'comment' field in the first record.

[jo15765]

I am not sure what you mean by content...It is a comment, just a long typed out expression similar to what I provided in the sample database that was uploaded.

[jo15765]

I just figured out what the problem is. In the query it is set up to show: FullComments: "Customer Comments:" & [comment] And it names the field FullComments (which can drop I don't care about that) and then it appends Customer Comments: to the beginning of the actual comments in the database. I need to keep Customer Comments: as a prefix for the comment field, but it is clearly messing up the DAO/ADO import. Any suggestions on a workaround for this?

[jo15765]

I just wrote a function that would append "Customer Comments:" To the beginning of that column, so that is figured out now. The problem I have now is that Rows 1 and 2 are header rows, and each of the codes provided start pulling the data onto Row 2. What would need to be modified so that data does not begin until Row 3?