Run Time Error ! Join Not Supported

[sqldevrequiretektips]

Dim strSql As string

strSQL = "INSERT INTO tblWLActCat_WLDay ( CustLifeNo, WLActCatId, WLDayId, WeekNo )" & _
" Select A.Cust_Life_Num, A.WLActCatId, A.WLDayId, A.WeekNo" & _
" from WLActCat_WLDay A Inner Join" & _
" (select distinct (cust_life_num) from CUSTTB Where " & sqlDB & " ) B " & _
" On A.Cust_Life_Num = B.Cust_Life_num" & _
" And A.WeekNo In (1,2,3,4)"

scon.Execute strSQL, , adCmdText + adExecuteNoRecords

I am trying to run it from VB form command click event
I have error "Runtime error ! Join Expression Not Supported"

When Execute above query in SQL Server Query Analyzer..It's work fine...

Is anybody know what should i do, Is anybody know another way to execute query...to get same output..

Please help me out...

 

[WhiteKnight57t]

debug!!

write out the String of the strSQL before you execute I bet a variable is missing!!

==========================
Sorry to be terse
some say it's a curse
I know it's worse
I'm just diverse
-Tim

 

[sqldevrequiretektips]

Thanks for your reply...but I don't think there is a variable missing in my sql..

see below...


Dim strSql As string

strSQL = "INSERT INTO tblWLActCat_WLDay ( CustLifeNo, WLActCatId, WLDayId, WeekNo )" & _
" Select A.Cust_Life_Num, A.WLActCatId, A.WLDayId, A.WeekNo" & _
" from WLActCat_WLDay A Inner Join" & _
" (select distinct (cust_life_num) from CUSTTB Where " & sqlDB & " ) B " & _
" On A.Cust_Life_Num = B.Cust_Life_num" & _
" And A.WeekNo In (1,2,3,4)"

after this i use debug.print
In Immediate window --

INSERT INTO tblWLActCat_WLDay( CustLifeNo, WLActCatId, WLDayId, WeekNo ) Select A.Cust_Life_Num, A.WLActCatId, A.WLDayId, A.WeekNo from WLActCat_WLDay A Inner Join (select distinct (cust_life_num) from CUSTTB Where (Region_Num = 50) ) B On A.Cust_Life_Num = B.Cust_Life_num And A.WeekNo In (1,2,3,4)

so as you say variable missing..I double check it there is now way variable missing...

So please help me out why ADO doesn't support this kind of join...

scon.Execute strSQL, , adCmdText + adExecuteNoRecords

Thanks
AD

 

[sqldevrequiretektips]

Let me tell you more in detail...

If i remove the A.weekNo In(1,2,3,4) then it execute fine...

But I want A.WeekNo condition in my result output...

Thanks
AD

 

[Sheco]

Perhaps there is a misplaced space or something...if you cut-n-paste from the immediate window to the query analyzer then it works ok?

It is strange that it would work from Query Analyzer and not from ADO... you know you can use a couple different transports in your ADO connection string... maybe one is better than another for this.

just a thought but if all else fails maybe you could put it into a stored procedure... it looks like the only input param you would need is the text for (Region_Num = 50)

 

[sqldevrequiretektips]

If i cut and paste from immediate window to query analyzer it works fine...


I can't use storeprocedure in my case..
Reason :tblWLActCat_WLDay (Local Access Table)
WLActCat_WLDay, CUSTTB (Accessing from Ms Access through link tables..- SQL Server tables )

So, I want to insert the records in Local MS Access Table called tblWLActCat_WLDay.

And if I know I can pass input parameter on stor procedure and retrive recordset then i can insert into to my local access table...

set command.commandtext = strsql
command.commandtype = adstoreproc
Like set rs = command.execute

but when Records i am receiving might be more then 200000
and it's take so long to insert 200000 records in local access table..

Do Until Rset.EOF

--inserting records in local access table


So my loop take so long to complete...

Please see - thread709-996910 see only posting for 1,feb 2005 which i did posted..u will get better idea...

In short..I am just trying to improve the perfomance..and try to run my query faster

Advise me what's the solution for to achieve quick output from select statement and insert into local access table...

Thanks

AD

 

[fredericofonseca]

amardesai13

As for this particular code
INSERT INTO tblWLActCat_WLDay( CustLifeNo, WLActCatId, WLDayId, WeekNo ) Select A.Cust_Life_Num, A.WLActCatId, A.WLDayId, A.WeekNo from WLActCat_WLDay A Inner Join (select distinct (cust_life_num) from CUSTTB Where (Region_Num = 50) ) B On A.Cust_Life_Num = B.Cust_Life_num And A.WeekNo In (1,2,3,4)

the error is that the "and a.weekno in ...) is not part of the join, therefore it can not be a "and".

e.g.

Select A.Cust_Life_Num, A.WLActCatId, A.WLDayId, A.WeekNo from WLActCat_WLDay
is one part of the sql

inner join "join expression e.g. tbl on field1 = field2"
"end of join"
and now "where" clauses that are part of the first sql bit

where a.weekno in ...


An regarding your speed problem I asked you for more information on thread709-998501 and you still haven't replied.

The only way you are going to solve the speed problem is either by using DTS or by using both BCP (Bulk copy) on the SQL server to output to a file, and then importing it into access.

Doing it with VB or ADO or linked tables is not going to solve it.

So if you are interested please go to the other thread and supply the missing info so that I or others can have a look at it with the less hassle possible.






Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com