Solved: ODBC values into an array destination

[snicho]

I'm having trouble getting values from and ODBC connection into an array.
If I set the destination to be a range, then all works ok.

ie.This works fine:
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DRIVER={MyDriver};DBQ=K:\Path\Data;SERVER=NotTheServer", Destination:=Range("A1"))

This doesn't work:
Dim NoteValues as variant
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DRIVER={MyDriver};DBQ=K:\Path\Data;SERVER=NotTheServer", Destination:=Array(NoteValues))

Is there a limitation on Destination?
Does it have to be a range, or can it be an array?

Because I don't know how much data I might get on a given read of the database, AND because I need to write it to a worksheet on a single row rather than to a column, I was hoping use an array then write it to the sheet using application.transpose

The maximum data my query will return is six strings no longer than 50 characters each.

[mvidas]

Hi snicho,

From the vba help on the add method of querytables:


Destination
Required Range. The cell in the upper-left corner of the query table destination range (the range where the resulting query table will be placed). The destination range must be on the worksheet that contains the QueryTables object specified by expression.
One suggestion might be to create a new worksheet, add the query table there, and read the results into an array: Dim AnArray() As Variant
Application.ScreenUpdating = False
With Sheets.Add
With .QueryTables.Add(Connection:="ODBC;DRIVER={MyDriver};" & _
"DBQ=K:\Path \Data;SERVER=NotTheServer", Destination:=.Range("A1"))
'your querytable elements
End With
AnArray = .UsedRange.Value
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End
Application.ScreenUpdating = True
ActiveSheet.Range("wherever").Resize(1, UBound(AnArray, 1)).Value = _
Application.Transpose(AnArray)Just a thought. But you can't return the query table to an array, just not part of the object.
Matt

[snicho]

Thanks, Matt.

Thought that might have been the case - it's something I've wondered about previously but never found an answer.

At least this confirms I'm not missing something obvious!

I like to keep things in arrays as much as possible to keep my code running fast. Since the Destination has to be a range, I'll go down the path of writing to a separate sheet and transposing from there.

Cheers.

Steve

[mvidas]

I like to keep things in arrays as much as possible to keep my code running fast
Words I love to see! Have you considered not using a query table (which forces excel to use a destination range) but instead using ADO or something similar? I'm not sure how much query experience you have, but querying it directly instead of using the excel object could allow you to return the values to an array. You would just have to know a little bit more about the source and what you're getting.

[snicho]

Thanks for that, Matt. It's a while since I looked at ADO.

A quick check of my Favourites Bookmarks suggests I should pay another visit to Ole Erlandsen's site (http://www.erlandsendata.no/english/). He seems to have a fairly comprehensive collection of examples about this.

I vaguely recall that my previous attempts to make an ADO connection to this particular database via ODBC failed miserably. However, if it means that I can import directly into an array, then it's definitely worth another look.

Thanks again.

[Ken Puls]

Hi Snicho,

If you're interested, I have an ADO example at my site to Retrieve Data From A Database To Excel Using SQL. (http://excelguru.ca/node/23)

It can be modified to pull from an ODBC link (link at bottom of the page to a site that lists all kinds of driver strings).

HTH,

[snicho]

Thanks for your input Ken.

The notes on your site look to be very close to what I'm after - they combine the functions of using an array and transposing the result.

Between your advise and that of mvidas, I'm sure I'm heading in the right direction now. The only tricky part could be getting the connection string right for the database in question.

I hope to get this sorted out in the next day or two. I'll post back here then and let you know how I finish up.

Thanks again.

Steve

[Ken Puls]

Hi Steve,

From the site I link to in my article:


For Access (Jet)
oConn.Open "Provider=MSDASQL;" & _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\somepath\mydb.mdb;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
For SQL Server
oConn.Open "Provider=MSDASQL;" & _
"Driver={SQL Server};" & _
"Server=myServerName;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
Don't know if it will help or not. He also links to a couple more articles that may be of use.

Cheers,

[snicho]

Ken,

After much gnashing of teeth, etc, I have managed to modify the code from your page to suit my requirements.

I had to:
- dig up some additional connection string information from the original ODBC provider (I guess it DOES pay to go back to the orignal source);
- sort out some syntax problems with my connection string (removing an extraneous semicolon and also an extra inverted comma); and
- link the correct reference to my project (Microsoft ActiveX Data Objects 2.8 Library instead of Microsoft ActiveX Data Objects Recordset 2.8 Library)

Anyway, I'm now connecting and extracting information from the database ok.

I like this approach for retrieving external data. It looks to have a lot more flexibility.

Thanks again for your assistance!

[Ken Puls]

Hi Snicho,

Glad to be of help, although it hardly feels like I did much. You got the lion's share of the work on this one. ;)

Yes, every one of those issues would be important to resolve, no question. :)

With regards to the transposing... if you're using Excel 2000+, I believe that the copyfromrecordset already does it for you. If you're 97 or earlier, check out the TransposeDim function that's on my page. I didn't write that one, but it's a great routine. Lastly, if you do need to transpose, and you're post 97, you could try something based on this:

Sub transpose()
Dim myarray()
Dim c As Long, r As Long
Dim lcols As Long, lrows As Long

lcols = 3
lrows = 3
ReDim Preserve myarray(0 To lrows - 1, 0 To lcols - 1)
With ActiveSheet
For c = 0 To lcols - 1
For r = 0 To lrows - 1
myarray(r, c) = ActiveSheet.Cells(r + 1, c + 1)
Next r
Next c
.Cells(1, lcols + 1).Resize(UBound(myarray(), 2) + 1, UBound(myarray(), 1) + 1).Value = _
Application.WorksheetFunction.transpose(myarray())
End With
End Sub

Basically, just resize your target cell to the size of the (transposed)array and then use the worksheetfunction.transpose to drop it in there.

Let me know if you need any help with it. :)