defining Column Reference with ADO connections using VBA

**NBVC** · 05-16-2008, 09:36 AM

Hi, does anyone know about ADO connections to Databases using VBA?

I have this code that works well.

It extracts Employee ID's and there Names from our database and lists them in columns A:C

What I want to do is list the ID's manually and get it to extract only the Names for the ID's I listed in Column A...

I am not sure how to amend this code to look at ID's listed in column A instead of extracting the whole database.

I know I need to add a "WHERE ID =" clause in the SQL but not sure how to say WHERE ID = A1, A2, etc...

Thanks.

Here's the code that works at extracting the whole database (it's not mine...I don't remember where on the web I found it):

Please Login or Register  to view this content.

**Richard Schollar** · 05-16-2008, 11:10 AM

Hi Vittorio

Assuming you have the IDs listed in col A of Sheet1 starting from A2 then you need to build up a string to hold the various IDs. Do this first by extrcating the IDs into a variant array:

Please Login or Register  to view this content.

Next we need to convert this variant array of IDs into a string of IDs bounded by single-quotes and commas:

Please Login or Register  to view this content.

Now you have your list, you need to incorporate into your SQL string. The SQL operator IN is the one you want to use, as you have multiple values:

Please Login or Register  to view this content.

Note that I think there is an upper limit to how longg strings can be when using the Open method of the Recordset object (with ADO). If you do run into this barrier, you can get around it, I believe) by using the ADO command object.

Another thing is I have assumed your IDs are textual above - if this is not the case and they are simply numeric numbers then you probably won't need the singlwe quotes around all the values.

Richard

**NBVC** · 05-16-2008, 11:26 AM

Hi Richard,

Thank you very much for coming to my aid. It is much appreciated!

This seems to have worked, except that it seems to reorder the results...

i.e. the results do not line up with the corresponding actual IDs listed in column A.

They seem to come out as the though the ID's have been sorted.?

Any way to fix this?

**Richard Schollar** · 05-16-2008, 11:40 AM

Well, you could use the Find method of the recordset object to loop thru the recordset and return the specific results. Let's say your IDs in the A column are contained in range variable rngIDs:

Please Login or Register  to view this content.

Richard

**NBVC** · 05-16-2008, 11:47 AM

Hi Richard,

You kinda' lost me here...sorry.

Am I replacing code or adding code?

Is rngIDs the same thing as vIDs or strIDs?

**NBVC** · 05-16-2008, 01:39 PM

Okay Richard...

I think I've got it....

This is the working code:

Please Login or Register  to view this content.

The only thing now is that it runs very slow as it goes through each line....

I am asking too much if I ask if it can sped up a bit in any way?

**Richard Schollar** · 05-16-2008, 02:56 PM

You can return all results in the recordset by using the CopyFromRecordset method of the Excel Range object ie:

Please Login or Register  to view this content.

would copy the entire dataset into the sheet in one go (and is very fast). This doesn't copy across headings (ie the field names) but this doesn't sound as if this will be a problem.

The significant slowdown is caused by you looping thru the recordset (Find is very slow) - I would hold the criteria on a different sheet/totally separate range and not worry about the return order (or sort the criteria and then sort the returned recordset, but you will still run into problems if not all IDs in the criteria range are present in the recordset returned).

Richard

**NBVC** · 05-16-2008, 03:12 PM

How exactly would I implement that in my code above?

**Richard Schollar** · 05-16-2008, 04:31 PM

That would be along the lines of:

Please Login or Register  to view this content.

**NBVC** · 05-16-2008, 10:04 PM

Thank you very much, Richard.

I will try this when I am back at work next week.

**NBVC** · 05-20-2008, 08:16 AM

Hi again Richard....and thanks again for helping me resolve this issue.

I tried your last code and it runs okay. The only thing is is that it relists everything in ascending order and it seems to consolidate my original list too (which may prove beneficial on another project I am working on

)...

Although this may work on the Employee list I originally talked about... I am trying to implement this on another, more complex project. The real project I am working on does not involve Employees. Instead, my original list is a multilevel Bill of Materials made up of subassemblies and component parts; the same part can be listed in several subassemblies. I need the costs lined up with the components... even if that component repeats...

