Help understanding ADO codes and similar to get data quick. ACE JET Peculiarities

Help understanding ADO codes and similar to get data quick. ACE JET Peculiarities

Aka. WTF is the ACE and other Queer Stuff taking so long while a Jet went like “Billy Ho”

( This Thread follows on a bit from this thread https://www.excelforum.com/excel-pro...ml#post4630973 )
( I had to change the title above a bit after my last after thought gave some really unexpected results *** ??? )

I am looking for some general comments and advice here. I will try and keep it brief, but I have cracked my head on this one a lot over the last couple of weeks...
Very briefly what I am trying to do:
I have a big main file already with lots of data. I may add some more. But I want to keep it manageable. So I am trying to find a good compromise with having in addition to the Main File, some data in other data files which I can add to my main file fairly quickly. That way I can
_ add to,
_ replace, or
_ just use temporarily in the main file , then delete.
That is the way I want to go about things,... for now. So I would want that to work fairly quick , in “real time as it were”. That is why I have dome some detailed experiments comparing the times of different codes to bring data in.
So I have looked quite thoroughly at getting data from the other data files, in particular whilst keeping those data files closed.

After taking advice, ( and listening to my own ), I went very mainly along the ADO way of doing things.
But some of the results for time taken were disappointing so I started again...

I have done more codes and time tests than I can remember, so I will just summarise briefly here and reference some “ appendix” posts elsewhere where I look at each code in much more detail..

A bit more detailed background info to what I am doing is given here:
https://www.excelforum.com/developme...ml#post4619363
To simplify for the purposes of this Thread, let’s just say I want to get data from a file that is in a long column in a worksheet and put it in a VBA variant Member element type Array , arrBk().

Codes I have tried so far:
some initial simple codes
I suspect that finally I will get the data from a closed workbook, but just as a start point and for a frame of reference I done some simple ones for comparison
_ Simple capture of the data into the Array if the File was already open ( so this sets my best “hoped for” standard as it were )
https://www.excelforum.com/developme...ml#post4619759
_ Timing opening the data file in the existing Excel , filling the array, then closing.
https://www.excelforum.com/developme...ml#post4619960
_ Again timing opening, capturing the data and closing, but using a second instance object of Excel
https://www.excelforum.com/developme...ml#post4619980

Ms Queer Stuff
_ I did a lot of codes which ended up being great learning but only final novelty value based on a maco driven Ms Query. A lot of detail in this Thread ( https://www.excelforum.com/excel-pro...-xl-files.html ). Some example codes starting from about here:
https://www.excelforum.com/developme...ml#post4620321
And going on to around here:
https://www.excelforum.com/developme...ml#post4626489
Ending up finally with this code to use in my speed test
https://www.excelforum.com/developme...ml#post4626501

_ then I went on to the three typical ADO type codes.
A tried quite a few variations, here a few:
_ Looping through records ( rows)
https://www.excelforum.com/developme...ml#post4626697
_ Using the VBA Range object CopyFromRecordset method
https://www.excelforum.com/developme...ml#post4626707
_ Using the ADO GetRows() Method
https://www.excelforum.com/developme...ml#post4626709

At his point a few things were clear.....
It’s the opening of the ADO connection that takes all the time. I do not quite understand that. ( The opening of the record set is quite quick, as is any simple processing after. )
I cannot see any noticeable difference in the 3 different ADO ways I did . ( That goes for the codes I have done for myself, or in answering Threads on this- I often practiced by trying a few ADO ways, - I never noticed any difference in times ). They all use the same opening of the ADO connection, so that dominates )
I am puzzled what where makes the data “table as it were”.
I am thrown off as always by Object Orientated Programming terminology. If the ADO connection is an object, then is it a Highway, like ADOConnectHighway , and it takes a while as it is the best Interplanetary Highway imaginable that I could transport all my computer data along in one go. ??? Which I don’t need

After this I wondered off into looking at
Codes based on using the unique fixed vectors for a single cell over an extended range, ( in a closed workbook reference ).
( In plain text simple links to closed workbook).
Starting from about here
https://www.excelforum.com/developme...ml#post4626713
and going on to about here
https://www.excelforum.com/developme...ml#post4628606
It surprised me that the simple closed workbook links worked better than all the codes so far.. and I wondered why that work at all. Is it an “internal Excel thing”, fundamental to how excel works on spreadsheet values held in a simple help matrix somewhere, which coincidently needs little modification to get at the matrix of a different Workbook. That I can understand I think. I do not believe we really have an “Open” Workbook ever. We work on a pseudo copy of the workbook and reference the data from the cache held of the data.
( That in turn has a ( dangerous or potentially very useful ) advantage that when I pass my main Workbook on to someone, but do not give them the closed data workbook , then links and formulas including multi cell range references to the closed workbook, will still work , as can access ( potentially all ) the data from that no longer available data workbook. This is because that data is still to be found in the Cache held in the main Workbook of the last referenced data
( http://www.excelforum.com/excel-prog...ml#post4319226
https://www.excelforum.com/developme...ml#post4482591
https://www.excelforum.com/developme...ml#post4492413 )

_....

Anyway , after all that last lot of codes, I thought I had something really clever with my “Codes based on using the unique fixed vectors for a single cell over an extended range, ( in a closed workbook reference )”

I was just going to write another book on it, Lol... when , just for fun I tried a go at a ADO JET instead of the nowadays more common ADO ACE... I couldn’t get a code to work on my data file unless I resaved it as .xls, ( Excel 97 – 2003 stylio ) –
( Working jet Code ( needed .xls File ) https://www.excelforum.com/developme...ml#post4636087 )
I note here that any ADO code does not return me more than 255 columns anyway.. so I have not lost too much..( My Files are typically a couple of thousand columns, so over pre XL 2007 255 column limit, but rows would never, I think exceed pre XL 2007 65536 row limit )

ADO JET provider alternative
Taking a look at a quick set of speed measurements here _...
https://www.excelforum.com/developme...ml#post4636533
https://www.excelforum.com/developme...ml#post4636716
_... the JET code nocks the socks off all the others. ???
So .. WTF is going on .. ?
Is the answer that the .Open on a “Connection” to a .xlsx or .xlsm file somehow makes the whole massive worksheets available resulting in that very long time compared to the JET code which necessarily needs a .xls file to work, and therefore has to “open up” less or “lay open” less?.

( I assume I cannot use JET on a .xlsx or .xlsm file - I have not found a connection string that works yet. )

Can anyone shed any light on this, - like for example does no one use ADO on any Excel File ?? – I know I have on answering Threads, but in a more demanding situation it seems totally useless compared with even a simple closed reference.

_.____________

To summarise the help I am asking for in this Thread..
_1) Any general advice on the best way to go about what I am doing.
_ 2) Any simple to understand explanation of what is going on with the codes I have done, in particular
_ 2a) ADO ACE JET stuff: what is actually happening internally with all this .Opening of a connections and the .Opening of the record set , - what is the subtle difference. ( If can get a better understanding then I might see the best way to do what I am doing )
_ 2b) My “Codes based on using the unique fixed vectors for a single cell over an extended range, ( in a closed workbook reference )” – As I understand it somewhere the last ( see the references I gave above ) lot of data pulled in from a closed range reference is held in some XML thingy file, “held in the Cache” .. – if I could tap on to that it would be handy .... – according to those references I gave, it will do that automatically sometime use that Cached info. anyway . But if I could purposely access that it could be useful ... This seems to be an area that no one has investigated...
_3 ) The last JET versus ACE issue ???? Can I not either get JET to work on my .xlsx and ..xlsm files or somehow control ACE that is does not take so long, ( I am guessing it is something to do with the Connection “object” thing, as that is what seems to be causing all the grief )

Thanks
Alan
( No rush on this, - I have been looking at this for a while, and probably still will be for a little while .. .. . )

Hi Alan,
I have not read the entire thread.

JET AND ACE are Access's database engines. JET was replaced by ACE. ADO is class which has its own methods and properties, like any other class. Remember to get a data from website, you need MSXML2.XMLHttp class, like ADO, this object has its own properties and methods. In OOP, most classes have properties and methods.
I think the limit of 255 is something to do with Access application, not the ADO library.
Here is the connection string for SQL Server.
Public Const ConStrSQLServer As String = "Provider=SQLNCLI11;Server=(localdb)\MSSQLLocalDB;Database=Books;Trusted_Connection=yes;"
It does not use ACE engine. The engine provider is SQLNCLI11, not ACE.

Hi AB33,
Thanks for the reply..

All this is just what I read......
I got the impression that Jet was an attempt to optimise VB and VBA to control stuff using sql commands. (Apparently a bit earlier Microsoft had Microsoft changed their spreadsheet documents into SQL-accessible database tables... )
ACE (Access Connectivity Engine ) came a bit later and was optimised for Office stuff especially ACCESS.
https://www.excelforum.com/developme...ml#post4630570

Back in that MS Queery thread, I was able to use a command in a rConn.Open thingy to pick our segments along a column of several thousands , but the segments were limited to 255. The rConn.Open thingy took as its second argument a “oConn already opened thingy” which had as its “Provider” a "Microsoft.ACE.OLEDB.12.0" ...
https://www.excelforum.com/excel-pro...ml#post4630790

So I could get, for example, my 21 heading rows in segments
$A1:IU21 __ IV1:SP21 et. Etc...
_....
And I tried again with the ADO ( ACE ) GetRows() code ...
https://www.excelforum.com/developme...ml#post4630570
_... I can get one 255 wide segment at a time 1 – 255 __ 256 – 510 etc... etc...

Please Login or Register  to view this content.

That suggest that the ( big ? ) oConn “object” is possibly getting or rather “opening up” a lot more... but possibly the record set is limited... a record set is one of these weird “irationalised data bases or whatever” so may be that makes sense...... So the info is there.. in the “opened up oConn “object”” ...so that is not limited.
In fact it appears to get loads and loads. TFM ! .
That was my suggested reason why the .Open ( oConn.Open ) takes so fringing long !!.
If rConn ( “Recordset “object””) is limited to 255, that could be part of the reason that it is so fast. Consequently JET at 255 ( xls ) limit is also fast.....
It sort of all makes sense in a very insensibly designed sort of a way..
_.____________________________________________-

Regarding this

Please Login or Register  to view this content.

???
I am not quite sure what to do with that

I have this for ADO working on .xlsx File

Please Login or Register  to view this content.

and this fro JET only works for .xls

Please Login or Register  to view this content.

I tried things like this..

Please Login or Register  to view this content.

In the JET code
https://www.excelforum.com/developme...ml#post4636087
it told me “The provider could not be found “

Same error in all my ADO codes ..
???
( I am using mostly XL 2007 )


Alan

Alan,

I'm not familiar with SQLNCLI11. But you only need to open the connection to the database 1 time. Then can perform all the query's you want using that 1 connection.
Is your opening the connection to the database more then 1 time?

Can you post a small snippet of your code?

Hi mike,
Thanks for the reply,

Originally Posted by mike7952

I'm not familiar with SQLNCLI11 . But you only need to open the connection to the database 1 time. Then can perform all the query's you want using that 1 connection.
Is your opening the connection to the database more then 1 time?
Can you post a small snippet of your code?

I have no idea what the SQLNCLI11 is about.
That was a suggestion from AB33 , which I do not really understand.
I do not see yet how it fits into any of my codes.
If you have time, if you read my first post, you may get a clearer picture. Sorry it is a bit long. I tried my best to condense it and “farm” out my codes and further explanations in the referenced “appendix posts” to reduce clutter in the first post.

In post #1, I have tried to summarise and reference the codes that I am using. ( I have all my codes in the referenced posts and also in the uploaded files ) The files were too big to attach, - I give links to a file sharing source there ( or rather in one of the referenced “ appendix posts “ https://www.excelforum.com/developme...ml#post4636533 ) . )

I am mostly not concerned with query codes in this Thread.
( Although the whole terminology is a bit foreign to me, that is one of my problems Lol.. )

But I do take your point about “only opening the connection once” .. I need to think a bit more about that. ..
A lot of my problem is just trying to understand a bit better what some of the codes are doing, in particular the ADO codes

Alan

Hi
Just some feedback, and comments to add some clarity about what I am doing/ what this Thread is about.

