Help understanding a MS Query code retrieving data from closed XL Files

Hi,
One of the reasons I started this Thread was as I could not get some of the codes I am looking at here working. Along the way whatever was wrong seemed to cause the other codes, ( which had been working previously) not to work as well. In preparing the Thread question all codes are working fine now... ??? I have no idea what caused me hours of grief.. ???
But that makes it even more relevant my second question / request for help.

I expect I need help from someone who knows not just VBA but some more deeper computing Queery type thingies .. The problems I have had already support I think my idea of wanting to understand as much as possible I can of the codes that I am using, ( even if I am barely equipped to in this case,.. Lol )

What I would like is if someone can walk me through the code snippet below and if possible explain in as much detail as possible what is actually going on whilst keeping it in terms a non computer professional like me can understand.


I will just give a brief description here, and the most relevant code snippet.
But if it helps I have done three help posts here:
https://www.excelforum.com/developme...ml#post4620322
https://www.excelforum.com/developme...ml#post4620597
https://www.excelforum.com/developme...ml#post4621266
( https://www.excelforum.com/developme...ml#post4621485 ( Codes ) )
Those are based on three full working examples with all codes , Files and further explanations. The posts are very similar. Each one corresponds to getting data from a different XL data file. ( Last post is just code for pen ultimate post )
I have had a good go at understanding as much as I can from the basic codes given to me by a macro recording, and have written a Function from scratch which seems mostly to work.

So this is what I an doing
_a) Get a deep column of data into a VBA Array
I know what the codes do do, because I wrote them and have been testing them a lot. The problem is I do not know what I am doing, or rather I do not understand much about how I am doing it. ...
I am looking here at just one of many ways to get data from some very large XL files . This way does some sort of Microsoft Queery thing. I can and do Google Queer stuff, but it is not really getting me where I want to be.
I found a video ( in Hindi ) , a “Get External Data from Excel files Using Microsoft Queery in Hindi video”. https://www.youtube.com/watch?v=_P07uPfv-ck I ran a macro recording while following some of the stuff being discussed there. I finally got a code that gets the data in a deep column of data and dumps it in a single “column” Variant Array .

This is the main bit I need help understanding. A few lines are probably obvious, but some look like Computer brain surgery to me. Therefore when something goes wrong I have not got a clue how to De Bug. ..

Here is the code snippet( actually it is all the code recorded by the macro recorder, but I have opened up some lines using extra line continuation, _ _ _ , bits. Ideally I would like some detailed explanation of what all the constituent bits are about. )

Please Login or Register  to view this content.

_._________-

Finally, I am experimenting a bit just now to see if I can get a Queery Code to
b) Get a row of data from the closed workbooks based on knowing the value in a particular row.
c) Getting the first 20 row x full columns range of data ( which is a multi spelling header range )

In the meantime I am experimenting with that using the macro recorder. I am hoping I might be able to do that by design , rather than by experimenting . If it is possible for anyone to include explaining how to do that in their explanations, than that would be a bonus.


( I am not too concerned in this Thread in the alternative ways of getting my data, as I am considering that in parallel. I am interested in this Thread, in understanding the Queery type of way )

I often find that some of these things are barely understood fully by anyone. So if more than one person can give me their “take” on this, then so much the better.

Thanks
Alan

( I am currently using XL 2007 for this work. – I have XL 2010 also, but I would need any solution to be compatible with XL 2007 I do not have Power Queery . ( And would know even less what to do with it ) )

Hi AB33,
_ You have three almost identical Posts ???. So I am not sure if I have what you intended posting ???

I think you may be telling me something along these lines:

1.
This bit:_....

Please Login or Register  to view this content.

_.. is somehow getting me a table.
Possibly you are saying that the arguments there of SourceType:= and / or Source:= are somehow ...” making a connection...” ?? and ( ............ ) is the connection string ??
( Destination:=Range("$A$1") is fairly self explanatory I think – it is top left of where the table is pasted.

2.
This_..

Please Login or Register  to view this content.

_.. is somehow doing something Queer but as Queery things go, quite straight.. forward: It is giving me one column, where _ “Testdatensatz_BLS_3.0 Name Leng” _ was my Worksheet name ( ShtName ) __ “Shrt_Desc” _ was the Heading ( first row value ) in that column.
So, as in my attempt at rewriting the code here Function ValuefromQueryListTableWonk https://www.excelforum.com/developme...ml#post4621485 , I already do this_..

Please Login or Register  to view this content.

_.____________________________

So I think I get the general jist of what is going on
BUT
I need to understand in a bit more detail, such that I could, for example, modify that Command Text to get _b) a single row from my data and _c) the entire first 20 rows.


And as always, as I am using this code in a real application, I would like to understand in as much detail as possible the first 1. bit. In doing so I might be able to understand enough to get the code to work on a text file, which is another possibility I could then consider.

So I am still looking for some help there to both understand and / or get the modified code line for the getting the data from rows and the first 20 rows.

( By the way I am looking in parallel at the whole ADODB way of doing this. But I do not understand what the difference is in all this ADODB, ODBC, and god knows what else.. it is all like brain surgery to me. Still looking for some help here from the informed.. )


But Thanks AB33 for your contribution. ( Hope you get your Internet Explorer ExcelForum access problem sorted, - there has been some other mucking about recently with the Log–in stuff: https://www.excelforum.com/suggestio...t-it-does.html , but I have no problem currently with Internet Explorer. Like all other ways to log–in , it currently takes a few goes sometimes or you have to try accessing different parts ( use different URL links ) until you find one that gets you in ) )

Hi,

You don't actually need those arrays. Your code is more simply put

Please Login or Register  to view this content.

SourceType:=0 is specifying that the source is external (xlSrcExternal = 0)
Source:="ODBC;" the options for this are ODBC, OLEDB, Text, and URL depending on the type of query.
"DSN=Excel Files;" this is the DSN (data source name) that you selected while building the query
"DBQ=..." the actual source data file
"DefaultDir=..." the default directory

the driver, buffer size and timeout you should not need to worry about.

For the command text, you have a simple
SELECT [table alias].fieldname FROM [table name] [table alias]
syntax which is fairly standard SQL. For MSQuery you enclose the table/alias name in `` rather than [], and the $ needs to be added to the sheet name as that is how the driver sees sheet names.

I hope that is of some use.

Originally Posted by AB33

..I am unable to reply until the access issue is resolved.

- I geused you were having problems after I saw youur thread .. https://www.excelforum.com/suggestio...my-access.html ... hope it gets sorted for you... Alan

Hi
Thanks Don that info was very helpful.

The Arrays were confusing me, - I guess just some quirk of the macro recorder and / or putting arguments in order in ( , , , , ) place order thingies in a ( __ ) instead of named Arguments which I personally like to use as often as possible instead..
Or maybe rather SQL stuff pseudo like ; ; ; ;

??? maybe in this case it is just a case or like
Array(Array(“a” , “b” ), Array( “c” , “d” )) looking just like
{“a”, “b”, “c” , “d”}
looking like
“a”, “b”, “c” , “d”

But there could be good reason for doing that.... If you look at ( https://www.excelforum.com/developme...ml#post4615067 ) you can get some weird results ... and then
Array(Array(“a” , “b” ), Array( “c” , “d” ))
Can come out like
{“a”, “b” ; “c” , “d”}
So maybe those separators _ ; _ are causing the macro recorder to through a paddy and chuck out those Arrays....

Please Login or Register  to view this content.

So anyway
I see that a ; is something like a , in VBA , or probably more like a ; in Debug.Print which sort of takes you to a new line for the next Named argument. So really I should have trigged to that and broke down my string better initially, do i will, do that now.. or rather.. ..._..
_..... But first, I note a few further things after a bit of further experimenting.
_ the macro recorder seems to make a peculiar split of the directory path putting the two split bits into a different Array.
_ I need for the File name the Full Path and File name, which begs the question why it needs to know the default Directory, ( which by the way only needs the Drive I find, so H rather than H:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWb ) . But I have seen this before in using stuff like ShellApp.BrowseForFolder where you have to re tell it where to start, so there is some consistency there ( The code will take the Drive Letter, the Full path to the default Folder, or even the Full File Name and Path )
_ I do not seem to need the vbCrLf between the SELECT and FROM. Possibly another macro recorder oddity.. ??
_.. – Hence with all that I was really confused _...

_...... so I had a good think and started to modify the code, but..

But I was not done yet unfortunately.... still trying to get clued up so as to do b) and c) – 4 hours of playing manually got me nowhere....But I had / have a theory...

I guess I may just have to accept that this Queery stuff is some interface I can use which based on a liberal sprinkling of fairy dust passes me all the information that, once the SourceType:=xlSrcExternal is set in a VBA List Object table thing_..........
( _.... A list object table thing is a Collection object optimised to produce a table, so like the rows are maybe like the Items of it. ( https://www.thespreadsheetguru.com/b...ent-2098248289 http://www.excelforum.com/excel-prog...ml#post4112203 ) ) _....will accept its second argument as the Source:= argument .. ... This second argument in the .Add Method of a ListObject would usually be a Range, but .....”..... when SourceType = xlSrcExternal. An array of String values specifying a connection to the source, containing the following elements: _ 0 - URL to SharePoint site ___ 1 – ListName ___ 2 – ViewGUID.........”

_....................

So I think I have almost my answer to b) and c) ....( get the thing modified for the getting the data from rows and the first 20 rows ).
_... The point of this Queery thing is actually in this case an Argument option within the creating of a VBA ListObject. '2b) This will create an unfilled table in a worksheet, and set information on where the information is.

I am guessing a .QueryTable Property thing '2c) actually “makes” the connection, so then I can go on and '3a) pass the Command Text to say what info I want.

The '3c) .Refresh updates the table. ( In between those steps '3b) I set some other , probably mostly optional, arguments

So the reason I got nowhere after hours of trying to use this way to get at rows from the table is the following: I missed the point of what was going on. What I would need to do is to change the list table to the whole data range in the closed data workbook and then work on that List table to get rows from it.
That approach is probably not possible in my application, as the size of the data is such that the
'3c) .Refresh line would take a Day and my computer would melt in the attempt...