If it is too much work to get the desired "faster" result..then not to worry... your original "Find" method solution works well...it is just a little slow....

**NBVC** · 05-26-2008, 02:15 PM

Hi again Richard and sorry to continue on this subject... but I have a slight problem with this solution now...

It won't let me run the program if my list exceeds 1000...

I get the error:

ORA-01795: maximum number of expressions in a list is 1000

Is there a workaround you can suggest?

I've read that you could split the list up and combine the groups with OR statements, but I am not proficient enough to figure out just how to do that in my scenario.

**NBVC** · 05-26-2008, 02:53 PM

Okay, so from reading up on this error in database forums, I was able to come up with something that looks like it works... it however, slows down the process significantly... if anyone could suggest a speedier way, I am all ears...

This is the amended code...

I created groups of 1000 and then concatenated then in the rsOra.open statement with OR statements....

Please Login or Register  to view this content.

**NBVC** · 05-26-2008, 04:02 PM

So it actually didn't work as I thought...

I had to separate the rsOra.Open statement into separate Case statements depending on group size....

Please Login or Register  to view this content.

**Richard Schollar** · 05-26-2008, 04:06 PM

Hi Vittorio

Not sure if this will improve matters, but try using the ADO Command Object instead - code will be similar to:

Please Login or Register  to view this content.

I'll be interested to hear if this works.

Richard

**NBVC** · 05-26-2008, 04:34 PM

Hi Richard,

Do I still need the vIDs and strIDs creations?

Would my Select statement be something like

Please Login or Register  to view this content.

where would I apply:

'then populate the recordset with:
Set rsOra = cmd.Execute()

How does it know where to actually put the data in my spreadsheet?

**Richard Schollar** · 05-27-2008, 03:35 AM

You are building your SQL statement in exactly the same way as you were before (only using one IN mind).

The code I posted only returns the recordset - it doesn't pass it to the spreadsheet (so you will need to apply it to a range eg with the CopyFromRecordset method we used earlier).

Your Select statement will be just the SQL string (no other parameters such as adOpenStatic etc) so just:

"SELECT * FROM table WHERE Id IN(" & YourListOfIDs & ")"

Richard

**NBVC** · 05-27-2008, 08:09 AM

Hi again Richard,

I attempted to put together the code based on your instructions, but am now getting an error...

-2147352565:[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

This is my code as it stands. Do you see where I went wrong? I am trying to populate column BK with corresponding Product Codes from my Database for the Parts listed in Column E.

Please Login or Register  to view this content.

**Richard Schollar** · 05-27-2008, 10:34 AM

I think it all looks fine - does it work if you use a very small dataset ie just two or three different IDs?

**NBVC** · 05-27-2008, 10:50 AM

No it doesn't, Richard.

I tried with 3 items and got same error.

**Richard Schollar** · 05-27-2008, 11:36 AM

In that case let's try this instead:

Please Login or Register  to view this content.

**NBVC** · 05-27-2008, 11:42 AM

Sorry Richard... same error occurs.. whether I use all records or just 3

**Richard Schollar** · 05-27-2008, 04:33 PM

Hmm, afraid I don't know why that is happening - possibly it is because of some setting in the DSN file that is being referenced (may be barking up totally the wrong tree though?!).

Have you thought about returning the records via a QueryTable in Excel (eg essentially thru MSQuery) - I believe this will be pretty fast and I know for a fact that you can use seriously big SQL strings.

Anyway, probably worth considering, but if you're happy with what you've got (albeit it being very slow) I'd stick with that.

Richard

**NBVC** · 05-27-2008, 04:40 PM

Hi Richard,

I am okay with what I've got now.. thanks.. yeah it is a little slow but it's not killer slow....

I am not sure what you mean by using MSQuery, though... that would pull all records.... I thought... can you use it to pull only records for items listed in a spreadsheet column already... (ie. could you look at items in column E and only pull relevant data into column BK)?

defining Column Reference with ADO connections using VBA

LinkBack

Thread Tools

Rate This Thread

Display

defining Column Reference with ADO connections using VBA

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions