excel strqry connection error

[dorabajji]

Hi,

Please Login or Register  to view this content.

i am getting the following error, when the line comes in Rs.open strqry,con,3,3,1

Can any one explain me what is 3,3,1 integer denotes?
This is urgent, if anyone can tell me what this is asap, it would be helpful..
Thanks in advance..

Since code is blocked by firewall, i am also attaching that..

[dorabajji]

pl see the error too..

[CK76]

3, 3, 1.

Is respectively CursorType, LockType and Options (CommandTypeEnum or ExecuteOptionEnum).

CursorType 3 = adOpenStatic

LockType 3 = adLockOptimistic

Option 1 = CommandTypeEnum, for adCmdText.

Have a read of links below.
https://www.w3schools.com/asp/met_rs_open.asp
https://docs.microsoft.com/en-us/sql...ql-server-2017

The error indicates something is wrong with your query. At a glance, it looks like you are trying to access cell within sql string. I'm guessing that's causing issue here.

To make it easier to debug your query string. After it's been constructed, add "Debug.Print strqry" to the code and see what comes up in immediate window.

EDIT: FYI it would be easier for us to help you if you can upload sample workbook. Also, I'd recommend not querying the open workbook (i.e. ThisWorkbook). There is known memory leak issue associated with it.

[dorabajji]

Hi Rorya Sir,
Yes, from what i see from the file is that..
it is connecting to two sheets in the file and from a certain range, it gets headers and values from those two sheets and form a third sheet..

Sir, i have one doubt..
THis was working fine, till a fewdays ago.. now y suddenly it gets problematic..
Eventhough the code is not changed or nothing is added to those sheets in the used range.. but suddenly this problem pops up..

I checked even the original file, it also now poses the same problem..

[CK76]

There isn't enough info for me to help you further I'm afraid. But if I were to take a guess...

Eventhough the code is not changed or nothing is added to those sheets in the used range..

This leads me to believe, some cell (that was used already) had changed, most likely being one of the cells holding header.

[dorabajji]

Hi Sir,

I am getting the following in immediate window..

[dorabajji]

Sir,
I am new to this strqry.. from this select method, i understand that the headers and data are copied? what is split here doing?
What is the puropse of split in this and what it means in this scenario..

[CK76]

Sql syntax itself looks ok (except for potential ambiguity in column name). So, likely issue is then in the source workbook data structure.

FYI - For ADODB connecting to Excel workbook, it's always safer to use IMEX=1. As from your query, it looks like some columns may have text / numbers mixed in.
ADODB will guess column data type based on first x # of rows in a given column. So it's safer to force interpretation as text (IMEX=1), rather than risking it returning null for fields that does not match guessed column data type.

EDIT: [split] is a column name. Though since you don't specify which table it is from in SQL query, there can be ambiguity created.

It is best practice to use alias for tables.

Ex:

Please Login or Register  to view this content.

[dorabajji]

so where should i insert imex=1; when the connection establish? or when strqry,con,3,3,1 is done...
Where to fit in..
Ok , as you say the numbers and text are mixed in.. if that is the case, cstr is used for converting into text .. so it wont change the datatype?

[CK76]

In ADODB you cannot specify column data type. So it uses guess on source data type and CSTR may not work if column is guessed as text, as numeric values may be evaluating to null before being supplied to CSTR.

IMEX=1 should be in connection string (not in recordset.open) at the very end.

Please Login or Register  to view this content.

[dorabajji]

Sir,
In the original file , in both this sheet(two) and resultant sheet.. only columns apart from in this connection are added/modified ... for eg: in account sheet col j to m is used.. for a different macro and then contents are deleted.. but the used range remains ..

In the same way in resultant sheet col xa is used..

but not the ranges specified in the strqry are modified in the source file..
Actually source file is the current used file.. same file/not a different workbook.. source is taken from two other sheets in the same workbook and puttogether as resultant third sheet..
I dont understand how these headers are merged and formed as two column sheet in the result.. what basis the data is taken, i am not understanding..

[CK76]

Sorry, but can't help you without looking at source file.

Typically, I don't recommend use of ADODB code, unless you are very familiar with how it works, and/or you can enforce very strict data governance.

There are easier way to bring in data in modern Excel (such as Get & Transform/PowerQuery).
Or in case of older Excel, using Auto-Filter/Advanced Filter etc (Granted, you do need to have workbook open).

[dorabajji]

Sir,
When i inserter imex also , i am facing error..but different one, before itself showing up..

[dorabajji]

THis is the new error..

[CK76]

Oh my bad. IMEX should be within single quote...

Please Login or Register  to view this content.

It's part of Extended Properties.

But why change the code? You stated that it worked as is before. Hence, my suggestion that issue lies with your source. Not with code.

[dorabajji]

Hi ,
I have just attached the heading .. as you told, headings are not picked up correctly from source..

[dorabajji]

sir,
the problem is .. i cannot provide you the workbook or trying to give a sample book.. as i dont know sql excel connection.. it is tough for me to provide you..
But i have completed the file, but this previous work done by someother person, is posing the problem and i am not able to find it.. i tried with the original file, which was given.. now it is giving error... where as the original file, the file i worked on were working fine..
So i deleted all the macros i put and also checked..
I have just used these 2 sheets for 2 macros but have not used in the range provided for this connection..
completely at see.. so only am trying all the possible solutions provided by you.. if something clicks, i can then find out the issue..

If source is the problem, the file which was original(only with this macro and not added any other macro).. should work fine?
and i am not understanding, when the same file works yesterday, how does it change today.. I had even restarted excel and system.. am not understanding certain aspects and getting the pattern, i think that is the trick am missing..

[CK76]

Unfortunately, it's near impossible for me to pinpoint cause without looking at the source file(s). As ADODB code is very dependent on source data structure.

Try breaking down your query into individual parts (i.e. run each nested select statement separately and see if it returns result). If it produces error, you have narrower scope to check for error.