But anyway , for completeness... , I have this now in my Function version of the code which at least shows a bit better what is going on..... ( https://www.excelforum.com/developme...ml#post4622636 )

Please Login or Register  to view this content.

I may have this almost Solved, but will not mark it as such yet, as I might have some last follow up questions to wrap it up. ( Any comments of better explanations than mine to what is going on here in the code is very welcome in the meantime.)

Thanks
Alan

The arrays are added by the macro recorder as a throwback to the time when the 255 character limitation applied to the string arguments. You could get around that by passing an array of string components that were then joined to create the full connection and sql query.

To restrict the data returned, you need to add a WHERE or TOP clause to your SQL query. There are plenty of online SQL resources.

Originally Posted by xlnitwit

..arrays added by macro recorder .. throwback to when the 255 character limitation applied to string arguments..

Thanks, that makes sense. – I have hit this 255 character limit on a lot of things ..( https://www.mrexcel.com/forum/excel-...er-limit.html? https://www.mrexcel.com/forum/lounge...ml#post4276500 )

Are you saying that the limit does not apply currently to such SQL thingy strings ??, ( I cannot check easily as I would not know how to make a long syntaxly valid SQL string )
_..._______________________________

Originally Posted by xlnitwit

..To restrict the data returned, you need to add a WHERE or TOP clause to your SQL query. ....

?? Hmm – I wonder if that might have any effect on what I am doing here or not?..._..
_... For example This code: ( Sub TestAQueryListObjectColumnAndDeleteWonk2 , Explained here: https://www.excelforum.com/developme...ml#post4620597 , _...
_.....Uses this closed data File (which has about 8810 rows that I am interested in)
Data File: “DBSept2016 - first AlanSucces.xlsx” http://imgur.com/D9knusB
Download File: https://app.box.com/s/j4ssuud642l1dr5tp689a080y7ustl7t
( The code , Sub TestAQueryListObjectColumnAndDeleteWonk2 , is also in Module “DBSept2016” in this File: “WBAccestTimeTest1.xlsm” Download File: https://app.box.com/s/vj2z43orwbobv0kpv0mwdcw9vnt096jl )

On running that code, Sub TestAQueryListObjectColumnAndDeleteWonk2 , it seems to get only down to 8810 rows anyway... ( It gets that Just before the Rem 4 Option Incinerator which deletes that column and ListObject after I have got the data into an array )
OnlyGets8810RowsInAQuerrListObjectTableThingyAnyways.JPG http://imgur.com/etFgKi0

So I am wondering if this Queery thingy way would not be effected by any WHERE or TOP ? ( I will be looking at some ADODB alternatives for this tomorrow, and possibly the WHERE or TOP might be more relevant there ??? )

Thanks for the extra input.
Alan

SQL is the same whether you are using a querytable or ADO- it's still a query.

Connection and CommandText (which used to be the deprecated SQL property) can take much longer strings than 255 characters.

Hi Don

Originally Posted by xlnitwit

SQL is the same whether you are using a querytable or ADO- it's still a query...Connection and CommandText (which used to be the deprecated SQL property) can take much longer strings than 255 characters.

-I follow that now, thanks for that.

Regarding the WHERE or TOP stuff:
Are you suggesting that if I figure out how to do the WHERE or TOP, then somehow less data will be got ( or " revealed " / “ made available " or whatever at some point in the code, so that might have some effect, such as to make my code run quicker?? ( It takes about 8 - 18 seconds ( not sure whay it is so inconsitant yet ) using a fairy old machine with XL 32Bit 2007 – ( But at this stage I am mainly looking at comparing different ways to get the relatively quicker one ) )
Alan

I don't imagine you will get a particularly large speed difference when querying Excel workbooks as there is no proper database involved and you will not have the benefit of indexing on your fields, but it should be a little faster. It is always a good idea to restrict a query to only the data you actually want.

Originally Posted by xlnitwit

I don't imagine you will get a particularly large speed difference when querying Excel workbooks as there is no proper database involved and you will not have the benefit of indexing on your fields, but it should be a little faster. It is always a good idea to restrict a query to only the data you actually want.

Thanks, I always have an instinct to just get / look at the data I need, but have no idea , ( yet ) , how all this queery SQL stuff does that. I'll have a go ( I already have a prelimanary summary to all what is going on in my code... ( https://www.excelforum.com/developme...ml#post4620321 ) .. I will post a summary here for posterity, ..... once I have it all clear... )
Alan

Hi Don, ( or anyone who can help with a Microsoft Query SQL command Text syntax )
I am having trouble getting the syntax correct for my Query.....

This is my current SQL Queery thing Command string:

Please Login or Register  to view this content.

This gives me finally a ListTable object “Table” type thing with 8810 rows , ( That is how many rows of data I have in my closed data file __ “DBSept2016 - first AlanSucces.xlsx” http://imgur.com/DE3sgk0 http://imgur.com/D9knusB
Download File: https://app.box.com/s/j4ssuud642l1dr5tp689a080y7ustl7t
( The Worksheet I am interested in has the Name “Tabelle1”. At the top of the first column in that worksheet I have “NDB_No” )

I am trying to experiment with reducing the rows queried. I cannot seem to Google or get from experimenting the correct syntax.
For example, to try just 1000 rows, I tried variations of these

Please Login or Register  to view this content.

I get a sql syntax error ( This occurs at my code line QuerTbl.Refresh BackgroundQuery:=False )

Can anyone give me the correct syntax? (or alternative to limit the rows ( records ) )

Thanks.
Alan

TOP XX should be right after SELECT.

Please Login or Register  to view this content.

Another way to shrink it is via WHERE clause after FROM. Using condition based on some column.

Hi CK76
Thanks for the reply
I have tried that string and a few variations of it ( including with Added space between 'NDB_NO' & FROM ), but I get an “Application or Object defined” error at the .Refresh code line ...
Alan

Hi CK76
Thanks so much for that great alternative.
I mentioned previously in this Thread that I was looking at ADO stuff as an alternative in parallel to this Thread, and was going to get on to that later. I have some similar looking codes to the one you just gave me already.
When I move on to that , I will take a look at that code of yours in detail and let you know how I get on.
I did, however, try the code out briefly , ( I changed the path name for my data file to filePath = ThisWorkbook.Path & "\DBSept2016 - first AlanSucces.xlsx" )
I can confirm that it gives me the entire file , ( all columns ) up to row 1000, and very quickly, so that’s great.
-.______________________


I wonder if this may be telling me that the SQL code lines of this form

SELECT TOP 1000

Are only applicable for codes which give me the entire row ( all columns ). ???

My Query code is , as I require for now, giving me just the column with heading “NDB_No”,( the first column ).

Once again I emphasise that in this Thread I wanted to concentrate on my MS Query code version.
But I am very grateful for you taking the time to do that code for me.

Thanks
Alan

P.s.

Originally Posted by CK76

...
You can change strQuery to something like below to just grab data that starts with "Chicken" for [NDB_NO].

Please Login or Register  to view this content.

This could be a very nice alternative idea for my final requirement..

Hi CK76

Originally Posted by CK76

Hmm, try below in your MS Query code. Regardless of how many columns you are querying. You can use SELECT TOP statement.

Please Login or Register  to view this content.

Either of these:_....

Please Login or Register  to view this content.

_....
Give me this:
Top100MsQuery.JPG http://imgur.com/UBXVwpU

Which is what I wanted

Thanks you very much !
Alan

Originally Posted by CK76

Ah got it to work.

Please Login or Register  to view this content.

And these all work too

Please Login or Register  to view this content.

Great !
Thanks so much
Alan


I have no idea how the last CK76’s solutions work or what they are doing.. but I will have a long think about it.. –
_... I suspect that somehow the Aliases were not working As their aliases should , and somehow CK76’s solutions have got them to get their arses working , As their arses should... just Arsing about

Hi,
In this post I am updating my current code based on the last few inputs. I expect now I am close to a working code that I can understand, which is the point of this thread. So I will try to summarise and explain the working code as it stand now for my and anyone else’s future reference.
If anyone has any comments or suggestions for improvements , or in particular, better explanations of what is going on then please do reply . Thanks a lot.

_..................____
Current Code (s)
I have posted two code versions, _..one here
https://www.excelforum.com/developme...ml#post4626004
_.. and one here
https://www.excelforum.com/developme...ml#post4626005
( along with some preliminary stuff required for the Timers used ****....
They are both the same code ( Functions ). One has a lot of other stuff there for my benefit to remember what went on and to give me something to experiment with should I come back to it later.
The other is tidied up a bit might be easier for anyone else to follow. I have also added a calling routine and given links to the Files to make it a standalone, that is to say, all he info and some test data is there to get the codes up and running
( **** Preliminary stuff here: https://www.excelforum.com/developme...ml#post4625998 )

What is going on
This code does what Excel does when you click around here ExcelMSQueery123.JPG http://imgur.com/UuRtnkw
It is explained well here, even if it is in Hindi https://www.youtube.com/watch?v=_P07uPfv-ck
So I am bringing into Excel data ( a long column in my case ) from a external closed data File.
I did all that while doing a macro recording. This Thread is about tidying that up and understanding it a bit.
It seems there two main things going on.
_ An Excel VBA ListObject thing. ('2b) )
That is a lot of stuff to make handling tables in a spreadsheet easier and better

_ SQL Stuff ( '2b)(ii) 3 )
SQL (Structured Query Language) is a sort of language for getting data which has been organised along some internationally agreed rules of organising and layouts. Excel files and simple text files are examples of software which organises any data in such a way that it conforms to and so can be accessed via typical SQL commands. Some minor stuff here:
_.......Excel has a strange ` ` usage in places where SQL stuff has [ ] . ( Possibly an attempt not to confuse Excel things like [myWorkbook.xlsx] if they are used in SQL stuff strings.) It seems you can use ` ` or [ ] conventions for any SQL statement. You can mix the conventions also , but not in an individual statement.
_.......sometime the macro chucks out a long string split into Arrays due to a previous 255 character limit. Usually you can just take each element string bit and stick the string together as a long single one.

Code Progression
Rem1) This was string manipulation of data File name, path , directory etc. With Hindsight I can get easily everything easily as and when I need it from the Full File name and path which I take into the Function anyway.
_..similarly
Rem 2) Line continuations _
‘2a) Some stings..
_.. I confess it was quite convenient in this Thread to take the advantage of the _ line continuations to list out neatly the named arguments and the pseudo named arguments within the second argument of Source:= , but with hind sight I decided to do away with that. For one thing I think somewhere along the line in VB it was not available and I like to keep things as backward compatible as possible, and I live for the horizontal scroll bar anyway. Keeps ya fit, scrolling.. .. along to the right
The most of these “connection strings” are hard coded, so you just get used to it , I suppose, or keep a set of them stried somewhere and just copy in when you need them....

So the last two code sections I have mostly done away with.

'2b) The object calling up table thingy VBA Excel ListObject (Get and / or assign the information ( data ) ( Method.Add(__) ) )
We are doing queer ListObject table stuff. That is a less usual use of an Excel ListObject thing.
Just as an aside:- More usually, argument of SourceType:= xlSrcRange means the source , Source:= , of the data, would be a spreadsheet range, and you do not specify explicitly a Destination:= TopLeftWhereItShoudBeArgument ( if you do, then it is ignored. )
The Method to do that is the .Add( __ with those main arguments of SourceType:= xlSrcRange and Source:= the range from and where it is ) .
Having got this far for a simple range as source, the “Table” would now “be there”, where the “Table” is defined as the source:= range. ( Actually, you can even leave out the Source:=range. ... The ListObject is a collection object of a Worksheet so if you just do like WS.ListObjects.Add, then Excel will look for and guess where a table is. )
( If the range used is given a , name, defining it as a Named Range object, then adding, for example rows, to the list object will change the RefersTo:= range of the Named range appropriately. That is just something that the ListObject is “intelligent enough” to do. Often the named range is given then a name like “Table1” which in my opinion confuses a bit if you have not understood what a ListObject is all about. I think it would be better to use a name like “RangeThereaboutsUsedFortheSouceArgumentInAListObjectCreationSAowhichWillReferToAnIncreadsedRangeAreaIfYouUseLikeARowAddtypeMethodToTheaforesaidListObject” . At least that is what I would call it for the love of God and '_- Explicit Pedantry . In a way you can think of a parallel to the ListObject in its more typical use and a Range object: The Range object is the thing “behind” or associated with a cell or collection of cells, the ListObject is the thing “behind” or associated with a Table. The only thing that gives an indication in the spreadsheet of a ListObject is a small little indicator at the bottom right cell of a Table range to indicate there is a table. https://www.thespreadsheetguru.com/b...t-excel-tables . )

'2b)(ii) '2c)
The logic in the way Excel chooses to go about using a ListObject in the less common queer way is a bit confusing in my opinion: Using in the .Add argument ('2b)(i) SourceType:=xlSrcExternal , arranges that the '2b)(ii) Source:= argument will accept SQL type commands
So with SourceType:=xlSrcExternal , the named argument of Source:= will contain a pseudo subset of named arguments relating to geting at the data file through / with SQL stuff. They are separated by a ; . The order mostly does not seem to be important, apart from that the ..”... ODBC, OLEDB, Text, and URL depending on the type of query.... “.... should come first.
( The named arguments in the .Add(_____) can come in any order. That is a sort of VBA convention I think. )
So using SourceType:=xlSrcExternal , therefore causes the ListObject.Add argument , Source:= , to expect the appropriate commands which are conventionally referred to as the “connection string” ( https://www.connectionstrings.com/qu...-syntax-error/ ).
This connection string takes different formats. For the case of usage in a ListObject there is a required syntax , which is somewhat less encompassing than many: I think the format is somehow designed to make it available but not actually open it yet. I think the idea here is to allow SQL commands to be sent later to define exactly what data is needed.
What I find confusing is that for some strange reason it is not enough yet. The ListTable Object of this kind is defined but a “connection” is yet to be made. Why this is not always done in this case based on the argument options is a mystery to me.
It appears that a further step
'2c) Dim QuerTbl As QueryTable: Set QuerTbl = LstTbl.QueryTable
is required to ”open the connection”.
A Property .QueryTable sort of provides a link along which later SQL commands can be sent to return data to the ListObject. ( In other ways of utilising SQL stuff ( as I do later in ADODB based codes ), the commands are sent, I think, sort of as the connection is made )
_.... I would have thought the Source argument combination would have resulted in this being done already

At this point, as a consequence of the ‘2b) .Add(SourceType:=xlSrcExternal ______ ), followed by the '2c) Set QuerTbl = LstTbl.QueryTable “activation / opening” we are sort of hanging in the air waiting for something to be done.. I expect this sort of “drawn out” way is appropriate for a ListObject which is pseudo version of a simple fixed range type table. This is possibly why we can then ... repeatedly....over and over... do what the next code section does:

'3) do stuff continuous over and over. With the QuerTbl object thingy
Another way of thinking about it, through ‘2c) , we now have a Queer / Bent Dyke Table as opposed to a normal straight one from like a fixed range.
So we go and do a bit further after the ListObject object Add(___) which would have normally in the straight range case have sufficed.
We applied to the current pseudo ListObject Table a .QueryTable Property , '2c) , which kind of returns us a object of a rather Queer Table as it could thought as being a bit dynamic in terms of its contents.
( A normal Table is also may be dynamic in that it can have rows, columns added, etc.. – Bit that is a more restrictive and is keeping a certain basic form.)
For a Queer table you can continually enquire for information, then a refresh is going to sort of rebuild the whole table, based on a few other basic minor properties, ‘3b). ( For ‘3b) I think they are fairly self explanatory, or I will realise what they are as I go along. )

'3a SQL Command stuff
The first thing to do typically at this point is to prepare SQL commands which will be sent to
'3a(ii) aliarsing about
This was the new bit in this latest code version. To limit the rows ( records ) the TOP sql thingy could be used. We had to play around with the syntax a bit as the previous command could not seem to have a TOP easily included. I am wondering if that previous command, of
SELECT `Tabelle1$`.`NDB_No` FROM `Tabelle1$` `Tabelle1$`
Was like OK for the simplest case , but was not quite of this form and was not really in a aliarse form, like sort of:
SELECT [table alias].fieldname FROM [table name] [table alias] ( what I mean is, there is nothing aliarse about this: Tabelle1$ As this Tabelle1$. I do not understand what this is all about, but
With SELECT `Tabelle1$`.`NDB_No` FROM `Tabelle1$` `Tabelle1$` I could not seem to find a snytax to add a TOP xxxx to get just the first few xxxx rows ( records )

Maybe the answer to that little problem was, as they say in the trade.. if you don’t getcha self aliarsed then don’t expect to be able to pull out your rows.... - actually, I made that up, but maybe it is a pre requisite to aliarse in order to pick out your rows
.. Anyways, these alternatives from CK76 all seem to work,_...

Please Login or Register  to view this content.

_... then after that , you can add to any SELECT a TOP xxxx and Billy Ho , there you go, all is well, well sort of, - it don’t seem to make a big difference to speed but maybe it will later with my data in text Files as an alternative ??? (EDIT##: I will check all that as a separate excessive in the next post , I think )

Originally Posted by xlnitwit

don't imagine you will get a particularly large speed difference when querying Excel workbooks as there is no proper database involved .....

.. ??
So anyway: '3a(iii) ' Put ya aliarse in and get ya rows

Please Login or Register  to view this content.

'3c)' The refresh
This will finally do the business.. making the table as we wanted it appear.

That all above covers the main point of this Thread.
( A few other things are then done: Rem 4) puts the data in an Array and then Rem 5) trashes the column of data. Rem 6) organises that the value returned by the Function gives information as to the time taken for the main working part of the Function.

_._________________________________________

##The next post I might just discusses a bit more some findings of the TOP experiments if I can get at a few computers to get a good spread of typical results

This is a short follow up to the last post.
Mostly it is concerned with looking at the effect of including a TOP xxxx bit in a sql command line.


Here are some Timings for the two functions discussed in the previous posts.
Functions here:
Function FukMeHeadLongUsingValuefromQueryListTableDon1
https://www.excelforum.com/developme...ml#post4626004
Function FukMeHeadLongUsingValuefromQueryListTableDonCK76
https://www.excelforum.com/developme...ml#post4626005

I am using the Calling
Code and Timers:****
https://www.excelforum.com/developme...ml#post4625998

Data File ( has 8810 rows of data )
"DBSept2016 - first AlanSucces.xlsx" http://imgur.com/D9knusB Download File: https://app.box.com/s/j4ssuud642l1dr5tp689a080y7ustl7t

File for output Main Workbook ( Also has all the codes in it ###)****
“WBAccestTimeTest1.xlsm” Download File: https://app.box.com/s/vj2z43orwbobv0kpv0mwdcw9vnt096jl
###The current codes in the file may be doing slightly different formatted speed tests to those in the referenced codes

The first sql command string option , SELECT `Tabelle1$`.`NDB_No`FROM `Tabelle1$` `Tabelle1$` I have in Function FukMeHeadLongUsingValuefromQueryListTableDon1
For the other three I use the second function Function FukMeHeadLongUsingValuefromQueryListTableDonCK76 and modify or ‘comment out the penultimate line in this code snippet from that function

Please Login or Register  to view this content.

The results are averages from many measurements, but having watched the progression and seen the typical progression and inconsistencies from one run to the next, I think there is very little in it. ( As always at this stage, only the relative measurements are of interest in comparing . The actual times could be dependent on many factors .I am interested only in comparing to find the best way of doing my final requirement )

Certainly for the Excel data file reading no great sped advantage of including TOP. This was suggested / expected I think. It may be more relevant when I look at other alternative ways, possibly with reading from a text File with this method and / or using ADO techniques as I intent do shortly??

????? I got crazy inconsistent results with one of my computers, from 5 – 30secs with no clear pattern so I have left them out for now.
I do not understand why I got such inconsistent results with that last computer. ... maybe it is broke !! – I will check again once I have other code types up and testing!

So I think the conclusion probably from this way is that the queer stuff I looked at it is for small pretty things.. and not likely to be much use to me...
But I have got it covered for completeness, and I expect a lot of what I have learned here will help on the ADO attempts I will begin shortly

I think this Thread is pretty well Solved for me now, but any comments or suggestions for improvements , or in particular, better explanations as those to my last post are very welcome.
Thanks again AB33, Don and CK76

Alan


Current Results, - I may add to or edit later

sql Command QuerTbl.CommandText	Acer Aspire 4810TZG 32Bit 4GB RAM Vista XL 2007 32Bit	Dell 64 Bit Win7 4GB Ram XL 2007 32 Bit	Acer Aspire 7535G 32Bit 4GB RAM Vista XL 2010 32Bit	Acer Aspire X3200 32Bit 4GB RAM Vista XL 2007 32 Bit
SELECT `Tabelle1$`.`NDB_No`FROM `Tabelle1$` `Tabelle1$`	8.69 seconds	5.12	?????	4.15
SELECT AliArse.[NDB_NO] FROM [Tabelle1$] As AliArse	8.88	5.18	?????	4.1
SELECT TOP 8810 AliArse.[NDB_NO] FROM [Tabelle1$] As AliArse	8.73	5.21	?????	4.12
SELECT TOP 2000 AliArse.[NDB_NO] FROM [Tabelle1$] As AliArse	7.88	5.03	?????	3.94

Originally Posted by Doc.AElstein

Certainly for the Excel data file reading no great sped advantage of including TOP. This was suggested / expected I think. It may be more relevant when I look at other alternative ways, possibly with reading from a text File with this method and / or using ADO techniques as I intent do shortly??

Once again, I would doubt it as you have no indexing or proper database.

Originally Posted by AB33

...top 2000 says it all....A bit of diversion. Acer and Dell are "No go area" for me.

2000 is TOPs ... Lol.....
( The Dell belongs to me Father -in-Law, The Acers are mostly OK, if I take the cover off and keep them cool
Alan

Originally Posted by xlnitwit

Once again, I would doubt it as you have no indexing or proper database.

Thanks Don, I will check that out agian when I do ADO and / or text file stuff, which I expect to be generally quicker anyway..

EDIT: but you are expecting it also not to make a big difference then either ... OK , point taken

Hi, I thought I was done, here but have a couple of questions ... I may have answered them partly myself..

So I am getting on “super Billy Ho Go good” checking out bringing in a single very long column from a closed Workbook data file.. ( with Microsoft Queery Stuff ). The key to the thing working as I wanted was a Command Text code line like this ,

Please Login or Register  to view this content.

Now
Questions:
_1) Command Text code line for all columns aka. or have I hit a fundamental 255 column limit..
In addition to a long column, I want to get the first 20 or so rows. The macro recorder gives me something of this form if I laboriously select every column heading...: -

Please Login or Register  to view this content.

So I thought maybe that this would give me for example the first and third columns for 21 rows ( back to front for fun )

Please Login or Register  to view this content.

and it does
_ I can have going on for 4000 columns in some data files .... I don’t mind filling up the area in the VB Editor code window where you scroll to, to the right with all the headings. In fact, I am quite partial to that. ... .. but I know I shouldn’t really, .. lol.. , so I thought I had better find a Command Text code line For all columns
I have played around with COLUMNS and * bits but I can’t quite seem to it hit on the correct code syntax......... _....
_.... Or maybe I did while preparing this post._.... This almost worked

Please Login or Register  to view this content.

. - But it only got me 255 columns. When I tried to google on this I could not get a straight answer. There seemed to be discussion of a sql ODBC limit of around 255 columns. But I am not quite sure how that translates into English***
Can anyone shed any light on this


***_2) It appears that the general thing that I am doing here is a ODBC thingy. A few hours into reading up on this I am not much the wiser, - I do not understand most of the computer jargon... So_..
_..Is there coincidentally anyone out there who both
_2a) is experienced and educated enough to have a good understand of what ODBC is , but also
_2b) who has not yet forgotten how to speak in the English that a computer illiterate like me could understand. If so is it possible to explain it to me. I am mainly interested in understanding as far as I can in terms of what actually goes on in the ODBC part of my code, ( whatever bit that is – I would guess at the .Refresh .
I would like to try to understand a bit , so that when it goes wrong later, and I post asking for help, I might be able to say a little bit more than – .... .. Errr... it broke, can you fix it please... Maybe some emphasis could be put in any explanation as what sort of things can cause problems with it working and whether you think it would likely still be available in the future , if that is not a silly question

I stress I am mainly interested in the Microsoft Queer way of doing it in this Thread as a comparison against all the other many ways I am considering in parallel .. ( although I am beginning to get the feel that this sql stuff comes in ADO stuff, which I am starting to look at as well just now... )

Thanks
Alan

I have stripped down and ‘commented bits of my main code and added a simple Call code,
Sub DoAQuickTest
So if it helps to explain what I am doing , you can run that code and you will see what I am trying to do
I want 21 rows x 1767 ( ideally 1767 even though there is entries only to 1766 )
you need this data File: “DBSept2016 - first AlanSucces.xlsx” http://imgur.com/D9knusB
Download File: https://app.box.com/s/j4ssuud642l1dr5tp689a080y7ustl7t
That file above needs to be closed and in the same Folder as this file where the data goes , ( actually that data is deleted after the data is put in an Array , which is the final goal of what I want ) : “WBAccestTimeTest1.xlsm” Download File: https://app.box.com/s/vj2z43orwbobv0kpv0mwdcw9vnt096jl This file also has the code in it in normal code module “Scrap”

Please Login or Register  to view this content.

My recollection is that 255 is the limit to the number of columns with ODBC or OLEDB using either Jet or ACE. Presumably because good database design would tend to preclude tables with more.

Hi Alan,
You are tearing your hair over nothing.
ODBC was 90's technology. It was virtually dead. Like VBA, it is a legacy, hence you found it on recorded macro. You also find it in some dreadful Accounting software called Sage. ODBC is nothing but a driver. The bulk of work on Database is done using SQL.

MS has ADO Net. ADO has been integrated in the Dot Net Framework.
I personally do not spend any time on anything to do, not only with VBA, but VB too. MS has put all its eggs on C#- by extension Visual Studio.

Originally Posted by xlnitwit

... that 255 is the limit to the number of columns with ODBC or OLEDB using either Jet or ACE. Presumably because good database design would tend to preclude tables with more.

Thanks, Don
It seems quite surprising to me. I would have thought these days that is fairly limiting.
But this is just showing up my lack of any real computer background.

I guess if I had had the slightest idea about how to go about organising data bases efficiently then I would have organised my files totally differently from the outset.

On the other hand I have not been too impressed with the speed of getting just 1 column out so far. ...
I don’t really know what Jet or ACE is about. I have seen some reference to Jet on ADO stuff , I think. Is this suggesting that I will be limited to 255 columns with aby ADO alternatives I look at? – Some initial googling suggested that might be the case.

Alan

Hi AB33

Originally Posted by AB33

..ODBC was 90's technology. It was virtually dead. Like VBA, it is a legacy, hence you found it on recorded macro. You also find it in some dreadful Accounting software called Sage. ODBC is nothing but a driver. The bulk of work on Database is done using SQL.

Thanks for the info, sounds like this ODBC probably is not much of a goer



Regarding:

Originally Posted by AB33

MS has ADO Net. ADO has been integrated in the Dot Net Framework. ...

-- same question as to Don - Am I likely to hit a column limit of 255 with ADO stuff as well ?

Hi
So how’s this?
ODBC is old technology from about 1995. It appears from the Microsoft literature to still be supported.
I still have not found a translation into English of what it is or does. I have done about 12 hours intense reading now and am no further.
But a quick attempt to summarise the situation
There is / came JET and other stuff ( and god knows what that is about either.). but along the way .... There is SQL. Maybe that can almost be put into English:
SQL:
Some set of commands that tend to be internationally arranged to be accepted to pick out data.. but which then necessarily, to some extent, defines how the data would have to be organised_.....

Somewhere along the way Microsoft included some software ( and deicide to use a fancy word like a driver fir it, ) for Excel to turn its spreadsheet documents into SQL-accessible database tables....

Maybe a bit later something called JET came along , the idea partly being to optimise VBA and VB controlling and using sql stuff.

The Queery stuff I do was actually more of something to have been done manually. My recorded macro sort of just revealed how it works. Since “...Microsoft change their spreadsheet documents into SQL-accessible database tables, ..” that might mean they added that option in List table to get the table through sql stuff, and that is what the macro recorder caught.
Maybe no one ever thought to do that, - may be it needed a total computer idiot like me to think of doing it_..
_.. or maybe others did it did but stopped doing it and used the JET stuff later, which was better.. well I guess I will see with the ADO stuff.. or maybe not.. as apparently “...Jet does not work with XL 2007 ++?? “
_.....
_.....
“....255 is the limit to the number of columns with ODBC or OLEDB using either Jet or ACE. ... good database design would tend to preclude tables with more....”....... well, .. that ties up with the very deep columns of strangely organised text data I have seen with duplicated Headings. Or rather some item number or key given to it:
Prodkey1 Kcal
Prodkey1 Fat
Prodkey1 Protein
Prodkey1 Koh
Etc...
But really with text storage these days what’s wrong with a big matrix? even if it does have a lot of spaces in it?? And it sound a bit of a contradiction – Duplicating a heading name and giving it a number instead as a Key???. Why not use the Name as a key and be done.
Maybe that is all just an attempt to hide that when they got together to agree on all the sql commands and standards and how for compatibility the data should look, they all drunk too much and forgot how to arrange a workable sql set of commands to do a simple a simple x, y coordinate way of picking out data..
JET is very much into the sql stuff.. and something called relational database management system (RDBMS), was made up to cover the mess they had made in not organising their sql commands and compatible data layouts better in the first place. ._.... –-- This is all a bit tongue-in-cheek and just helps me to get a Layman’s understanding that I can work with and maybe use to explain why if I find a simpler and better way of doing it. --- _...

___ ( It looks like I will probably hit the same limitations with the ADO stuff,, .... the nice code from CK76 .. https://www.excelforum.com/excel-pro...ml#post4624989 .. has a great option to pull out a row directly base on a __ WHERE t1.[NDB_NO] like '%BitOfWordToSearchFor%'" __ .. but I note it also only gets me 255 column... Hmm.... )

_....And anyway, I have not been too impressed so far with the speed of getting a single column out...but I need to consider more in detail the ADO stuff, possibly asking in another Thread for help with that..

_....And further still....
As far as I can make out, if you are using Excel, then anything that works with the synchronised all column then next row way based on offsets is going to be efficient . My final proposal would be to get the heading column, then, based on a search from it, pull out the row...

But I will leave this Thread to concentrate on the “MS Queery / probably change the title to Microsoft prompting supported ODBC Queery” stuff

Any comments on my appraisal of the situation?
Note I am being a bit tongue in cheek just for fun with some of my comments, - no offence is intended to any computer professionals. I love you all really x – I would really be in the poo without your help
At this stage I am just trying to get as good a understanding as possible before committing myself to using any one way of getting my data out, from time to time, from big-ish Excel Files.. It’s looking like this “MS Queer ODBC” way, what on earth that is, when it's at home, is only of novelty value
Happy Easter
Alan

_.__________________________

EDIT Saturday : 15th April 2017:
I did some notes here in preparation for going on to look at ADO alternatives which shed a bit more light on what , I think , is going on:...
https://www.excelforum.com/developme...ml#post4630570

To get around 255 columns limit. You can do one of following.

1. Create named ranges in source workbook. Limiting each named range to be less than the limit on columns. Then use named ranges to query data. Note that you cannot use dynamic named range using formula. It needs to be fixed range.

Please Login or Register  to view this content.

2. Specify range that you are querying from using cell range. (This can also work to limit row size if needed).

Please Login or Register  to view this content.

Both will require multiple queries to bring in all data.

ODBC and OLEDB are both APIs that allow connecting to data sources. ODBC is, as it's name implies, Open- I.e. vendor agnostic. OLEDB on the other hand is a MS implementation, although other companies have produced OLEDB compliant providers for their products, understandably. ODBC is absolutely not dead. OLEDB, on the other hand, arguably is- as far as I know there isn't even a provider for SQL server after the 2012 version.

Jet is a Microsoft database engine, primarily used by Access.

SQL is a language for querying relational databases.

Your point about the number and headings of columns in the table seems to imply that you would benefit from reading about normalisation.

@ CK76
Hi CK76,
Thanks very much for that. In fact you answered another question about a second after it came up in my mind: I was just starting to write some ADO alternative codes and I was wondering what the cell Range query option was about..

Thanks for the tip on the 255 work around. I might have missed that as I might have thought it was a sort of truncating of the first 255 columns, but that is not the case:
I think I can see what is going on:
I guess the “thing” that “gets” the data is limited in the “Fields” that it can cope with. And I guess if the data is conforming to the standard that allows SQL stuff to work, then you can look at any columns. ( Although interestingly that means that SQL stuff does accept that data files might be > 255 columns .. which is sort of against the idea of good database designg ... may be... )

I tried with the code you did for me with

Please Login or Register  to view this content.

and it gets me the next 255 columns (256-510)

I am not at this stage if that will be of final use to me, it might get a bit messy concatenating multiple results , I am not sure at this stage. Using the WHERE option would be tricky , ( unless I can mix column selections... I tried something like these to get me column 1 and a few others .. but they do not work.

Please Login or Register  to view this content.

I did not expect they would work.. I guess the FROM has to be a table of sequential columns ( Fields) which is limited to 255 )


But it is very helpful to know all the options available

Thanks again
Alan


_._____________________________

( closed data file __ “DBSept2016 - first AlanSucces.xlsx” http://imgur.com/D9knusB
Download File: https://app.box.com/s/j4ssuud642l1dr5tp689a080y7ustl7t
CK76 File “ADO_Demo.xlsb” Download File: https://app.box.com/s/p1psnfabrctmduvrjmpqkokhfiah7qjd )

Please Login or Register  to view this content.

@ xlnitwit
Hi Don , thanks for the extra info:

Originally Posted by xlnitwit

ODBC and OLEDB are both APIs that allow connecting to data sources. ODBC is, as it's name implies, Open- I.e. vendor agnostic. OLEDB on the other hand is a MS implementation, although other companies have produced OLEDB compliant providers for their products, understandably. ODBC is absolutely not dead. OLEDB, on the other hand, arguably is- as far as I know there isn't even a provider for SQL server after the 2012 version.
Jet is a Microsoft database engine, primarily used by Access.
SQL is a language for querying relational databases.....

That is confirming and adding a bit to some notes I was making on this https://www.excelforum.com/developme...ml#post4630570 So that is very helpful, Thanks.
_.______________________________

Originally Posted by xlnitwit

..Your point about the number and headings of columns in the table seems to imply that you would benefit from reading about normalisation.

I am probably getting into areas that come across like brain surgery to me. I have tried reading up on rational data bases and the such.. I have been able to glean the minimum understanding – much understanding more might be a bit dangerous for the health of my brain, Lol...
A quick Google tells me that Normalisation in regards to Fundamentals of Database Design using SQL is to do with no information redundancies... That seems a bit strange when I see deep columns in files specifically designed to work with relational databases which look like this:
Prodkey1 Kcal
Prodkey1 Fat
Prodkey1 Protein
Prodkey1 Koh
Not only do we have headings repeated, but also we have extra identifying Keys to tie all the stuff up in the different files held all over the place in the spaghetti of the relational database paths, connecting routes etc...
( It was files like that which you helped me a lot to get into the form of my data files I now have https://www.excelforum.com/excel-pro...-please-2.html - thanks again for that.. )
I don’t doubt that at the end of the day there could be some final efficiency somewhere, and possible saving in some computer resource. I accept my final file has a lot of spaces in it.
But
_a) they might be used, later, ( Likely they will ) and
_b) with ever increasing size and reducing cost of computer memory, it seems the extra memory usage is a small price to pay for being able to see everything in a simple table...
But I accept as I know nothing about the subject area, and what I am saying is probably an annoyingly simplification, - Apologies for that, It’s not my intention to nerve.. I am beginning to get an understanding that should be enough for me.

Thanks again

Alan


EDIT P.s. Just now I am writing some ADO code alternatives...

I use variations of this sort of code line a lot

Please Login or Register  to view this content.

I have seen something similar when reading text files using a "JET" similar code line

Is there one for Excel Files? - just for another alternative for me to try ?? ( I think AB33 was of the opinion that JET does not work in XL 2007 + - ?? - I do have similar codes I did for OPs working which read text files to Excel using a code line similar to the above , but with "JET" in it

Originally Posted by Doc.AElstein

That seems a bit strange when I see deep columns in files specifically designed to work with relational databases which look like this:
Prodkey1 Kcal
Prodkey1 Fat
Prodkey1 Protein
Prodkey1 Koh
Not only do we have headings repeated, but also we have extra identifying Keys

Those are not repeating column headings, they are values within a column. The benefit of this structure is that adding a new Prodkey only involves adding a row to the table, rather than altering the table structure and any existing query can simply pick up this new data without requiring any changes. Think of a pivot table in a workbook, where you add a field to the column area and each item becomes a new column header.

@ xlnitwit
Hi Don
I was playing around as you answered.. , I did the Data ---- From other sources stuff from the Ribbon options a bit differently ...
If I go for some of the other options like: from data connection assistant , _...
ODEBCdataConnections.JPG http://imgur.com/7A8vUKB
_.. then it seems I am doing something OLEDB , and I have some options:

_1. Microsoft SQL Server : Looks dangerous .. asks me for Passwords to get at a Server..
_2. Microsoft SQL Server Analysis Services: same again .. asks me for Passwords to get at a Server
_3. ODBC DSN: It gets a little way and lets me look at Excel files bit after I select my File, “DBSept2016 - first AlanSucces.xlsx” https://app.box.com/s/j4ssuud642l1dr5tp689a080y7ustl7t
, it seems to find no tables and I do not get further...
_4. Microsoft Data Access – OLE DB – Anbeiter for Oracle: .. asks me for Passwords to get at a Server
_5. Further Stuff: - quite a lot of stuff .... other options
???

I think I am struggling still with what ODBC and OLEDB actually are.. I have info about them....

I thought possibly OLEDB was a sort of user friendly interface which , as I noted before , was intended to be done manually, and my macro recording just coincidently worked and revealed how it did it. Or rather Microsoft Query is the User interface, which I was guessing used OLEDB, - maybe it did once, but in fact Microsoft Query uses ODBC – possibly just as that one is a bit sort of newer ( well actually not, as ODBC came first - I just found stuff about it a from a few years before i can find anything on OLEDB ).. or because it user friendlier, which is what the MS Queery is about... The fact that in my experiment above, experiment _3. did not work may be not too significant. Maybe with a different File it might have found a table.. The Microsoft Query sort of walks you through creating one from the data in the File. - “...My recorded macro sort of just revealed how it works. Since “...Microsoft change their spreadsheet documents into SQL-accessible database tables, ..” that might mean they added that option in List table to get the table through sql stuff, and that is what the macro recorder caught.....”“
So OLEDB is a sort of unfriendly user interface. It’s arguable dead, I guess that is what can happen when you are so unfriendly .. Lol... It is maybe more of a system level thing. It was probably initially intended for Microsoft people to play about with..

I do not think I actually came across OLEDB, but I think you have mentioned it to clear up some possibly confusion with the idea that ...” ODBC is dead....” which it is not. OLEDB is dead or dying. I do not grieve too much, I never met him .. lol...

ODBC stands for Open Database Connectivity, - I read that in a few places. As you said.. “....as it's name implies, Open- I.e. vendor agnostic...”.. That all ties up. It is along the lines of a User friendly interface.

( Somehow ODBC is part of , or shipped with, Microsoft Office as it does not seem to need a reference to any Library for the code to work. - I guess as the (Add In ?) MS Query uses it then that sort of is obvious.. ) . )

ODBC and SQL may have sort of started around the same time, when no one really new what was going o., OBDB was a standard set of API's to get at data, and SQL is a standard language for doing that.. so the two sort of probably developed together a bit, initially

OLE DB stands for ????? have not found that yet... maybe everyone forgot, since it died... Lol...

I have just about sussed out, in Layman’s terms what a API is and what ADO is. –“... ADO is the API from Microsoft that you can use to get at data that conforms to necessary standard which in the meantime most data sources do...” It makes OLE DB more user friendly. But OLE DB is dead ?? – flogging a dead horse? – well probably not, maybe they kept some of the bits of OLE DB on a life support machine , for ADO to use them.
Or it is a Zombie like Apparition , haunting in lies like _ “Provider=Microsoft.ACE.OLEDB.12.0;...

_... - Don: “...SQL is a language for querying relational databases...”
_....– Alan “SQL is some set of commands that tend to be internationally arranged to be accepted to pick out data.. but which then necessarily, to some extent, defines how the data would have to be organised. A lot of stuff has been done along the way including that ..Microsoft changed their spreadsheet documents into SQL-accessible database tables, .. that might mean they added that option in List table to get the table through sql stuff. All this was started and a lot already done while still in the 255 limit to the number of columns. One argument could be that good database design would tend to preclude tables with more than that. ... well you do find in data files often very deep columns.........” I think we are saying the same the .. I am slowly getting it :-)

_.______________________________

Originally Posted by xlnitwit

.. not repeating column headings. .. values within a column ....benefit of this structure is that adding a new Prodkey only involves adding a row to the table, rather than altering the table structure and any existing query can simply pick up this new data without requiring any changes. Think of a pivot ...

I think I realised that, - I just about gleaned enough understanding about rational database to see that..... so although I have no idea at all about pivot tables, I take your point:.
As I said, I am sure there are good reasons for doing things they are done, and the fact that they were all drink at the conference where they agreed on all the conventions was probably only one .. lol.... ... _...
_...I actually meant to say that words are repeated and added. But I do not doubt for one minute that the complexities of how a computer works means the way it is done is more efficient. The problem is I discover things that no one can explain, like the Memory of memory problem we discussed in that other Thread, with using more than once a second range object in a .Match ... and that made it very impracticable to use the data as it was initially.
Also the person who is using what I am trying to develop needs to be able to “see” in even more simple terms than me. So a simple 1 off File like my final data file is preferable to keeping it all the rational data base form and getting into all the Access , .accdb or .mdb database thingies , stuff from which my data was originally deciphered.
( Interestingly a lot of people have asked me for my data, which is all openly available , but only in the .accdb forms, which only the privileged few know how to use in RL...... Even in one instance the software engineer responsible for a .accdb .mbd database thingy : He had had many enquiries about a simple table as I have. – Bit he was not the engineer responsible for collating the original idea, and while he could do “queries” for limited amounts of data, as he had learnt this in college, he had no idea how to get the data in a simple to see 1 off table , which I, thanks to a lot of help in Forums, were able to do.... )

Thanks again
Alan



EDIT: I updated my notes on ADO stuff to include ODBC and OLE DB stuff
https://www.excelforum.com/developme...ml#post4630570

@ AB33

Originally Posted by AB33

'Connect to a data source:
'For pre - MS Access 2007, .mdb files (viz. MS Access 97 up to MS Access 2003), use the Jet provider: "Microsoft.Jet.OLEDB.4.0".
For Access 2007 (.accdb database) use the ACE Provider: "Microsoft.ACE.OLEDB.12.0". The ACE Provider can be used for both the Access .mdb & .accdb files.

Hi Ab33, thanks for that....
A couple of points..
_ (i) It sounds like a possible recommendation.. I am not sure if it means that it is no longer available
_ (ii) That seems to be talking about Access. I am not sure if it is the same for Excel.. – A JET type command does to get data from a text file in XL 2007 ( see end post #41 ). Possibly there is a command line to work for Excel also –I expect if there is I will probably find it .... in a week or so !!

Alan

Hi,
I just carried on my work looking into ways to get data from closed XL Files in another Thread:

https://www.excelforum.com/excel-pro...ml#post4636791


Amongst other things _....

Originally Posted by AB33

...You can try to use Jet driver in excel 2007 and see if you get an error.....

_...... I tried a “JET ADO” version.... with some interesting results....

_.......


Some of the work in that new Thread follows on from this Thread, so I am adding the link to “link” them ( https://www.excelforum.com/excel-pro...ml#post4636791
https://www.excelforum.com/excel-pro...ml#post4637116 )


Alan