Limits in SQL query

[CG Rosen]

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

 

[AB]

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.

 

[Ralph]

try this:
rsData.Open szSQL, szConnect,adOpenKeyset

 

[CG Rosen]

thanks - worked perfect / CG Rosen

try this:
rsData.Open szSQL, szConnect,adOpenKeyset

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