Groups > microsoft.public.excel.programming > #109407 > unrolled thread

VBA Excel SQL Query

Started by	Bruno Campanini <brunocam@libero.it>
First post	2016-10-11 14:40 +0200
Last post	2016-10-23 19:48 +0200
Articles	3 — 2 participants

Back to article view | Back to microsoft.public.excel.programming

  VBA Excel SQL Query Bruno Campanini <brunocam@libero.it> - 2016-10-11 14:40 +0200
    Re: VBA Excel SQL Query isabelle <i@v.invalid> - 2016-10-23 10:54 -0400
      Re: VBA Excel SQL Query Bruno Campanini <brunocam@libero.it> - 2016-10-23 19:48 +0200

#109407 — VBA Excel SQL Query

From	Bruno Campanini <brunocam@libero.it>
Date	2016-10-11 14:40 +0200
Subject	VBA Excel SQL Query
Message-ID	<ntimjb$9q$1@gioia.aioe.org>

SQL = "SELECT " & xlTable2 & ".* "
SQL = SQL & "FROM " & xlTable2 & " "
SQL = SQL & "WHERE (((" & xlTable2 & ".F6) "
SQL = SQL & "In (SELECT " & xlTable1 & ".F1 "
SQL = SQL & "FROM " & xlTable1 & ";)));"

Set RS = xlDB.OpenRecordset(SQL, dbOpenDynaset)
Destination.CopyFromRecordset RS
-----------------------------------

This query works fine on Windows 10/Excel 2016 as long
as the number of records in the tables is <=65 000.

In my Windows 10 Enterprise the MaxLocksPerFile
is set to 9 500 (dec); I tried to set it at
1 000 000 (dec) but Excel continues showing error
"... could not find the object..." and stops at:
Set RS = xlDB.OpenRecordset(SQL, dbOpenDynaset)

Any suggestions?

Bruno

[toc] | [next] | [standalone]

#109450

From	isabelle <i@v.invalid>
Date	2016-10-23 10:54 -0400
Message-ID	<nuiiv9$ie3$1@gioia.aioe.org>
In reply to	#109407

maybe here:
http://www.myengineeringworld.net/2013/01/export-large-access-tablequery-to-excel.html
isabelle

Le 2016-10-11 à 08:40, Bruno Campanini a écrit :
> SQL = "SELECT " & xlTable2 & ".* "
> SQL = SQL & "FROM " & xlTable2 & " "
> SQL = SQL & "WHERE (((" & xlTable2 & ".F6) "
> SQL = SQL & "In (SELECT " & xlTable1 & ".F1 "
> SQL = SQL & "FROM " & xlTable1 & ";)));"
>
> Set RS = xlDB.OpenRecordset(SQL, dbOpenDynaset)
> Destination.CopyFromRecordset RS
> -----------------------------------
>
> This query works fine on Windows 10/Excel 2016 as long
> as the number of records in the tables is <=65 000.
>
> In my Windows 10 Enterprise the MaxLocksPerFile
> is set to 9 500 (dec); I tried to set it at
> 1 000 000 (dec) but Excel continues showing error
> "... could not find the object..." and stops at:
> Set RS = xlDB.OpenRecordset(SQL, dbOpenDynaset)
>
> Any suggestions?
>
> Bruno

[toc] | [prev] | [next] | [standalone]

#109451

From	Bruno Campanini <brunocam@libero.it>
Date	2016-10-23 19:48 +0200
Message-ID	<nuit4e$13hp$1@gioia.aioe.org>
In reply to	#109450

isabelle submitted this idea :
> maybe here:
> http://www.myengineeringworld.net/2013/01/export-large-access-tablequery-to-excel.html
> isabelle

Ok Isabelle, having two sessions opened: Excel and Access you
can do every thing without problems.

I was just wondereing how to use an SQL query within Excel
to make operations related to more then 2^16 records.
It seems it's a mission impossible.

Thank you
Bruno

[toc] | [prev] | [standalone]

csiph-web

VBA Excel SQL Query

Contents

#109407 — VBA Excel SQL Query

#109450

#109451