Does RunSQL have a limit?

[desmond]

Can anyone tell me if the RunSQL command has a limit?

I have created a process to add a number of columns to a table, with each row of each table having an entry of '1234567980'.

The process works when I set the rows to 183. But when I set this to 184, the process doesn't load the data into the columns. I get no error at all, just no entries in my rows.

I have no idea why this works for <= 183, but not for >183. Any experts out there who can tell me why?

Here's the code:

Private Sub Test_db_entries()
Dim sTest1 As String
Dim sTest2 As String
Dim i As Integer
Dim sText1(9999)
Dim sText2(9999)
'Imax = 184 - this doesn't work
iMax = 183 '- this does work

'CREATE TESTTAB TABLE
strSQL = "CREATE TABLE TestTab ([Fld1] TEXT(10));"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL

'ADD MORE COLUMNS
For i = 2 To iMax
sText1(i) = "Fld" & i
DoCmd.RunSQL "ALTER TABLE TestTab ADD COLUMN [" & sText1(i) & "] TEXT(10);"
Next i

'POPULATE DATA
For i = 1 To iMax
If i = 1 Then
sText1(1) = "Fld1"
sText2(1) = "1234567890"
sTest1 = "[" & sText1(1) & "]"
sTest2 = "'" & sText2(1) & "'"
Else
sText1(i) = "Fld" & i
sText2(i) = "1234567890"
sTest1 = sTest1 & ", [" & sText1(i) & "]"
sTest2 = sTest2 & ", '" & sText2(i) & "'"
End If
Next i

DoCmd.RunSQL "INSERT INTO TestTab(" & sTest1 & ") VALUES(" & sTest2 & ");"

End Sub

 

[Galaxiom]

You seem to have confused the rows and columns in your post.
(BTW. Database tables have Fields and Records rather than Rows and Columns.)

Access does have a limit of 255 fields in a table or query. An update query requires two fields for each updated field. I don't know the significance of 183.

However the desire to have so many fields, especially combined with using code to add fields suggests inappropriate table structure.

 

[desmond]

You seem to have confused the rows and columns in your post.
(BTW. Database tables have Fields and Records rather than Rows and Columns.)

Access does have a limit of 255 fields in a table or query. An update query requires two fields for each updated field. I don't know the significance of 183.

However the desire to have so many fields, especially combined with using code to add fields suggests inappropriate table structure.

Thanks Galaxiom - you're right I got my rows and cols mixed up - I'll refer to them as fields and records from now on. I'm very junior with vba .

I'm guessing that the RunSQL text must have a limit because if I increase the size of each field (from 10 to 20), the max fields becomes 97 (if I use 98 the same problem occurs - all fields successfully add with no records).

Do you have any idea why this occurs?

 

[Atomic Shrimp]

I wonder if there's some kind of execution stack that's getting filled up here. Try putting DoEvents inside the loop, immediately after your DoCmd line(s) - i.e.:

For i = 2 To iMax
sText1(i) = "Fld" & i
DoCmd.RunSQL "ALTER TABLE TestTab ADD COLUMN [" & sText1(i) & "] TEXT(10);"
[COLOR="Indigo"]DoEvents[/COLOR]
Next i

 

[gemma-the-husky]

put some error handling in, and see whats causing the error

 

[desmond]

put some error handling in, and see whats causing the error

There's no error - it just doesn't update the row...

 

[desmond]

I wonder if there's some kind of execution stack that's getting filled up here. Try putting DoEvents inside the loop, immediately after your DoCmd line(s) - i.e.:

For i = 2 To iMax
sText1(i) = "Fld" & i
DoCmd.RunSQL "ALTER TABLE TestTab ADD COLUMN [" & sText1(i) & "] TEXT(10);"
[COLOR=indigo]DoEvents[/COLOR]
Next i

Tried this - didn't work I'm afraid. Still gets stuck on the 184th field update.