Limits in SQL query
 

Good day Group,

As a newbie to sql programming in vba I have run into problems.
By help from checking old posts in this group I have managed to
write codes that with a ADO connection and sql statement extracts
data from a closed workbook. I have data in a list in the source
workbook that is in abt 4000 rows and 60 columns, When runing
the code I will only have 1024 rows extracted. Some columns has
cells with up to 500 characters.

How to overide this row limit?

Brgds

CG Rosen



' this code gives only 1024 rows. Why?

szSQL = "SELECT * FROM [db_test1$]"

Set rsData = New ADODB.Recordset


rsData.Open szSQL, szConnect, adOpenStatic, adLockReadOnly

Limits in SQL query
 

Haven't seen anything like it mysefl.
you can try switching drivers - e.g., if you use ODBC you can tray OLE
DB instead or SQL Natvie Client driver or any other that there are on
your machine - and then see if it makes any difference.
It might be some setting for the driver too - so, maybe you need to
change that setting as oppose to driver.

On Apr 30, 4:36*am, "CG Rosen" wrote:
Good day Group,

As a newbie to sql programming in vba I have run into problems.
By help from checking old posts in this group I have managed to
write codes that with a *ADO connection and sql statement extracts
data from a closed workbook. I have data in a list in the source
workbook that is in abt 4000 rows and 60 columns, When runing
the code *I will only have 1024 rows extracted. Some columns has
cells with up to 500 characters.

How to overide this row limit?

Brgds

CG Rosen

' this code gives only 1024 rows. Why?

szSQL = "SELECT * FROM [db_test1$]"

Set rsData = New ADODB.Recordset

rsData.Open szSQL, szConnect, adOpenStatic, adLockReadOnly

Limits in SQL query
 

try this:
rsData.Open szSQL, szConnect,adOpenKeyset



"CG Rosen" wrote:

Good day Group,

As a newbie to sql programming in vba I have run into problems.
By help from checking old posts in this group I have managed to
write codes that with a ADO connection and sql statement extracts
data from a closed workbook. I have data in a list in the source
workbook that is in abt 4000 rows and 60 columns, When runing
the code I will only have 1024 rows extracted. Some columns has
cells with up to 500 characters.

How to overide this row limit?

Brgds

CG Rosen



' this code gives only 1024 rows. Why?

szSQL = "SELECT * FROM [db_test1$]"

Set rsData = New ADODB.Recordset


rsData.Open szSQL, szConnect, adOpenStatic, adLockReadOnly

Limits in SQL query
 

thanks - worked perfect / CG Rosen






"Ralph" skrev i meddelandet
...
try this:
rsData.Open szSQL, szConnect,adOpenKeyset



"CG Rosen" wrote:

Good day Group,

As a newbie to sql programming in vba I have run into problems.
By help from checking old posts in this group I have managed to
write codes that with a ADO connection and sql statement extracts
data from a closed workbook. I have data in a list in the source
workbook that is in abt 4000 rows and 60 columns, When runing
the code I will only have 1024 rows extracted. Some columns has
cells with up to 500 characters.

How to overide this row limit?

Brgds

CG Rosen



' this code gives only 1024 rows. Why?

szSQL = "SELECT * FROM [db_test1$]"

Set rsData = New ADODB.Recordset


rsData.Open szSQL, szConnect, adOpenStatic, adLockReadOnly