I am not sure that ADO strictly speaking is a class, as AB33 said . ADO I think I have almost sussed: ( https://www.excelforum.com/developme...ml#post4630570 ) It is one of those job creating ( as an alternative to community work ) projects to keep computer engineers busy. Those things sometimes go under the name of application program interface (API ). That is a sort of in between thing, like an interface , subscribed to so that if users take that route to do something , they can use some standardised ( agreed to international standards ) to get at stuff which otherwise might mean doing every task in a completely different way. So ADO is an API, and it is one of Microsoft’s. It is intended for generally getting at data bases. ( Structured Query language, SQL is just some set of commands that tend to be internationally arranged to be accepted to pick out data, so it follows that it would be used a lot in ADO stuff)
API, and the ADO one of that, I can see is a worthwhile thing to do, at least to help the masses do some computer stuff.

Possibly the word “ADO” might loosely/ commonly be used to describe the “Class” or library associated with the ADO API. In Early Binding you will need to check the reference in Extras for something like Microsoft ActiveX Data Objects. ( I think when talking bout the Library / class then you will find the term ADODB, ADODataBase used, but again it seems a bit imprecisely used or defined )
Anyway after you have access to the Library, ( Which you can of course alternatively do with Late Binding _ Create( __ ) _ stuff as an alternative ) will get to see the things that are causing me some grief, both in using and trying to understand , such as the ADODB.Connection and ADODB.Recordset
But my original explanation seems not too bad actually on this. The ADODB.Connection can be thought of a ”laying open” the entire data, sort of pseudo it “has it” / has it available. Think of it as buying organising all the roads and transport that works in a super efficient “last minute / just on time” supply system. So setting up that can take all the time and will be dependent on the size of the stuff it is referring to. That would appear to be a big job for the .Conn to a XL 2007 + .xlsx or .xlsm, as opposed to a smaller .
I think the it follows and makes sense that the ADODB.Recordset is somewhat more straight forward.
_........................
Once again in this Thread I am initially trying to both understand and get the most efficient way to get at my data. I still do take the point that I might in some final application be able to just keep remaking my record set object possibly after a connection... I will go off and check that as well now.
Thanks everyone for any continued help here.
Alan

_.....................
P.s.
I am still no further trying to find out anything about SQLNCLI11
I guess another thing that is puzzling me is what actually does the word Provider and Engine mean.

I do think I have JET and ACE a bit understood, - they are variations of the same thing.



A couple of things for me to go off and do I think.
_ Check out a ACE on a .xls File, maybe confirm / see if that is also quicker
_ See if i can answer this old Tread of mine and see how ACE or JET goes about bringing in a text File of my data
( https://www.excelforum.com/excel-pro...al-arrays.html )

Hi AB33,
My current thinking was that the .... _ “ Big thing that the object ADODB.Connection is “ _ ... is probably not at all limited and is probably somehow “ making available “ an entire .xlxs File, which, of course, is potentially massive!
My segment code proved that ( From Post #1 https://www.excelforum.com/developme...ml#post4636771 ) – or maybe not quite , - I thought a bit about what marc said, re did my segment code ( https://www.excelforum.com/developme...ml#post4636771 ) and did this quick check... as example start to get out my 21 x lots of columns heading rows... I just get the first two segments to demo....
_... ( In fact I used the code from CK76 from here https://www.excelforum.com/excel-pro...ml#post4630790 , and stripped it down a bit, just to demo the point I am making...)
_ So I use a single “ Big thing that the object ADODB.Connection is “
_.. In that code below, I get 2 segments out, columns 1 – 255 and 256 – 510 columns.
_.... I do twice do have “_ making of a record set _ ADODB.Recordset __ .Open _ thingy “

I get this Segy1Segy2.JPG http://imgur.com/UjnXoDB
Code for that, Sub TestieSegments() ,
and Files here: https://www.excelforum.com/developme...ml#post4637173


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

I also remembered this from that Thread..

Originally Posted by xlnitwit

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.

. It is not totally clear what Don was saying there, but I think I can glean enough info for now to say something along these sort of lines..
It is the record set that is limited to 255 columns , ( or the object thereof from Microsoft ActiveX Data Objects 2.5 Library** )

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

I accept that this may be true “....."SQLNCLI11" is the connection provider for SQL....” because I am sure you know what you are talking about.
But I do not know what that means. I cannot use it to do anything, so I have no idea. Sorry but this “to show you what ever problem or limitation you are facing is unlikely because of ADO. "SQLNCLI11" is the connection provider for SQL, but I am still using the same ADO class but with different connection string. This is nothing to do with ACCESS, but I am using the same client(Excel) using the same class-ADO. “ says nothing to me. My ignorance.. Sorry . I am totally missing how that says anything. I don’t know what you are talking about there.

_....


My current take of what All this is about
It seems I have as good idea as anyone by now I expect about what SQL is#.
( I am still not quite sure what the term provider and Engine means in the context of all we are talking about. )
( From me post #6 “....ADO is an API, and it is one of Microsoft’s. It is intended for generally getting at data bases. ( #Structured Query language, SQL is just some set of commands that tend to be internationally arranged to be accepted to pick out data, so it follows that it would be used a lot in ADO stuff)....” __ So Microsoft have “provided us all” with ADO , bless them
Once upon a time there was Open Microsoft Windows API ( ODBC - old technology from about early 1990s ) for getting at data or data management systems. It may have started out at about the same time as SQL. In any case it soon used it very effectively. It remains very popular, and Microsoft Query, MS Query, uses it. MS Query is a user friendly interface for getting at data. ( For API see my post #6 ) . OLE DB (Object Linking and Embedding, Database ), From Microsoft , came a bit later, partly inspired by the success of ODBC . This was something originally at the system level , a sort of unfriendly User Interface , based on ODBC. It was for Microsoft engineers to aid playing with themselves*.... It is dying or dead. ( Probably because they wanked too much* )
I guess in the OOP hierarchical type thing , there seems some logic to JET.OLEDB and ACE.OLEDB
JET was probably some interface to the interface that came along to make the OLE DB a bit more user friendly and optimise it a bit for Office stuff. It is inherently limited to 255 as back in them days just about everything (sensibly**?? ) was. ACE was the newer version and probably was updated for .xlsx , ,xlsm stuff as well as possibly having some emphasis on ACCESS to try and encourage that software... ( I am not sure that I would say that JET and ACE necessarily “belong” to ACCESS ).
I expect that possibly they just did not bother to update the Microsoft ActiveX Data Objects to a new version with a ADODB.Recordset of over 255 rows.. I have no idea about organising data sensibly**, but I have heard a lot of professionals say that they think the going over 255 columns was a bad idea.
( I seem to remember only buying Excel originally as a free Open Office software i found only had 255 columns....
‘_... Never mind )

Jet and ACE are both database engines. They do the actual database work underlying the Access application.

I would imagine that accessing an xls file is faster because it is a compiled binary file, unlike the xlsx and xlsm files, which are in fact compressed folders. I would expect xlsb to be a little faster than xlsx and xlsm because, although it is still a compressed folder, the relevant files within it are binary and not XML.

@ Don
Hi Don,
Thanks for the extra input,
I think we just posted almost at the same time so you may not have caught my last post...

I seem to have a good understanding of most stuff now.

Your “compressed folders” is probably not far off my idea that .xlsx results in a rather big one of these things __ ..”.. “ Big thing that the object ADODB.Connection is __ after .Open “.....”

This is interesting “.....xlsb to be a little faster than xlsx and xlsm because........relevant files within it are binary and not XML. .........” back in post # 1, I talk about another alternative approach of mine , the reference to closed workbook idea.. Zhat revealed to me that .xlsx and .xlsm has these strange XML type files in it that you can do a trick by resaving as zip and opening .... https://www.excelforum.com/developme...ml#post4482591 Sounds like you are saying that those extra “hidden files “ are not in a .xlsb ... I never heard a good understandable explanation of the difference between the .xlsb and the .xlsx and .xlsm , but from what you just said and that reference above one can “see” the difference... I guess that “ resave as zip trick” would not work for a .xlsb.

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

Originally Posted by xlnitwit

Jet and ACE are both database engines. They do the actual database work underlying the Access application.....

I am still getting lost with a couple of words chucked about all over the place with no clear definition:
_ Provider
And
_ Engine

I am not too sure about this “Engine .... ACCESS ... Jet ... Ace ???” stuff... .. this all sounds a bit cloudy and imprecise....

You and AB33 may be saying the same thing along the lines of Jet and ACE are somehow some controlling stuff behind data base stuff. Reading between the lines of what I have tried to pick up from googling for a few days now, I would say it’s a Microsoft thingy making their OLEDB somehow more specific to Office things with some emphasis on ACCESS – and that more so possibly with ACE.

_ I am just starting to read up on getting my Excel data files to and from a text file into another ( my Main File ) ... and I am seeing the whole .Jet.OLEDB stuff ... This as far as I can see is not particularly to do with ACCESS... ???

I suppose you might say..
_ all the text stuff I am talking about is using the ACCESS engine software...

_ I might say that they are both using the “Jet Ace optimised way of doing stuff in Office that is ( in particular with ACE ) a bit more optimised in the direction of ACCESS stuff”
You might want to think of it as an engine, or the engine in something. Or probably better an Engine used in a particular model / Application a lot such as ACCESS . The sales of that model / application paid for lost of its development cost to justify it being done , but it can be used in a few other models / applications.
I do not have ACCESS unfortunately, but I might take a guess that the Microsoft ActiveX Data Object is a default library that you do not have to add a check to in Extras to get it available as you do have to do in Excel.
Hence people say like ACE and Jet are ACCESS engines – a lot of people do say that... – you did not quite say that... you said.. “...Jet and ACE are both database engines. They do the actual database work underlying the Access application...” with hindsight that might not be so far off what I am saying.


As for Provider...
TThat still appears to me a rather imprecisely used word....
_... as a typical example – I would hit on something like I just did... this “......ActiveX Data Objects are a language-neutral object model that expose data raised by an underlying OLE DB Provider. The most commonly used OLE DB Provider is the OLE DB Provider for ODBC Drivers, which exposes ODBC Data sources to ADO.......” and then need a week to try and explain it .
Then I would get totally confused and think that in a connection string you would / should do like
OLEDB __ . or ; then all the rest.

The I read that some things only worked when they added that.. like
OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
_.. so the second term is something else like an under object, but they forgot when they named it, and usually without the first term in it works anyway ( probably goes to the default provider ) so they just left it as it was.
People started calling it the _ Provider _... like they ....
started calling _ ACE and JET Access stuff...

Hi Alan,
I will stick to what I said right in my first post. You need a connection string- call it engine, or provider, or driver. Majority people who want to work in data base as back-end use the connection string site,which I linked it in my earlier post. I never bothered to learn why this provider or that provider, I just go into the site to get the connection string. I then construct my SQL statement. Done!

Let's make life simple and do not need to crack your head over trivial issue. I was going to post a code, but Mike just wrote.


https://www.excelforum.com/excel-pro...ble-value.html

Hi AB33,
Thanks for the link to _ https://www.connectionstrings.com/excel-2007/ https://www.connectionstrings.com/qu...-syntax-error/

The Forum there is dead, but they have a good collection of strings. ( I think I posted there once or twice a year or so Ago:. )
I use it a lot. I have mostly got text and Excel connection stuff from there..

I do not have ACCESS.
I know nothing about ACCCESS

I think I see that the code you referenced from Mike will allow me to use Excel to get at stuff in an ACCESS file. Loos like another interesting possibility , but a bit off track from what I have been doing so far.
I doubt I would know where / how to query an ACCESS file to get the info I need quickly

Purely out of interest :
Originally my current data file, came from this,..
Data File: “sr28.accdb” https://app.box.com/s/szpb33lazt35iz441mpw1n1kyazuukpi
InfoPDF File: “sr28_doc.pdf” https://app.box.com/s/7xrs9jq8pfmp98x56jcfapa1noqen61y
but I would not know how to start with doing anything with that )
The data file, “sr28.accdb” , which I am guessing is arranged to make it jolly Billy Ho spiffing good, good with irrational DerangedAnal databases or whatever has a size of 144.05MB. ( It just took almost an hour for me to upload to the file sharing site to get the link )
My data file , “DBSept2016 - first AlanSucces.xlsx” , https://app.box.com/s/j4ssuud642l1dr5tp689a080y7ustl7t is 4.1MB.. ( takes a couple of seconds to upload )
Both have the same info in, but mine has unified units and all appears on one sheet like a table that you can look at. It took me a week turn MS Query manually derived data sheets within Excel from that file into my now data files
After this, a software engineer responsible for the ACCESS File asked me for a copy, as he had had many request for such file. He had no idea how to do this. He knew how to get and add small bits of info into it using his ACCESS, as he had learnt to do that. I have the opposite problem
I guess if I came back in another life and studied computing that I might be using ACCESS and wonder what an idiot like me was doing with Excel and possibly text files.. LOl...

I think I will stick with trying to get a good understanding of what I am doing. ( Mostly bringing stuff into a Main Excel File from another Excel data File. ( I may have a quick look at changing my Excel File to a text file, and getting that into my main Excel File , just as a last comparison ))

But thanks for all your input.
Alan

Originally Posted by mike7952

Best way I know to describe Access is it is Excel on Steroids.

Well, that does sound like the sort of thing I might want to get into .. but I am not sue if that would be too wise.. for the sake of my health...

Alan,

Do you have Microsoft Access?

Hi Mike

Originally Posted by mike7952

Do you have Microsoft Access?

No ( see post # 13 https://www.excelforum.com/excel-pro...ml#post4637277 )

Originally Posted by AB33

It has been known xls suffers from memory leak

I suspect what you are referring to here is the memory leak issue that ADO has if you query an open workbook. That is not specific to xls files.

Just as FYI Doc.AElstein.

Few additional info.

If working with text file, you should consider setting up Schema.ini file in same folder as text file that's being queried. Without it you will encounter issue querying columns with mixed data type. As text files do not contain schema info (unlike .xlsb, .xlsx, .xlsm etc).
https://docs.microsoft.com/en-us/sql...xt-file-driver

When working with query that you are not sure that it will return data. It's good idea to add check to make sure recordset contains data (which isn't present in my demo code in your other thread).

OLEDB provider is analogous to ODBC DB drivers. It is used to allow application (consumer) to choose from one of multiple implementations of data storage (binary, database, XML etc).
https://msdn.microsoft.com/en-us/lib...(v=vs.85).aspx

Edit: More detail on Provider Model Design Pattern in below article.
https://msdn.microsoft.com/en-us/library/ms972319.aspx

@ CK76
Hi CK76
Thanks a lot for checking the Thread out, appreciate it.
Thanks for that extra info... I am playing with the text idea just now... so I will look at all the info you gave ( I just got some text files made of my data, - practicing here just now: https://www.excelforum.com/developme...ml#post4637378 .
The code to write to a text file takes ages, but that is not such an issue as I only need to do that typically once.
I want the reading of columns and stuff from it to Excel to go quickly, so I will see how an ADO compares with all I did with reading the same data when it was still in the Excel data file..
I may post all that in an old unanswered Thread of mine once I get a code to read the Files using ADO.. I think thanks to you and a few others I can probably handle that now... I couldn’t do that back then.. https://www.excelforum.com/excel-pro...al-arrays.html

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

I don’t recognise any of that _ Schema.ini _ stuff – I have no idea where that would come in. But maybe that will become clear when I get my ADO reading codes sorted.. ( I will probably start with an Excel Open _______ For Input first , just for a comparison )

_.......
Every time someone tells me anything with the word “ Driver “ , “ engine “ or “ provider “ in, my brain freaks out – those words seem to be used randomly by people that know what they are talking about ...
It totally confuses someone like me as they do not seem to have any clear definition.
One persons driver seems to be another person’s engine etc..... etc... So I totally get disorientated.

You might have gleaned from my posts that I have built up some “Layman type” explanation for the whole story. I avoid the words “ Driver “ , “ engine “ as I have yet to hear anyone use them consistently or explain the differences in them in plain English.
I can possibly understand what is going on. I had an earlier life as a Physics / Microwave Electronics Researcher – we had a few words we threw around everywhere – once at a major conference they debated a week what some of them meant. They never came up with an explanation, and concluded, I think just to use then further anyway lol....

_......

Like you said this :
“.......OLEDB provider is analogous to ODBC DB drivers. It is used to allow application (consumer) to choose from one of multiple implementations of data storage (binary, database, XML etc)....”

I said something like this
My current take of what All this is about
It seems I have as good idea as anyone by now I expect about what SQL is#.
( I am still not quite sure what the term provider and Engine means in the context of all we are talking about. )
( From me post #6 “....ADO is an API, and it is one of Microsoft’s. It is intended for generally getting at data bases. ( #Structured Query language, SQL is just some set of commands that tend to be internationally arranged to be accepted to pick out data, so it follows that it would be used a lot in ADO stuff)....” __ So Microsoft have “provided us all” with ADO , bless them
Once upon a time there was Open Microsoft Windows API ( ODBC - old technology from about early 1990s ) for getting at data or data management systems. It may have started out at about the same time as SQL. In any case it soon used it very effectively. It remains very popular, and Microsoft Query, MS Query, uses it. MS Query is a user friendly interface for getting at data. ( For API see my post #6 ) . OLE DB (Object Linking and Embedding, Database ), From Microsoft , came a bit later, partly inspired by the success of ODBC . This was something originally at the system level , a sort of unfriendly User Interface , based on ODBC. It was for Microsoft engineers to aid playing with themselves*.... It is dying or dead. ( Probably because they wanked too much* )
I guess in the OOP hierarchical type thing , there seems some logic to JET.OLEDB and ACE.OLEDB
JET was probably some interface to the interface that came along to make the OLE DB a bit more user friendly and optimise it a bit for Office stuff. It is inherently limited to 255 as back in them days just about everything (sensibly**?? ) was. ACE was the newer version and probably was updated for .xlsx , ,xlsm stuff as well as possibly having some emphasis on ACCESS to try and encourage that software... ( I am not sure that I would say that JET and ACE necessarily “belong” to ACCESS ).
I expect that possibly they just did not bother to update the Microsoft ActiveX Data Objects to a new version with a ADODB.Recordset of over 255 rows.. I have no idea about organising data sensibly**, but I have heard a lot of professionals say that they think the going over 255 columns was a bad idea.

I can follow what I said, even if it is just a bit light hearted and imprecise... It misses out a lot of detail. But it is written in English

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


But I do really appreciate the extra info. I will have a good look at it, maybe the penny will drop ... lol...
I have a lot of good info from this thread. I expect. I need to get my brain in gear to see it better, I guess.

Thanks again
Alan

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

P.s.
Would_..

Please Login or Register  to view this content.

_..not just be like

Please Login or Register  to view this content.

_....then Str would be “” for all of “ WHERE ______ “ for with conditions of where ???

Using the WHERE might be another alternative to what I was planning to do.
I was planning to use ADO or whatever proves the quickest to get an instant first column. Then I would write some code to do a good quick search of the product I wanted. Having found it and its row number.

Then I might use my simple closed Workbook reference idea to get a complete row. That would be quite quick even for a fairly long row. And I would get over the 255 column Limit...

Just by way of a quick update...

Originally Posted by Doc.AElstein

.....
A couple of things for me to go off and do I think.
_ 1. Check out a ACE on a .xls File, maybe confirm / see if that is also quicker
_ 2. See if i can answer this old Tread of mine and see how ACE or JET goes about bringing in a text File of my data
( https://www.excelforum.com/excel-pro...al-arrays.html )

I checked the ACE on the same .xls File as I did the JET code on.
As expected, the time was then similar, and a massive improvement over the ACE on the same File saved as .xlsx
That all ties up with all the ideas in this Thread.
A small interesting point is that I did the tests very carefully and averaged a lot of measurements:...
I saw a definite better time for the JET code, all be it small. The codes were identical except for the connection string.
Some typical results Here
https://www.excelforum.com/developme...ml#post4637122
( all codes and Files , for example here https://www.excelforum.com/developme...ml#post4636533 )

( I am getting on with the text file reading idea now ... later )

_....then Str would be “” for all of “ WHERE ______ “ for with conditions of where ???

If using single condition in WHERE clause. Yes. But in many instances you want more than one condition.

In those instances, you will concatenate multiple conditions which will start with "AND", "OR" etc. However, since you can't use "AND" or any other condition to equal every row you must leave out the condition, if you want to allow for "ANY" value when user so choose.
So, user may be allowed to use BLANK/ANY in given condition, you must skip that concatenation altogether.
Then you'll need to ensure WHERE statement only occurs once in a string at each concatenation. Since you will not know which condition will be the start.

There for, to avoid checking at each concatenation step whether query string already contains WHERE, you can simply have "WHERE 1=1" in base string.

EDIT: Below link to Stackoverflow has better explanation.
http://stackoverflow.com/questions/2...n-a-sql-clause

Could pass an array as the where clause using In( Your array values)

Please Login or Register  to view this content.

or for 2 different fields

Please Login or Register  to view this content.

Hi mike, Hi CK76,
Thanks for the extra info...
I can’t follow most of what you are saying in saying in Posts #23 and #24.
I have no real experience with using the sql stuff to pick out specific info, so I can’t relate it to anything in the practice.

No matter, I might get the point later once I have more experience with using these sql commands.
( I can’t follow anything at the stack overflow reference. They seem to be waffling over all sorts of different things there. I don’t see any clear answer there at all. )
Thanks anyway
Alan

OK , Thanks, may be I will understand later when I have some experience with sql queries. I just have zero experince here so I can't relate it to anything
No mattter

Hi

By way of an Update or rather passing comment..
A re read of all this:_..................

_..Xlnitwit: accessing an xls file is faster because it is a compiled binary file, unlike the xlsx and xlsm files, which are in fact compressed folders. I would expect xlsb to be a little faster than xlsx and xlsm because, although it is still a compressed folder, the relevant files within it are binary and not XML.
Alan: back in post # 1, I talk about another alternative approach of mine , the reference to closed workbook idea.. that revealed to me that .xlsx and .xlsm ( or .xlsb) has these strange XML type files in it that you can do a trick by resaving as zip and opening .... https://www.excelforum.com/developme...ml#post4482591 Sounds like you are saying that those extra “hidden files “ are not in a .xlsb ... I never heard a good understandable explanation of the difference between the .xlsb and the .xlsx and .xlsm , but from what you just said and that reference above one can “see” the difference... I guess that “ resave as zip trick” would not work for a .xlsb.
So it seems Microsoft in their wisdom decided to supply Files from XL 2007 + in a compressed form with other Files. That is all “hidden” behind the .xlsx and.xlsm – so the .zip trick gets you at that
xlsxZipOpen.JPG http://imgur.com/rCGCKb9
xlsbZipOpenWTF.JPG http://imgur.com/0ax8lql -- “not saved in a text readable Format……“ well someone got that right at least: I can’t read it sheet1 – binKopie.txt https://app.box.com/s/w2obco2xwtu5tjecjgegx2al6txjzbh6
( It don’t work for .xls - well it wouldn’t I suppose , it ‘aint zipped **
xlsZipOpenNotOnYourNellyMatey.JPG http://imgur.com/l88sX5O )
(Zip trick: Make copy --- resave as .zip --- open up zip fle ---- navigate down to get a .xml of a worksheet --- Copy that, -- rename like .txt --- open that --- and be amazed or confused )
That might have some advantages if I can get at those XML Files to use that extra range value stored from a closed range reference from the last time the File was open.. ...
: I don’t understand what this means ?? -- binary format:- the structure is similar but it's not saved in a text readable format as the Xlsx files --- ??? _ Nevermind.. _...

_.............. Made me think that I should do a quick check for completeness on my closed data file reading speed tests on both a .xlsb, and if I can manage it somehow on a true XL2003 File***.

So, to that end..., - I have now:
DBSept2016 - first AlanSucces.xlsx ( 4.157 KB ) https://app.box.com/s/j4ssuud642l1dr5tp689a080y7ustl7t
DBSept2016 - first AlanSucces.xls (**8.629KB )*** https://app.box.com/s/v7kqq7xdj8p86yb8oh7rlxl0lsniqzr5
DBSept2016 - first AlanSucces.xlsb ( 3.177 KB ) https://app.box.com/s/ogwxh8x0094a263bj456ks6nv8310do3

I redid some speed measurement results here: https://www.excelforum.com/developme...ml#post4638679
( The .xlsb speed tests are based for comparison with the ADO looping through rows code just to keep a valid comparison with the .xlsx amd .xls tests. Necessarily I think I am limited to the .ACE.OLEDB .??.. ( ?? = ds*?=)(/% “thing” – for want of anyone yet knowing what that is .. lol.. ! ) )


_.......
The results at that link ( : https://www.excelforum.com/developme...ml#post4638679 ) show that I have halved my time compared with the .xlsx File, and have a comparable time to my Closed Workbook reference ideas ( https://www.excelforum.com/developme...ml#post4626713 )
So it was worth making this extra test for completeness I think.
I am still not close to the massive improvement with using the .xls File.

I’ll carry on now with the Text file reading alternative
Alan

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

***One thing I would like to do for completeness is try a true 2003 File. ( That rather large File size , suggest I may have some weird compatibility File ** or it is because it ‘aint Zipped** )
Unfortunately the one computer I had 2003 on recently died.
If on the off chance anyone with 2003 catching this Thread has the time may be they could do me a favour- Please take either of those files and make me a true XL 2003 version. I expect this might mean opening the .xls version of mine, and doing a copy and paste of the first worksheet to a new XL 2003 File. ( I am only interested in the first worksheet )
Thanks

Otherwise I may either post a support Thread for this in Excel General, or buy a cheap version of 2003 Office from ebay or wherever and give it a go myself. In any case I will feedback my findings

Alan

@Alan

I have a Pro Copy of Microsoft 2003 , PM me If you would like.

An xls file will often be two or more times the size of the equivalent new format file. The xlsb format usually produces the smallest file size.

Originally Posted by xlnitwit

An xls file will often be two or more times the size of the equivalent new format file. The xlsb format usually produces the smallest file size.

Hi Don,
I have ordered a couple of xl 2003 discs, but I expect I will not learn much more. It seems you are saying the increased size I have in the compatibility mode .xls from my XL 2007 is likely to be the same in a “true” in XL 2003 made file.
It is interesting that size is smaller for the .xlsx and .xlsb, despite, as I understand it , there being more there – I mean by that all those extra files you can reveal with the .zip trick.
I am wondering then if the .xlsx, .xlsm and .xlsb ( “ hidden zip “ ) are not such a bad idea and quite efficient.** or ##
It is just a shame the _ ADODB.Connection .Open thingy _ takes so long on a .xlsx file ( a bit improved with the .xlsb ) . I suppose that might be as the _ ADODB.Connection .Open thingy _ does a ”unzip” to reveal the appropriate file.
I wonder if it is possible to get the connection to work on one of the “hidden” files instead.
That would be an interesting solution for me: The large data Files that I have are likely not to be added to. – More likely I would trash it completely and replace it with a new one every year or so. So if it were possible , I would just as well store that “Hidden” file . ( In other words I unzip the .xlsx or .xlsb file and save somewhere the appropriate file from all those “hidden in the zip “ files )
Then I would have my _ ADODB.Connection .Open thingy _ work on that file. It would be very interesting to know if that is possible ??

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

**
I am answering an old unanswered Thread about turning my data file into a text fiile, then reading bits of that instead into my main Excel File. For my 3.1 – 4.1 MB .xlsx .xlsb ( 8.6MB .xlsx) , I am finding an equivalent produced .txt file of about 18MB ( includes extra separator characters in it )

My .xls file only has about a seventh of the data in my .xlsx .xlsb and .txt file due to the truncation to 255 columns as opposed to the 1766 columns I have in my data file. ( I just did a quick check for only the first 255 columns in the text file and I get a size in the text file of about 5MB, and by deleting all but the first 255 columns in my .xlsx file I get about 3.9MB, .xlsb 2.9MB )
The fact that the .xls is bigger is reasonable as it has more than just simple text stored.
Very crudely I might expect an “unzipped” .xlsx to be around 60MB. So sounds like the “.xlsxmb zippy” thing that Microsoft decided to do from XL 2007 + was a good idea. ( I guess it went hand in hand with the massively increased spreadsheet size capability (## or maybe Microsoft wanted to hide their shame – a lot of computer experts have told me they think such big spreadsheet files are stupid / computer storage insanity ) )
It is a shame it is working against me for a _ ADODB.Connection .Open thingy _

_...
But maybe reading a _ .txt _ file ( or reading a _ unzipped revealed other File thingy _ if that is possible might be good alternative.

_...
( Another thing to try – a bit messy but for completeness - would be to split my data file into 7 .xls Files and do 7 x ( a ACE or JET on .xls Files
Crudely, very approximately, looking at my results so far that would suggest a total time of 2 compared with about 6, or , so a third of the time for a a _ ADODB.Connection .Open thingy _ on a .xlsx. ..... ... possibly getting close to the .xlsb results ....

_......


Regarding the .xlsb. generally ....
For my work I cannot google any real disadvantage of a .xlsb.
I am solely working on private stuff, so I take the otherwise unwise step of enabling all macros on opening by default – so the loss in the possibility of disabling on opening is not an issue for me.
I found it fun to do the “unzip trick” and “see” what was in the XML files as readable text. ( In the equivalent files in the .xlsb I see some strange garbage text ). But that was just for fun.

( If I get anywhere with my idea of accessing the info stored in the cache from the last brought in by closed reference range info, then I hope that still will work for .xlsb... Hmm – expect it will. ?? – i expect whatever process ( if I can find one ) that will read and find info in those .bins ( http://imgur.com/0ax8lql ) will also be able to get at what is in the .xmls ( http://imgur.com/rCGCKb9 ) )

_.....

So I still have a bit to do ....

My belief is that the contents of the xlsb file are compressed binaries that would be much the same as the binary information stored for each worksheet record in the BIFF8 (xls) format files. There is no way to get ADO to talk to those without extracting them.

Hi Don
My idea was that I would do the extraction manually once a year. Then I save those extractions and use my code on the extractions.
I expect I am talking rubbish as I have no idea of what is involved.

But pseudo code idea like what I was thinking is this

Either

I extract and resave a sheet1.bin or sheet1.xml ( once a year in the Folder where my main File is )

Then I have pseudo for the connection strings In the codes I use daily something like these

Please Login or Register  to view this content.

Or
Maybe there is an intelligent code that “scrapes” XML files : that would get me both my basic data ( and possibly also the data I was interested in for the last stored in the Cache from closed workbook references )

There is no provider I am aware of that could read a sheet1.bin file. Or, to be more accurate, obviously the ACE provider can, but it expects you to point it at a workbook and there is no way to tell it that you already did the extracting on its behalf.

There are certainly ways to read XML files but I can't believe that it would be worth the time to come up with a schema to read a worksheet and I'm not sure it would be any faster or better than using a workbook or a database. SQL Server has a free version, for example.

Thanks again Don,

Originally Posted by xlnitwit

.. SQL Server has a free version, for example.

I am not sure what you mean by that ?? version to do what ?
Alan

To serve as your data storage, rather than a workbook.

Originally Posted by xlnitwit

To serve as your data storage, rather than a workbook.

OK , thanks.
I don’t think I want to get into “Powerful” external things like that just yet.
I will probably be extending my external grasping capabilities a bit far ..Lol..

But I can imagine it would have a good potential for working fast. – I do already have one other alternative way to get data using code in my main File where I can daily bring data into my main File. That scrapes data from a site which gives data of the sort I have which, when I had all they had to offer, would have a row size of about 35000. I sometimes have my main file on an old almost dead computer that takes ages to do most things, but it never ceases to amaze me how quickly that scraping code rips a row of data out for me.
So I can understand the potential for having and getting things from “cloud” type sources.
Having said that , that is slightly away from my current example of bringing in a whole column of data, - I do also have a scraping code that does that to get me the entire “Product name or description” , which is what I am basically doing now. That I remember was not quite so quick, and probably comparable with the best times I have seen so far.

It seems like I may be coming to the conclusion that you cannot pull data quickly using code from a column of data in a .xlsx, .xlsm or .xlsb file. The sticking point being that all methods currently available have been designed to do the “unzip”, which I expect is causing the extra time of a few seconds.
That might be an advancement for Microsoft to do if they were bothered in improving such things, - That is to say they may arrange for a connection string start bit , ( going by whatever name you choose to use at the time : Driver, Engine Provider ) , which would not do the “unzip”,
I guess, however, the argument would be , why bother – put your data somewhere else and read that..
Maybe I will answer that later when I get my text file reading code going and do the time comparisons again. ( I am still, however, going to hit the RecordSet 255 column limit , .... unless I have an alternative sql language using type library to the ADODB which returns a “wider” Field/column RecordSet )

Hi Kyle, Hi CK76
Thanks for the extra input... and advice...

I think I noted somewhere along the line that I made a big mistake with going over 255 columns.
Way back I had a turning point where I knew even less than I do now and for some reason the > 255 columns in Excel pushed a 50 50 decision whether or not to buy Excel in the first place . I still had a gut feeling not to go over 255 columns, I don’t know why, I had no idea about computers but had some vague old mathematic memory telling me it was a bad move.
I still resisted and in fact for a long time split my table into a few Worksheets. Then somewhere along the line I thought.. Bollox ! - what is this phobia I have in going over 255 columns and I consolidated my data files into typically one sheet.

Now one thing did just occur to me .... I learnt something around Post #8, code Sub TestieSegments().. “.......
...... _ So I use a single “ Big thing that the object ADODB.Connection is “
_.. In that code below, I get 2 segments out, columns 1 – 255 and 256 – 510 columns.
_.... I do twice do have “_ making of a record set _ ADODB.Recordset __ .Open _ thingy “......”

My “ Big thing that the object ADODB.Connection is “ does do get everything and it is only the making of a record set _ ADODB.Recordset __ .Open _ thingy that screws things up for over 255 column

In this Thread I have been slowly getting the hang of sql commands in the first argument command string of the set _ ADODB.Recordset __ .Open _ thingy.
I see I can select a Worksheet in the sql command string.
So
I took a look at _ something else ( http://listenonrepeat.com/watch/?v=z...Something_Else ) and then i did this code
Sub AlanMadeAWonderWorkbookIn7Worksheets()
( based on the modified code form CK76 , File: “ADO_Demo.xlsb” https://app.box.com/s/byzrtemicqdrs3h01h8zrb0k3ijvhwro )
But
_ I split the my .xlsx file into the same Table data spread over 7 Worksheets in a .xls File
“DBSept2016 - first AlanSuccesxlsS.xls” ( *approx 9.6MB ) https://app.box.com/s/dti68egbsxugy190eb59x22ahiufrtki
( *Very strange ... all the data from the .xlsx file but approximately 7 times the data in the .xls file which was approx 8.5MB.That is actually pretty unbelievable Wonder as the text File I made from it was about 18MB, and .xls is not zipped ???
So that code above brings in my data from the closed workbook which has the data split into 7 Worksheets of approx 255 columns in each worksheet. It seemed to work quite well... here is that code:
https://www.excelforum.com/developme...ml#post4640259


That inspired me more to check the code out in a Function to do the speed tests again. ,
Function FuckMeHeadLongUsingADOJET7Worksheets(___, , ByRefs 7 arrays)
So I did it...
https://www.excelforum.com/developme...ml#post4640342
So what that code above does is the same as Sub AlanMadeAWonderWorkbookIn7Worksheets() but it brings in a column from each worksheet and puts that into an Array. So it is doing 7 times what my code version previously did with a .xls file


But this did not work to well. Yet. I have some very strange results ..
_ A. The most strange thing is that the looping of the record rows seems to randomly skip a record or two in the first 20 records. ??? ( so the code errors when it gets to end of a file a row or two too quickly as the loop skipped a record or two and got to the end a bit too quick.??? )
_ B. The command oRST.RecordCount seems to take a longer time than I expected . So that is really weird. Maybe there is a connection to the problem in A.

_ C. Generally when I go through the code in F8 mode things seem to go quite quickly. But a run of the code takes quite a while.

I think all the last three points may suggest something very strange going on.
So I will need to look a bit further here.

I realise it is still not really doing things in a more sensible databases sort of way, but i thought it was worth a quick go.... but is turning out a bit longer due to these weird results. ???

Alan


EDIT
OK found the problem. I should have known .. characters > 255 in a few cells. I was wary of that, but I did not expect a .RecordCount to get it right and then a loop through the same records to go through , not error, but just skip a row.. ( and the last similar code I did went through the same data and did not skip the row.. well that’s databases Recordsets for you.. Lol..
I’ll take another look at this latest code idea later then.....

Originally Posted by Doc.AElstein

....
EDIT
OK found the problem. I should have known .. characters > 255 in a few cells. I was wary of that, but I did not expect a .RecordCount to get it right and then a loop through the same records to go through , not error, but just skip a row.. ( and the last similar code I did went through the same data and did not skip the row.. well that’s databases Recordsets for you.. Lol..
I’ll take another look at this latest code idea later then.....

Hello,
This is just as a way of following on to the last post.._..
_.. it was late and I messed up in the last post.
My other codes just got the main data starting after my 20 heading rows , ( In those rows I have some untidy data , including some very long strings ) , and I really should have caught the 255 character problem anyway – I have encountered similar problems a lot . The fact that this comes up in RecordSets means one of possibly two things.

_ Fundamentally RecordSet data has that limit.
_ somewhere an Array of Variant type member elements is coming into play. I know only too well that those Variants are a “special sort” of Variant for use in Arrays and those are somehow limited to 255 characters, - no one knows quite why.. ( https://www.excelforum.com/the-water...ml#post4614261 )

_ In any case I went about redoing the last experiments and confining the range got for the Recordset to A21:A8810 which is the actual data range consisting of fairly sensible looking data

So this is what I got:
_ The code still goes through the _ ADODB.Recordset __ .Open _ thingy _ very quickly. – This was also the case for a 1 worksheet .xls file. So this is confirming the idea that a _ ADODB.Recordset __ .Open _ thingy _ is Bad News ( http://listenonrepeat.com/watch/?v=K...y_Hey_Bad_News ) regarding only on a .xlsx, .xlsm, .xlsb “hidden zipped” file
( Something I noticed in passing, by the way, is that if you are debugging after _ ADODB.Recordset __ .Open _ then opening the file in the __ .Open _ connection string will prompt you that you are opening in write protected. So my idea that the does a sort of “ laying Open “ of the said File.. – probably in some characteristics and processes similar to what happens when a file is in the normal way opened )
A quick test I did was: ,
_ the original 1 worksheet codes as working on that 1 worksheet File, ( "DBSept2016 - first AlanSucces.xls" https://app.box.com/s/v7kqq7xdj8p86yb8oh7rlxl0lsniqzr5 )
and then
_those codes with this file ... “DBSept2016 - first AlanSuccesxlsS.xls” (7 Worksheets approx 9.6MB ) https://app.box.com/s/dti68egbsxugy190eb59x22ahiufrtki

( but in all cases on doing one RecordSet on the first Worksheet only ( which is identical in both Files ) )
Results: https://www.excelforum.com/developme...ml#post4640375
There is very little difference, which is encouraging, but there is also not too much difference in the file size ( a bit wonderefulingly)


Then I went back to the Function code _.
Function FuckMeHeadLongUsingADOJET7Worksheets(___, , ByRefs 7 arrays)
_. Which I discussed in the last post, ( the code is here: _..............
https://www.excelforum.com/developme...ml#post4640375
_............... )
I made some a few corrections to it..
Things look a bit more sensible now:
The speed of the code comes out as about half to one second... which is Not bad compared to the sort of results generally that I have been getting..
Of course this may still be a bit of a messy not really databasesy way to do it. But it does encourage the 255 column usage. ( One added nice thing about this could be to extend the Worksheet number a bit and try to divide the data into different groups depending on the actual sort of data. That is actually what I did originally before going over to a consolidated single worksheet.. that is probably a long term idea.. )
What is a bit of A DOpey thing is having to use a XL 2003 .xls File.
Some more quick speed test comparisons here: https://www.excelforum.com/developme...ml#post4640375

I’ll leave this one hanging. Any further comments on anything or this latest _ .xls split in 7 x (255 column) Worksheets approach _ would be very welcome as always .

I will go back to the text File making and reading alternative now...

Alan

Hi CK76
Thanks for the reply,
I'm still not clued up on the _ Schema _ stuff.
When I get some text write / read alternatives tested, then I will come back and have another look at finding out what that is all about
Alan

Hi CK76
OK, thanks, - I expect I will come back to you later when I have got some text files and reading thereof up and running.
Alan

Just by way of a short update regarding.....

Originally Posted by Doc.AElstein

I may .... buy a cheap version of 2003 Office from ebay or wherever and give it a go myself. In any case I will feedback my findings....

Originally Posted by Doc.AElstein

Hi Don,
I have ordered a couple of xl 2003 discs, but I expect I will not learn much more. It seems you are saying the increased size I have in the compatibility mode .xls from my XL 2007 is likely to be the same in a “true” in XL 2003 made file......

I got a disc today, had no problems installing it in parallel to an existing XL2007 ( on a Dell 4GB RAM 64 bit Windows 7 machine )

I did some quick experiments:....


So I have a File in XL2007 with about 1766 columns, and shows 4.157MB size
“DBSept2016 - first AlanSucces.xlsx” https://app.box.com/s/j4ssuud642l1dr5tp689a080y7ustl7t

I saved that in XL2007 as XL 97-2003 .xls and it showed 8.593MB size ( I lose data after the 255 columns )
“DBSept2016 - first AlanSucces.xls” https://app.box.com/s/v7kqq7xdj8p86yb8oh7rlxl0lsniqzr5

I copy the one 255column data Worksheet from “DBSept2016 - first AlanSucces.xls” into a new Workbook in my just installed XL 2003, .... and it shows 23.444MB size
“DBSept2016 - first AlanSucces2003.xls”
DBSept2016xls2007_xls2003_xlsx2007.JPG http://imgur.com/zOVeW0P
I am not sure what that is telling me....
I have a go opening “DBSept2016 - first AlanSucces2003.xls” in XL2007
It looks like some rowandcolumnsare displayed a bit disjointed
xls2003in2007rowcolumnmismatch.JPG http://imgur.com/dBDlDOe
And I can’t seem to do anything with it. It appears to have an image in it???

I probably messed up... Never mind. ... So try again.....
So I tried again, and this time in pasting the Worksheet in from “DBSept2016 - first AlanSucces.xls” into a new Workbook in my just installed XL 2003, I chose the option XML calculationTabele , _..
XMLCalculationFormatPasteinXL2003.JPG http://imgur.com/1OdDVDK
_..and all is well .......
The File size looks closer to what I expect ( 8.427MB )
xls2003in2007GotPasteInRightBoyOh.JPG http://imgur.com/Ac20D35
“DBSept2016 - first AlanSucces2003.xls” (8.2MB) https://app.box.com/s/hd8vtx88hgrnr6fwaqis7jr37nz3wf5j

It opens normally in XL2007. It looks exactly like “DBSept2016 - first AlanSucces.xls”

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

I did a quick test with code Function FukMeHeadLongUsingADOJET(___ , ( which is a JET code working on a .xls File which was used in all my test so far for File “DBSept2016 - first AlanSucces.xls” ).

At first glance I see no noticeable differences if I use data File
“DBSept2016 - first AlanSucces.xls”
or data file
“DBSept2016 - first AlanSucces2003.xls”

So initially there seems no issue with using interchangeably _..
_..a data File in .xls from XL2003
Or_..
_.. a data file in .xls from XL2007
( Both files have identical data and formats in , as far as I can tell )

( Possibly later I might play around with trying the main File and all its macros in XL2003, to see if there are any issues there )

Alan

Hello.
@ CK76 ( or anyone else who can HELP.... )_.....

I have continued doing some work in connection with this Thread..._..
_.. I am looking now at the “ADO” way of bringing my data into the main Excel File.
I went off in an old unanswered thread of mine and answered it .. at least to some extent. ..
https://www.excelforum.com/excel-pro...ml#post4641706
https://www.excelforum.com/excel-pro...ml#post4643207
https://www.excelforum.com/excel-pro...ml#post4643208
The point of that Thread had been to get an better way to write, and , in particular , read Text Files into Excel ( into a VBA Array actually ).. I had thought myself about the “ADO” way, and it way suggested to me also in that Thread.... But I did not know how..
So anyway I solved that Threrad...
The end result is I have
‘_(I)- made a few simplified text files based on my original Excel example data File using for the separator a _ comma , _ Pipe | _ SomethingElse _ C ' mon http://listenonrepeat.com/watch/?v=z...Something_Else http://listenonrepeat.com/watch/?v=lgaWHXcJr7w
PipeCommaSomethingElseC'mon.JPG http://imgur.com/UsAUCeG
As discussed in that Thread, I have, for now, missed out the first 20 rows because of some issues. For orientation, in that shown screenshot, the text, C21 , is written in the original Excel data File in cell C21 (column 3 , row 21)
So I have those files. So far so good.

‘_(II)- I managed to develop a code based on first principals of all that “ADO” is about, .........well I thought someone’s got to : https://www.excelforum.com/excel-pro...ml#post4643207
I have been quite through, but I had some issues towards the end of that last post ( https://www.excelforum.com/excel-pro...ml#post4643208 )
With those remaining issues I need help with..

My current code is reading happily all those 4 text files, even the silly ones ( https://www.excelforum.com/developme...ml#post4643065 )

The main issue is that I cannot get the reading code to accept a separator other than a comma _ ,
I have goggled like mad, and get the impression that it can be done. It is not always clear or consistent how this is done, but I have experimented a lot, but , as yet , to no avail...
What am I missing... ??

This issue and a couple of others are towards the end of the post ( https://www.excelforum.com/excel-pro...ml#post4643208
) . The actual code there is detailed in great detail also.

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

It might help someone trying to help me if I give a totally stripped down and shortened example, and just address one issue first...
So here I go:

Issue:
The simplified below code makes me a 1 Dimensional , 2 Element Array, arr(), of Variant Member elements, size (0 To 1), that looks like this
{ a|b|c, _d|e|f }

What I want is it to give me this
{ a, _b }

But, important: I want it to do that by having the delimiter set to a pipe |

I have read up about schema.ini Files etc.. and have experimented to death – I have loads of Test Threads at those referenced links above. But I can’t seem to get it
What am I missing... ??

Here a simplified test code:

Please Login or Register  to view this content.

I have tried lots of variations as detailed in the other Threads referenced, including various variations of using a schema.ini File in the same Folder as the Text files
Just one example: If for the above simplified code I make a schema.ini file in the same folder as where the text files in , with what I think is the correct syntax for defining a | as separator for my text file, Test.txt, then I do get a reaction from the code... it errors!!! It says
“Runtime Error ‘-2147217904 (80040e10)’:
For at least one required parameter would no value given
It error at this : _ oRdta.Open "SELECT [F1] FROM [Test.txt]", oCon
SchemaIniErrorPipe.JPG http://imgur.com/DId6jgr
As you see in that screen shot I have this in my schema.ini file:
[Test.txt]
Format=Delimited(|)

Thanks
Alan

It's FMT=Delimited in the connection and not FMT=Delimited()

Hi Don and CK76, ( and anyone else following )
Thanks Don, CK76 very much for coming back here

Originally Posted by xlnitwit

It's FMT=Delimited in the connection and not FMT=Delimited()

I had / have tried that and the error was the same....but

Originally Posted by CK76

Also ...when you use Schema.ini... should have the basic parameters like below. Otherwise.. likely throw error in SQL statement
Format=Delimited(|)
ColNameHeader=False
Col1=F1 Char
Col2=F2 Char
Col3=F3 Char

That does the trick, great. !!! I guess that makes sense.. in a very unsensible sort of a way...The error in the sql statement in the “ defining a Sub data bit from all the “layed open data” code line was indicating it was not finding the column it was looking for. ( God knows what column it was looking for in that case as I guess it had not got any idea about columns......) ....I don’t know if that means that once I start using a schema.ini, then I must use all arguments ( parameters ? ) , or at least those I may be referencing. For example, my simplified code adjusted to just grab the second column _..
( oRdta.Open "SELECT [F2xyz99] FROM [Test.txt]", oCon ) , _...
will work with this schema.ini:

Please Login or Register  to view this content.

Very interesting that I must include the columns up to the one that I want. This tends to confirm a Theory towards the end of this model for Computer “.orPened up Connections”: https://www.excelforum.com/the-water...ml#post4636789

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

The documentation on this is a totally messed up and confusing in my opinion. I was clearly lead to believe I could somehow do selection of the separator in the “.Opening up” sql statement . Indeed the syntax will let me do for example this:

Please Login or Register  to view this content.

and it still does allow me to do this– even with a working code !?!
That is/ was really annoying. It totally threw me off. But looking positive, we appear to have found a great place to keep important ‘comments. I wonder if anyone ever noticed, ( maybe we 3 should do a Patent on it .. lol. ... I guess it is like writing text anywhere in a lot of code environments.. it just gets ignored if you are careful and don’t use words that might get detected as commands... . )
Actually this also works as long as I have a correct schemi.ini_...

Please Login or Register  to view this content.

_.. and while I am at it this will work as well with a schema.ini_..

Please Login or Register  to view this content.

Without a schema.ini , this_...

Please Login or Register  to view this content.

_...will return me { a|b|c, _d|e|f }.

So there is something messed up with the syntax. I do not appear to be able to select the delimiter from the “.Opening up” sql statement. I know I can, for example, select successfully whether I have a header or not. So somewhere someone got their interfaces in a twist.


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

_... So I was stuck, but now I can move on, thanks a lot for helping me out... I’ll move on with this sort of thing now..

Please Login or Register  to view this content.

Thanks again . If I may, I’ll come Back with a few follow up questions later, now that I can move on with getting a column out... using a Pipe delimiter... but not SomethingElse
( I expect if I go back now and read all the documentation it will make a bit more sense... always does when you already know the answer... )

Alan.

Here's bit more on Schema.ini and why you'd need it.

Text files in itself has no information about how the data is structured (Schema), unlike Excel files and databases.

So, without Schema.ini file, provider will use setting found in HKEY_LOCAL_MACHINE\SOFTWARE\...\Access Connectivity Engine\
(Exact location will depend on OS, Office version, Provider etc).

0.JPG

If you know the setting to be always the same you can edit this and skip using INI file (though I'd advise against it).

However, registry is universal and will not accommodate for different setting you may want for each file (or for different text file types).
This is why you'd set up Schema.ini file to specify file specific settings.

FYI - You can query Excel file like below to obtain schema info (change argument for .OpenSchema depending on type of info you want).
And incidentally you can find that for text (VarChar) type column max length is set as 255 when you read provider-type info.

Please Login or Register  to view this content.

Thanks CK76
That all helps me get a fuller picture , which I like to have, of the things. That makes the reasoning behind schema.ini idea much better to understand. These snippets of info you give are very helpful as they are relevant to what I am doing, but that is not always initially obvious to me, so I would need a week of research to get the same info.
I had a quick play with a version of your code here:
https://www.excelforum.com/developme...ml#post4643889
I expect I will come back to that and the info from the last post quite a bit as I wrap up my little foray into reading of my data .. in whatever form ( or formS more likely ) that I finally decide to store it on / in.. etc.. .
( it seems the .OpenSchema method somehow does an appropriate sql command type thing to “open” “reveal” the relevant info... so the info is then held as some sort of “.Opened Recordset”. So it sort of combines a couple of steps like in the “ADO” codes discussed previously. That is probably going to seem very logical to me .. shortly .. for now it seems weird.. like I expect if you somehow “got” the corresponding data from the _ HKEY_LOCAL_MACHINE\SO ......... _ place instead, then I expect you would do it quite differently ... I will have to think a bit on this one......


The idea of the scehma.ini file I like actually, - it suits the way I like to do these sorts of things.
One of my follow up questions was going to be if including a schema.ini file might have any consequences on the speed of running of any of my codes. I think I am seeing now from what you have explained, that the answer to that is probably no. – it is going to get the information from somewhere anyway. I would not imagine any noticeable difference from going to either_..
the _ HKEY_LOCAL_MACHINE\SO ......... _ place you mentioned
or
_.. to my schema.ini file...
I will research again fully what I can put in that schema.ini ..... and certainly mess it up with lots of comments, notes, URL links etc... .. lol..


I need to put some thought into some of my usual habits regarding the size / length of text I put in cells, the first 20 especially. A good compromise seems to be to handle the data in the first 20 rows differently, using mostly spreadsheet / worksheet functions etc. Then I will keep the main data from row 21 in some more conventional way, depending on how I finally end up doing all it is I am doing..( if I can hopefully soon remember what all that was in the meantime ... lol ... )



Thanks again
Alan

( P.s. I don’t think your variable strQuery does anything, unless I missed something )

Originally Posted by CK76

...I keep all purpose ADO code.. edit as needed. ..often forget to remove a variable or two ...

Thanks CK76. I pretty well thought that, but I often miss something obvious, so that’s the only reason I asked.. ( You don’t want to know how many things I forget to take out when I re use a code – my own fault for cluttering with so much extra.. Lol.. )
_...

____________________________._____________________

Some follow up questions: No rush if / when anyone can answer any sometime I would be grateful:

_1 .OpenSchema Method
I Dim ed the variable things retuned by .OpenSchema as ADODB.Recordset, so confirmed it returned such.
I am almost there with all this “ADO” stuff .. Googling tells me things like .OpenSchema works on a connection .Opened object or an open session. I think connection object or an open session sounds like my assessment / description of something being “layed open” , that is to say , accepting your advances, and waiting for you to do what you want to it.
At that point all the hard and possibly time consuming work has been done. The rest is plain sailing. The fact that .OpenSchema returns a RecordSet is just for convenience – it could have been wired to return a simply array of data values, but it uses programs working on databases so it just was convenient to make it return a RecordSet
( I made a mistake in posts #52, .OpenSchema does not .Open as I said in that post. – it actually “works” on a .Opened )
So question 1 is - is all that a reasonable explanation?

_2.a) Am I correct assuming that I can only change my default separator either
by having and using the inclusion of a “schem.ini” file discussed in the last few posts
or
by the changing the _ HKEY_LOCAL_MACHINE\SO ......... _ stuff?
So I cannot do it somehow with something pseudo like:

Please Login or Register  to view this content.

_2.b) Regarding _ FMT=Delimited _ in the ObjConStirng
I have seen this in a lot of code. But I cannot seem to see it doing anything. The fact that I can write FMT=Delimited or FMT=DeplatedWonk or FMT=Annihilated .. etc. suggest to me it is just being taken as text to ignore. Any comments on that ?


_3.a) Once I have a “schema.ini” file, all but “text” is ignored in the ObjConStirng, ( and I expect the presence of “text” makes the code go look for a schema.ini. first, and if it does not find one then it looks further along the ObjConStirng. ) Is this correct?
_3.b) Without schema.ini, and no other parameters after “text” in the ObjConStirng , it appears I am defaulting to HDR=yes; and separator of comma , Is this correct?

_ 4) I cannot get the Property .RecordCount to return me anything other than -1 for a “ADO read text file” code. ( I used it successfully in many “ADO read Excel file” codes ).
I have read and googled that this property is not always supported , but I have not hit any info yet on if that is the case for test files.
_4a) Anyone know that for sure ?
_4b) Anyone know how to determine the Record Count for that Recordset got for a text file without looping until end of file, and without determining the size of what comes back from _ .GetRow() _ or _ .CopyFromRecordset. ?
( I would like to know it before I loop through or get at any records ( rows ) in the RecordSet

_ 5) Why is the word and phrase Record Set used as opposed to something like Data Set ? – As I understand it , a .Recordset returns a sub set of the data available from a “.Opened up connection object”....
This sounds a niggly point, but I expect the answer might explain a bit about what these database things are all about.

_6) Would inclusion of a “schem.ini” file possibly slow a code down. ... ?
I have not done a lot of tests yet and probably will, but possibly someone could confirm what I expect is the answer to be ..., that there is likely not to be any difference as the required info is going to be got from somewhere anyway , and that will likely be very quick anyway

_7a) In some codes I have googled they use the
oRdaST.Open sqlcmdRuedtrSet, oCon, adOpenStatic
In my codes adding the adOpenStatic causes a complitle error ( “Mehrdeutiger Name” – I think that means “Ambiguous” or “more meaning” or “double entendre” name in English )
( adOpenStatic does work in all codes I have done to read from an Excel File to an Excel file )
Why does that not work in my “ADO text reading” codes, and what is that about anyways ??
_7b) Same again for
oRdaST.Open sqlcmdRuedtrSet, oCon, , ,adLockReadOnly
It does not work my codes and gives the same error ?
_..Or.... Maybe I just need a good reference on all those options. I can get this to work , for example..
, ,adOpenForwardOnly
-7c) or just 7 total: Can someone recommend the best of these optional arguments for a “ADO text reading” code, and give an explanation of why / what it is doing-...... ( it sounds, for example, from what I have read and googled, that CursorType:=adOpenForwardOnly will speed my codes up a bit.. and I found that this one worked in my “ADO text reading” codes.

Please Login or Register  to view this content.

_8a) Can anyone think of any pros or cons for Early or Late binding the main two objects (ADODB.Connection and ADODB.Recordset) when used in a typical read Text File into Excel VBA code
_8b) I did noticed something strange by the way....
For Early binding the ADODB.Recordset, these two code lines do exactly the same_..
Do While oRdta.EOF = "False"
Do While oRdta.EOF = False
_.. that is to say , in a typical usage I keep doing stuff until oRdta.EOF is True
For late Binding the ADODB.Recordset, only this code line “works”_..
Do While oRdta.EOF = False
_.. that is to say , with this code line, _ Do While oRdta.EOF = "False" _ , I end up never doing something.
This last one, _8b), is a minor point, and clearly I should do, as good practice, _ Do While oRdta.EOF = False _, but the answer might help me know the difference in what is going on with the Late and Early Binding which, in turn might help me to decide what might be the best to use

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

I have asked quite a few questions there, - if anyone can answer any then that would be great. I will keep experimenting and googling in the meantime and report back if I can get further in answer any myself

Thanks,
Alan

Thanks CK76
This first bit I wrote just before your last post #56, so it is mostly to do with Post #55 from you...
_1 .OpenSchema Method
I was unsure as to whether .Open Recordset actually got ( grabbed ) anything. I was thinking because of the word Open , it did a similar thing to the Connection.Open. – I was thinking then that the_..
“ADO” Method GetRows()
or
Range object Method .CopyFromRecordset
or
.Value Property on “ADO” Field Object ( like _ = oRdta.Fields(0).Value )
_.. actually got / grabbed data... later..
So my thinking was that it sort of “made available”, established the “sub connection” based on the existing main “connection” / “full opened and waiting available” data.
?? – I am still not 100% on this one..
Edit: from another Later answer from you: ( https://docs.microsoft.com/en-us/sql...-ado-recordset ( Open Method (“ADO” Recordset) ) ) .. I get the impression still that it is doing something similar to Connection.Open ...
Here’s a good one “._.....
....”_.......it Opens a cursor on a complete subset of a .Opened up / layed open, connection object, and the actual curser/ RecordSet object is at any one time referring to a record ( row ).
So that goes some way to explain
_5) why they call it a RecordSet
_.. and that goes someway to explain question 7).... all those ( 3 ) optional arguments.
One nice outcome here of the “moving cursor” – along a row, then Movedown to the next row idea explanation is that it supports my measurement and Theories of why you must specify at least up to the last Field ( “column” ) that you want in “schema.ini” , even if you don’t want them all .. – if you miss any out then the cursor gets lost in its “travels” .....

_2.a) Got it, thanks – I can’t change for delimited “text” files in the ObjConStirng

_2.b) OK , thanks for that confirmation. I guess I can ignore using FMT=Delimited as my relevant registry has that probably as default and so my variations on the spellings were, indeed , being taken as text and ignored. ( I don’t think it would be clever for me to mess with my registry, lol.. )

_3a) _ 3b) “Schemi.ini” likely takes precedent over most I write in the ObjConStirng, ( and once you use it you need to be thorough )
Thanks, it’s all making sense. Maybe I will be brave one day and peep in my registry to see thhe defaults. ( or maybe I’ll check the Wife’s, lol.. or on another computer I do not need to badly in case I mess it up )

_4)....about get the Property .RecordCount .. to work
oRdta.Open "SELECT * FROM [Test.txt]", oCon, 1, 3
( Your 1 refers , I think, to CursorType:=adOpenKeyset
Your 3 refers, I think, to LockType:=adLockOptimistic )
It works !!!! – I actually googled some threads that asked but did not get that answered or had an answer that inferred it could not be done.. ( I still don’t have a clue what those optional arguments all means , maybe later... )
But I had a good look..

Please Login or Register  to view this content.

_.........................
This was all beginning to make great sense... : That above was all in response to your post #55

And then I caught your Post #56,- more great info , thanks... had a coffee and started again....

_................
_4a) _4b) Yep, you sussed that one, great!.. I have not seen that anywhere else yet. I can pretty well do all I wanted to now in any further experiments..

_5) Why call it RecordSet and not data set or similar ..
Thanks, That really clears that up for me now. I see now it would be a bit stupid to use a word like data sub set or similar ... because:
_(i) RecordedSet is more suited to what is going on: because I get now that it is to do with the manipulation through a cursor, record ( row ) at a time.
_(ii) data sub set or similar could be confused with Dataset which is something along the lines of a full data “table” or similar, but “ADO”, a don’t do that ..

_6) “having a schemi.ini probably has no noticeable increase in time issues...
Its very helpful for someone in the know to say that to me – otherwise I tend to go off in endless experiments which probably my computers accuracy possibilities would mask any meaningful results...

_7) Info on what the _ RecordSet.Open _ Source _ ActiveConnection_ ExtraArguments _ are all about
Thanks – I caught those extra links as I tried to tie up the number you gave to the named arguments. Also at the original Link you gave, you can expand on it by clicking on a few places and get the number to named argument listings again..
CursorType:= https://docs.microsoft.com/en-us/sql...cursortypeenum
LockType:= https://docs.microsoft.com/en-us/sql...i/locktypeenum
Option:= .. this a bit mixed up , but you get there with these two https://docs.microsoft.com/en-us/sql...cuteoptionenum https://docs.microsoft.com/en-us/sql...ommandtypeenum
So I really need to sit down and have a thorough look at those....

_8a) I think Kyle told me about that oddity on a scrapping code he helped me with ( https://www.excelforum.com/excel-pro...ml#post4443658 ). All of what you said ties up with my understanding of the main differences between the two. Certainly for me the use of intellisense in Early binding is very useful—I needed it to get at a lot of info in all my experimenting here – for example, getting all the named arguments, and their order for using in the unnamed way.
I was actually wondering if there were any consequences regarding how any final version of my codes might perform..I expect not much??
_8b) Such an answer as you gave is also quite helpful, I find personally. You obviously know an awful lot in this area so in a strange way it sometimes helps me not to get totally depressed that I know so little of what is to know in these VBA and related things, external libraries etc.... Clearly it ‘aint possible to know all .. so it is a little less depressing that I know next to nothing.. sort off



Anyways, CK76 you have been very thorough at answering. Thanks so much

I expect I might post a few times more just to share some of my final codes and results. I doubt if I should have too many, if any, more questions, as the help has pretty well got it sorted for me now


Alan

Hi !

A quick stop here for my 2 cents !

Excel 2003 can read and save .xlsx, .xlsm and .xlsb workbooks
(in respect of its limits for worksheets aka 65536 rows by 256 columns)
if updated with Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint File Formats …

"Microsoft.ACE.OLEDB.12.0" provider can play either with 2007+ workbooks as well with Excel 2003 formats !

When a Schema.ini parameter file is not needed in VBA, it may be necessary via a VBScript batch …

Becare also of Windows regional settings as CK76 and I we have recently experimented on another forum :
a working code with Schema.ini on CK76 side does not on mine.
Just 'cause in Europa we often use comma as decimal separator instead of point which was the separator of the text file,
solved adding a reference for decimal separator within Schema.ini …

Originally Posted by Marc L

.....quick stop here for my 2 cents

Hi Marc,
Thanks very much for stopping by. You are very welcome in the Thread !

_ _ ‘....Excel 2003 can read and save .xlsx, .xlsm and .xlsb workbooks.....in respect of
Update...Compatibility Pack for Word, Excel, and PowerPoint File Formats ” –Good to know thanks. I currently have got now both 2003 and 2007 on one machine. So jumping back and forth should not be a problem. I have not encountered any issues yet. :-)

_ _ “.......Microsoft.ACE.OLEDB.12.0" provider with 2007+ ... and with Excel 2003...”
Yes, I did it “....ACE stylio on a .xls File....” .. as one of my comparison code tests.... https://www.excelforum.com/developme...ml#post4637122 ..

Please Login or Register  to view this content.

_ _ “........Schema.ini parameter ...... may be necessary via a VBScript batch.........” VBScript batch - I do not know what that is or means ??? ( I have old books on VB and VB 6 and VBScript . And I did a short course at Night School on VB 2010. I think that is all code similar to VBA, but also Console stuff and lots to do with Forms stuff )

_ _ Decimal separator etc..... , and .
, and . – AARRRGGHHHH - I cry!! - I have German Excel. I do much work in English, for me, and in helping in Forums.
, and . are one of the biggest problem in my life !!!
I hate Excel and when it mixes up , and .. When Excel does this I swear a lot. I say Poo and many other very rude words...
It makes me Ill ! It makes me cry! It makes me scream. It makes me puke up. It makes me do things that are so very disgusting and rude that I had better not say!
This is one reason why I want as separator in my text files a pipe instead |
I try very hard already to overcome and prevail.
_ I do make Functions that help ( http://www.eileenslounge.com/viewtop...=22850#p177528 )

_ I use mostly text for all numbers. I find all Excel Functions are kind. If I give functions a text that “looks” like a number then if the function was expecting a number it always excepts a text the “looks” like a number.
_ I try to never use more than 2 decimal places for numbers greater than 1 . – I do this 2.34 or 654.23, or 0.0876876487126, but I never do this 6.987 _ I find then that Excel is less likely then to mix up , and .
_ I try to always use a . as decimal separator. Now I have this info also..............................”.... reference for decimal separator within Schema.ini....”... _ This is helpful info, Thankyou _ Now I will do this also.

Thanks Marc for your Input, please stop by anytime :-)

_...............
_.____________________



I am making good progress now and almost understand all.

But I am stuck two days on a few things

Can anyone help
_.....with the three arguments : ___, __, CursorType, LockType, Options
I have lots of links ,, for example
https://docs.microsoft.com/en-us/sql...-ado-recordset
https://docs.microsoft.com/en-us/sql...cursortypeenum
https://docs.microsoft.com/en-us/sql...i/locktypeenum
(?? https://docs.microsoft.com/en-us/sql...ommandtypeenum ?? https://docs.microsoft.com/en-us/sql...cuteoptionenum ??)
https://www.w3schools.com/asp/prop_rs_locktype.asp
https://www.w3schools.com/asp/prop_rs_cursortype.asp

Has any one other links, easier to understand? Any better/ simple explanations themselves?
I need explanation that I can understand. I have little computer background.

Some aspects I do understand, thanks to this Thread. For example I do understand this:..
This in Red is a RecordSet object. ( At any one time the Recordset object, oRst, refers to a “complete row” ( Record ). Various Methods and properties can be used on the oRst. o give actual data values )

( All screenshot is an .Opened available ready and waiting” Connection object )

Cursor F1	Cursor F2	Cursor F3		Cursor F4	Cursor F5
Cursor	Cursor	Cursor		Cursor	Cursor
Cursor	Cursor	Cursor		Cursor	Cursor
Cursor	Cursor	Cursor		Cursor	Cursor

Recordset .Open will define where Cursor can traverse, - all Cursor is where a Cursor can go. All above I understand

But I still very confused :-( with the three arguments : ___, __, CursorType, LockType, Options
I have tried very hard to understand. I speak English and German. I have spent many hard hours reading and experimenting but cannot understand yet.

I need an explanation of those three arguments ( and the different options ) that someone with next to no computer knowledge can understand.
All and any attempts to educate a computer idiot like me would be greatly received.

I expect no one person will know anyway all the options for all the arguments, but if anyone has a good idea for what some of them actually do, then that, in conjunction with all the reading I am doing , might help me to finally get a general picture


No rush, I will keep reading, and experimenting. It might suddenly click.

Thanks again
Alan

Hallo Kyle,
LockType
“.......LockType .....pointless ... not relevant fro .... to do - .... juicy "optimistics -pissimistic ...." certainly I had often missed that argument out , in experimenting, with not noticing any effect, but it is good to know it is probably OK to do that. It makes sense now you say it.. Being told continuously that ADO is really stuff for ACCESS is also something I need to have drummed into me often , then re reading stuff makes a bit more sense or is more understandably understandable.. I know I have a problem there but really think for me it might be unwise to get into ACCESS.
Actually when I did google LockType Optimistic vs Pissimistic ADO, http://bfy.tw/Bcpl , after it corrected the spelling it hit some slightly easier to understand stuff.. well a bit anyway,
Maybe I can live with leaving it out. It seems all my Excel code will work with out it, - but I am yet to see any code without it ( I mean the ones I have seen to do with Excel and text ) , maybe every one forgot they were no longer in ACCESS,
So possibly I will leave it out
_.. or who knows, maybe I will be optimistic.. CK76 was in the combination that first got my Property .RecordCount to work, I mean Optimistically ,looking at it, keep lose and relaxed on locking anything up, - ‘aint no one can sneak in while I’m doing stuff, and if so , hey why not might be interesting. – let’s not be pessimistic about these things... , in fact most people are , or worse , they adLockReadOnly so they are really stupid – might be like locking up your chastity belt for the night after someone has locked you in the vault. .. I am beginning to think that I may not be strictly speaking using a data base , so some things ** irrelevant.. Hey I think someone said that. The crutch to it is possibly only usually in the case of data bases “....other users may have updated records whilst you have the recordset open......” maybe that is obvious with hindsight, but I had not twigged because of no ACCESS experience. So hence having that in my head on re reading makes more sense..
So I guess I may either leave out the Lock argument, or keep optimistic, you never know – I might capture one day something unexpected,- something that leaks in then I will have caught a good trick or way to sneak something in. Maybe in hanging lose, something less could be done , which might improve speed. Also, I would prefer not to miss out something that might then be unknowingly given a default. So just a personal preference, which i note some professionals have to, – I would prefer to write in the default.. This leads in to a question...
I have a question: Am I likely to have any defaults for the arguments, ?? - or is the answer to that , that I have no registries associated with ACCESS as I don’t have ACCESS, and that is where something like that might be..??

_......
**CursorType
I think we might have talk about this one I am afraid: Sorry ( If and when you have the time , oof course :-) – no rush ) ...That is where it all started. I was happily accepting these things, when .. .. “ oh dear my “ADO” text codes does / did not work, ( https://www.excelforum.com/excel-pro...ml#post4645363 ).
I was happily using,
adOpenStatic ( in most codes that I have stolen this is given and it works in all my Excel “ADO” codes )
I kept using adOpenStatic , as I was being conformist and boring hoping not to find something more to investigate , but it don’t work. It errors in all my “ADO” text codes so far with Compile error ( ConpileErrorOpenStatic.JPG http://imgur.com/FDITMDQ: “Mehrdeutiger Name” – I think that means “Ambiguous” or “more meaning” or “double entendre” name in English )
I actually hit on
OpenForwardOnly
myself as I read it might be quicker, and I can’t imagine any case where I would read more than once – If I come back to get, for example just the TOP few rows , or just some rows WHERE something suits me, then I am going to have to make a new Recordset anyway, and start once again. With OpenForwardOnly , the codes then did not error
But then .RecordCount does not work.

CK76 gave me
adOpenKeyset.
Then the codes and .RecordCount work..so that started me thinking.. Why ? .... and what is everything ....
So clearly ( I suggest with respect ) there is something going on with this CursorType argument. I need to choose it correctly. So I would really like to have at least some understanding. I have been on this days now reading books and blogs. No one seems to actually say clearly what it is or does. All assume you know and discuss the options in such a way as totally incomprehensible, ( to someone who knows nothing ... about data bases If you tell me to go learn about data basis, I will say Bollox in black instead, but I would only be saying it in fun, x )
No CursorType given ( at least in code )
Initially appears to work like OpenForwardOnly
_.....


Options
I’ll leave my Options open, for now..
I’ll post the rest where no one is looking

_......
Summary ... ( For CursorType )

Originally Posted by Kyle123

....- use OpenForwardOnly if you can live with only one pass through the recordset or OpenStatic if you need multiple passes. The others aren't relevant for text files.....

I am sure you are correct , technically speaking , but my problem is
adOpenStatic _____ -- does not work, errors for my “ADO” text file codes
adOpenForwardOnly –- code works but .RecordCount doesn’t
adOpenKeyset ____ -- appears somewhat relevant to me, as only then do my “ADO” text codes work, and .RecordCount also works
I want to know what is going on
Frustrated Alan

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

Here is a demo code again,
( The only reason I uses an ArrayList is for convenience to not keep Re Dimig an Array for the data imported– In the final use I would like to pre define the size of the Array. Hence I would like .RecordCount to work )
It is standalone code as it makes the test File it gets data from. Also it makes the Folder you need as the To be Layed .Open and waiting for it connection object.
You had better not add a reference to this text file in a schema.ini , unless you know what you’re doing with your system delimiters )

Please Login or Register  to view this content.

In the above code :
Use:
No CursorType:= ( leave it out ) --- code works, but .RecordCount doesn’t
CursorType:= adOpenForwardOnly --- code works, but .RecordCount doesn’t
CursorType:= adOpenStatic ----- does not work, errors for my “ADO” text file codes http://imgur.com/FDITMDQ
CursorType:= adOpenKeyset –----- All is well, except its irrelevance relatively speaking to my way of thinking
_......Edit: I did a slightly differnet code ( using a With End With and setring properties through object refs instead of through arguments to Open method, for ease of experimenting ) here ( https://www.excelforum.com/developme...ml#post4641597 ). I did extensive further measuremetns I also included a oRst.CursorLocation = adUseClient , which cured all combinations of LockType and CursorType for the code to work with working RecordCount, other than still getting complie error for CursorType = adOpenStatic or LockType = adLockReadOnly . Intersting documentation suggest this somehow use only adOpenStatic for CursorTypes regardless of which CursorType you select, which might sxplain the specific error got by using adOpenStatic – somehow somewhere it was always defined , declared or similar , l ##?? leading to the complie error in its use ?? – So maybe in some strage way I have anwered a Question of mine
........._.....

Thanks for anyone for further help. I will keep reading stuff and experimenting
Alan

Re: Cursor stuff

Originally Posted by Doc.AElstein

... question: Am I likely to have any defaults for the arguments, ?? - ..

Some measurements here _..
https://www.excelforum.com/excel-pro...ml#post4649168 https://www.excelforum.com/developme...ml#post4648994
_.. give some very good results, both for speed whilst .RecordCount is also still working if I include a line
_ .CursorLocation = adUseClient _ .
That code line is not part of .. _ oRst.Open Source:=____ , ActiveConnection:=____ , ..TheseOther, Argumants
The measurements suggest once this option is chosen, TheseOther, Argumants are ignored.
Exception as seen here https://www.excelforum.com/developme...ml#post4641597 are _..
LockType = adLockReadOnly
or
CursorType = adOpenStatic
_.. which still cause a complie error ( ConpileErrorOpenStatic.JPG http://imgur.com/FDITMDQ: “Mehrdeutiger Name” – I think that means “Ambiguous” or “more meaning” or “double entendre” name in English )

Something screwy is going on and the error might be telling me I can’t use these options more than once in …. Whatever …. But to use the ones already … Whatever …. I must get at them using
_ .CursorLocation = adUseClient _
God knows WTF is going on.

Coming back to a simplified standalone code example, this would be the “best so far”_...

Please Login or Register  to view this content.

_.... maybe it is using LockType = adLockReadOnly or CursorType = adOpenStatic …. On the predetermined client side…. WETF that means.


Well I do not know what I am talking about….
_ Does anybody else ???

_.......

I will leave this and the last post hanging , pseudo on adLockOptimistic, while I update some further records from this open Thread object without hitting the refresh, then the lock on the post number will be done when I post that, so then I will look back to see if there is any previous update come in or if anything leaks into my brain in the meantime…
I didn’t understand that either

_....

Just by way of an update on a couple of things:……
Update 1

Originally Posted by Doc.AElstein

I may .... buy a cheap version of 2003 Office from ebay or wherever and give it a go myself. In any case I will feedback my findings....

Originally Posted by Marc L

......Excel 2003 can read and save .xlsx, .xlsm and .xlsb workbooks
(in respect of its limits for worksheets aka 65536 rows by 256 columns)
if updated with Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint File Formats …....

XL 2003 update. Second Installation
I installed from a second disc that I bought just now a XL 2003 on my main Computer, Acer Aspire 4810TZG. Installation went OK.
Same procedure as the last time _.............. ( https://www.excelforum.com/excel-pro...ml#post4642191
( I just stuck the disc in the computer disc drive, and then a bit later I double clicked on the .exe File which was shown automatically in a pop up which came up shortly after I inserted the disc ).
I had to do the custom installation to prevent it wiping out the existing XL 2007
I also unchecked WORD and OUTLOOK so that I only got XL2003 installed.
It asked me fairly early on in the installation for the 25 character product key which was included on a sticker on the disc holding cardboard sleeve. ( The disc and sleeve was in very good condition, and I expect it was in the original condition as bought originally )
At the end it asked me if I wanted to activate it per telephone or over the internet. I chose internet and it seemed then to do something very quickly..
The first time I opened the newly installed XL 2003, it opened up with what looked like an Excel File , but which had totally unreadable garbage characters all over it………………… )
_................................................
_...Second Installation
XL 2003 opened after the installation OK. But on this computer I have a personal workbook. It opens automatically when I open Excel 2007 and it tried to do that with the newly installed XL 2003, but it came up with garbage
So eventually I found something on the internet:
Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint File Formats https://support.office.com/de-de/art...rs=de-DE&ad=DE
https://www.microsoft.com/en-us/down...ails.aspx?id=3

I had to be a bit careful and uncheck a few extra things they offered on the first page of the download, although I did check the “The 2007 Microsoft Office Suite Service Pack 3 (SP3)”
So I ended up just before I did it with this as a “Download Summary”:

Download Summary:
1. Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint File Formats
2. The 2007 Microsoft Office Suite Service Pack 3 (SP3)
Total Size: 388.9 MB

I ended up with a “FileFormatConverters.exe” ( https://app.box.com/s/sn6vpjt6x7hrdu3aapjb16z20q5t1pp3 )
I opened it and it went through the normal downloading procedure
It seemed to work. But as often the stupid Bl..dy thing did not tell me where it put it. ????
Some time later a pop up told me Microsoft wanted to give me this as well “office2007sp3-kb2526086-fullfile-en-us.exe”. ( https://app.box.com/s/my7le0pd4piiwwwf577pqgngprijgflt ) So I downloaded that as well. I opened that and it did the first step in typical downloading, which was to ask if I wanted to do it. As normal I said yes. For about half an hour nothing happened . I double clicked on it in the Folder where it was and it told me it it was not a valid Win 32 thing.
???? I went back to looking for the downloaded and installed add in.
XL2003LookForAdd-In1.JPG http://imgur.com/Q04oZ7h
XL2003LookForAdd-In2.JPG http://imgur.com/42Ki6ip
After two hours I still could not find it.
My Personal workbook still comes up as garbage in XL 2003. Never mind – I just close it. Keeps asking me to accept the licence agreement as well )

I am lot too bothered about this. I think I will probably find it when I’m not looking for it

_.______________________________________________________

Update 2
Schema.ini files are good things, when you know about them.

Originally Posted by Marc L

...Because also of Windows regional settings as CK76 and I we have recently experimented on another forum :
a working code with Schema.ini on CK76 side does not on mine.
Just 'cause in Europa we often use comma as decimal separator instead of point which was the separator of the text file,
solved adding a reference for decimal separator within Schema.ini

That actually answers a question I had along the way.. I had this error_...
SeperatorErrorInIni.JPG ‘-2147467259 (80004005)’ “The field separator for the text data is that of the decimal or text separator” http://imgur.com/oa08NrI

_.... Now I don’t. – Why?
Let me tell you in a simple example ..
Here this simple demo code “works” without a schema.ini file.

Please Login or Register  to view this content.

It makes a simple text file_..
celA1,celB1,celC1
celA2,celB2,celC2
celA3,celB3,celC3
_.. then , assuming that is a representation of a 3 row x 3 column set of data, it displays the second column.
CelB1
Xxxxx
CelB2
CelB3
So far so good.
Then I thought I was am clever, because I learnt about a schema.ini file in this Thread. So I made a .txt file, renamed it schema.ini and saved it in folder “DBTextFiles”
What I also learnt in this Thread is that once I have a schema.ini. then in that above code the part of the Layed out .Opened connection string which I have, in that code, out in the variable ScOp , will be ignored, and it will seek that and other information in the schema.ini ( and if not there, use the default value in the Windows Control Panel is used ).
So I think I am clever and do write this now in schema.ini
I put this in it

Please Login or Register  to view this content.

But his does give the error ( SeperatorErrorInIni.JPG ‘-2147467259 (80004005)’ “The field separator for the text data is that of the decimal or text separator” http://imgur.com/oa08NrI )
So Does this error:

Please Login or Register  to view this content.

So I read this again..

Originally Posted by Marc L

...Because also of Windows regional settings as CK76 and I we have recently experimented on another forum :
a working code with Schema.ini on CK76 side does not on mine.
Just 'cause in Europa we often use comma as decimal separator instead of point which was the separator of the text file,
solved adding a reference for decimal separator within Schema.ini

.. I do think ...Hmm .. I google ... I do try this

Please Login or Register  to view this content.

Now all is well
Also this is good:

Please Login or Register  to view this content.

All is well when ya know a bout ya schema.ini

_............................._______________________--

I'm not sure of what is going, but I have a question.

Why are you using Late and Early Binding together?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Originally Posted by mike7952

I'm not sure of what is going, but I have a question.

Why are you using Late and Early Binding together?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hi,

That is purely early binding. How you instantiate an object is irrelevant to binding context; it's only how you declare it that determines whether it it early or late bound.

Hi Don, Hi Mike,
@Don. I expect Mike may not have meant what you thought he did……_..###
_...................
_...@Mike
Hi Mike

Originally Posted by mike7952

...Why are you using Late and Early Binding together?...

When I do test and experiments on codes like these I often test with Late and Early binding, just to see if anything performs differently. That does not happen very often, but occasionally there may be minor differences. ( I noticed one unusual thing ( Point _8 ) , and we discussed these things generally around Posts #54 and # 56 )

So in some of the codes discussed in this Thread I might have tried out all or some of these 4 variations:

Please Login or Register  to view this content.

When I post a code in this Thread it will have whatever combination of Early and Late Binding it had, coincidentally, based on my last experiment. It will vary randomly every time. Some times Late and Early binding will be mixed , sometimes not. There is no significance …( I understand the basic Pros and cons of Late and Early Binding. For example, like many, I usually start in development with Early Binding to get the advantages of intellisense, then in sharing usually use late Binding so as not to have to explain to an OP about setting a reference etc…. )
_._____________________
###@Don

Originally Posted by xlnitwit

....Set oCon = CreateObject("ADODB.Connection"):
_....Dim oRst As ADODB.Recordset: Set oRst = New ADODB.Recordset
_....That is purely early binding. How you instantiate an object is irrelevant to binding context; it's only how you declare it that determines whether it it early or late bound.

I expect you might have thought that Mike was referring to me doing something like this..
Dim oCon As ADODB.Connection: Set oCon = CreateObject("ADODB.Connection")
Dim oRst As ADODB.Recordset: Set oRst = New ADODB.Recordset
_.. I have never ( at least intentionally ) done anything like that.
But it is interesting. I guess this is saying something similar to what I have also heard “…. If you Dim as Object then it is late Binding.. ..”…

I assume in these two cases _...
Dim oRst As ADODB.Recordset: Set oRst = New ADODB.Recordset
Dim oRst As ADODB.Recordset: Set oRst = CreateObject("ADODB.Recordset")Recordset
_...that Set oRst = New ADODB.Recordset and Set oRst = CreateObject("ADODB.Recordset") are doing the same ???

_._______________________________________

_..

Originally Posted by mike7952

I'm not sure of what is going,.....

In this Thread I have been looking at a few different “ADO” ways to get data from a closed data Workbook ( or from a closed text file made from the closed data workbook ).
The main current issues are me trying to understand a few “things” / parameters/ properties of the Recordset object when using a code to get the data from a closed text file.
These are those things
CursorLocation Property
CursorType Property
LockType Property

I expect I have read now and have the links to just about all documentation on this. I am unable to get a “Layman’s” understanding, of these things to satisfy me, probably as I have little knowledge of databases.

A few people have suggested it is of little importance to what I am doing and has little effect.
With respect that is not the case.
My experiments show differences ranging from differences in performance to .RecordCount Property not working to Compile errors with certain combinations.
Clearly I need to chose carefully what combination I use. Some suggested combinations do not work. Others give reduced performance ( speed )
It is not a big issue. I can find empirically the best combinations. I just prefer to understand as much as I can abut the codes I use in the practice.
If all else fails I will probably come up with another of my wild theories to explain all… Lol….
Alan

( Second part of post #59, and Posts #60 #61 #62 are the current stand of the main outstanding issue. ). I really need someone who understands these things to somehow translate it into layman’s terms that I can understand, such that it might explain the results that I have summarised in those Posts. Kyle had a good go, but his suggestions do not match the results I get. – That’s all discussed in post # 61
I can’t really make anything clearer.. I expect I may be noticing some odd quirks that no one has ever noticed before, so no one really would have an idea what is going on… Unfortunately that happens often when I look a bit further … Lol…. Never mind, I’ll get over it ,.. lol… )

Thanks for stopping by
Alan

Thanks CK76, Kyle
Every take from someone that knows helps build up the picture for me..

Pessimistic: That is similar to what I am understanding, thanks CK76. ( I never grasped anything about a version number, but it makes sense…sort of )

Optimistic: I got the point I think that you are optimistically thinking probably no one will mess with your stuff while you are doing something so do not “lock everyone out”. So when you finished you do a check to be sure. I also read that the last one wins – who finished last gets shown as newest. But I think I am reading from you and elsewhere that it is obviously a bit more subtle than that.

This is all, I know, not so relevant, to me, as I am not really dealing with database.
Also, as I may have mentioned, I just tell everyone I will kick them in the Bollox if they mess with my data, and so I have no issues with getting any versions mixed up, or people accessing my data whilst I am. .
But I am getting the mentioned differences in what actually happens with the difference options. I was hoping to get some understanding so as to explain those differences, rather than just relying on empirical measurements to find the best combinations.
Thanks for coming back.
Alan

I found a dil-ado , but I am not sure what to do with it. Can anyone help?

Hi,
I thought I would be brave and mess with my registry files and the such, so as to further my experiments in a quest to understand what all this “ADO read Text File data ” stuff is all about

So from this thread I got the idea to look here HKEY_LOCAL_MACHINE
I expect that is hidden in every computer somewhere else.
I found it in mine, by following the 4 steps shown here :
Regedit124.JPG http://imgur.com/f9npIXv
1
2 _ here I double clicked on Regedit.exe
3 _ Step 3 was a warning to get the Administrator rights to continue. I am an Admin at my Computer – So I accepted
4 - So I ended up with the Registry-Editor Window up. I clicked my way down a Folder chain, and got for example here along this path: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\Format
There I saw my default separator:
CSVDelimitedInRegedit.JPG http://imgur.com/WmNohZ1

Before I did anything I ran this demo code that I have used a lot in this Thread:

Please Login or Register  to view this content.

It makes a simple text file, fills it with data, and from that file it then fills the Array, arrRcds() , with the second column data, ( assuming the data is separated by a comma )
So just before the code finished arrRcds() looks like this, ( it assumes that “celB1” is the header for the second column of data ):
celB2
celB3

So I double clicked on Format and got this little box up: TextFormat.JPG http://imgur.com/FQyHaCx
I changed Format __ CSVDelimited __ to Format __ Delimited(|)
Then I hit OK and closed everything

I ran my code again and it errored thus time with
Runtime error “-2147217904(80040e10)’:
For at least one parameter no value is given
RuntimeErrorForatleastoneparameternovalueisgiven.JPG http://imgur.com/cKRXHQZ
I wasn’t upset. In fact I was quite happy
Then I was really filled with joy and happiness when the code worked after I changed the bit of the code that defines the text of the text file to this

Please Login or Register  to view this content.

_.......
So I was inspired to see if I could somehow do something similar to Cursor stuff to help me further in my endeavours to find out what all that was about.
I thought I will have a look around and see if I can find anything at all looking vaguely like any of these_..
CursorLocation = adUseClient
CursorType = adOpenStatic
LockType = adLockReadOnly
_... Then the idea would be to mess about with them and see what happens in some of my codes..

I googled, read a few times my own ramblings in this and related Thread posts , and came to the conclusion that I am looking for some dil thing having something to do with ado, like a dil-ado
I found one here:
C:\Program Files\Common Files\System\ado\
DilLaDo.JPG http://imgur.com/VrfBjYn
“msado15.dll” https://app.box.com/s/sefek8my6bw0xyjxq54bvjbgkoodrsmp
I opened it with the text editor
This bit looks promising:
MyDiLADOCurseLok1.JPG http://imgur.com/ktXGZyw
MyDiLADOCurseLok2.JPG http://imgur.com/8tJ4ipf
But I am not quite sure what to do with it
Any ideas
I have a feeling my current settings might be something like LockType = adLockReadOnly and CursorType = adOpenStatic
But that is a bit of an inspired guess based on various things already discussed in this Thread.
I would like to change those to variations of-..
LockType = adLockBatchOptimistic
LockType = adLockOptimistic
LockType = adLockPessimistic
CursorType = adOpenDynamic
CursorType = adOpenForwardOnly
CursorType = adOpenKeyset
_.. then see what that does with my codes. But I am not sure how that relates to anything in that dll ado File.

I will probably just experiment and see what happens in a day or so. But I thought I would ask the question first in case anyone has any suggestions .
It might even be that someone will say - “Don’t mess with that stuff you idiot” – fair enough – all advice or comments are welcome

Thanks
Alan Gamut