Insert into table values limit

[desmond]

I'm pretty new to VB, so don't know what's causing my current issue.

I have successfully created a process to dynamically add field-names and their values to a table by looping through a text file:

For i = 1 To sMax
If i = 1 Then
sFldRpt = "[" & sName(i) & "]"
sValRpt = "'" & sDataRpt(i) & "'"
Else
sFldRpt = sFldRpt & ", [" & sName(i) & "]"
sValRpt = sValRpt & ", '" & sDataRpt(i) & "'"
End If
Next sFieldCount

If there are only 3 columns to update, sName will be:

[Field1], [Field2], [Field3]

and sVal will be:

'123', '456', '789'

Then I use the following command to update the table:

DoCmd.RunSQL "INSERT INTO Results(" & sFldRpt & ") VALUES(" & sValRpt & ");"

This process works so long as I don't have more than 16 entries.

For some reason, with 15 entries, it works fine, but with 16 - only the column names are entered, with no value update.

Can anyone help with this????!!!

 

[jdraw]

I'm pretty new to VB, so don't know what's causing my current issue.

I have successfully created a process to dynamically add field-names and their values to a table by looping through a text file:

For i = 1 To sMax
If i = 1 Then
sFldRpt = "[" & sName(i) & "]"
sValRpt = "'" & sDataRpt(i) & "'"
Else
sFldRpt = sFldRpt & ", [" & sName(i) & "]"
sValRpt = sValRpt & ", '" & sDataRpt(i) & "'"
End If
Next sFieldCount

If there are only 3 columns to update, sName will be:

[Field1], [Field2], [Field3]

and sVal will be:

'123', '456', '789'

Then I use the following command to update the table:

DoCmd.RunSQL "INSERT INTO Results(" & sFldRpt & ") VALUES(" & sValRpt & ");"

This process works so long as I don't have more than 16 entries.

For some reason, with 15 entries, it works fine, but with 16 - only the column names are entered, with no value update.

Can anyone help with this????!!!

Can you show us more?
What is sMax? How are sName(i) and sDataRpt(i) defined?

 

[desmond]

Sure - thanks for the quick response.

I've created sName / sPos / sLen as arrays:

Dim sName(9999)
Dim sPos(9999)
Dim sLen(9999)

Here's the full code:

strSQL = "SELECT * FROM DES_Display WHERE [Display] = True;"

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL)

Do While Not rst.EOF
i= i + 1
sName(i)= rst![Name]
sPos(i)= rst![Position]
sLen(i)= rst![Length]
rst.MoveNext

Loop

sMax = i
rst.Close
dbs.Close

'CREATE THE TABLE WITH THE FIRST RECORD
strSQL = "CREATE TABLE Results ([" & sName(1) & "] TEXT(" & sLen(1) & "));"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL

'ADD THE COLUMNS SELECTED TO THE TABLE
For i = 2 To sMax
DoCmd.RunSQL "ALTER TABLE Results ADD COLUMN [" & sName(i)& "] TEXT(" & sLen(i)& ");"
Next i

Open txtFileIn For Input As #1

Do While Not EOF(1)
Line Input #1, sRecord
For i = 1 To sMaxField
sDataRpt(i) = Trim(Mid(sRecord, sPos(i), sLen(i)))
If i = 1 Then
sFldRpt = "[" & sName(i) & "]"
sValRpt = "'" & sDataRpt(i) & "'"
Else
sFldRpt = sFldRpt & ", [" & sName(i) & "]"
sValRpt = sValRpt & ", '" & sDataRpt(i) & "'"
End If
Next i

DoCmd.RunSQL "INSERT INTO Results(" & sFldRpt & ") VALUES(" & sValRpt & ");"

Loop

Close #1




What's strange is that it works perfectly if there is only 5 columns to add, even up to 15. As soon as you have 16 columns to add, it doesn't work...

 

[boblarson]

So, I just gotta ask - why are you doing this? What is the purpose of creating fields in tables via code like this?

 

[jdraw]

Sure - thanks for the quick response.

I've created sName / sPos / sLen as arrays:

Dim sName(9999)
Dim sPos(9999)
Dim sLen(9999)

Here's the full code:

strSQL = "SELECT * FROM DES_Display WHERE [Display] = True;"

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL)

Do While Not rst.EOF
i= i + 1
sName(i)= rst![Name]
sPos(i)= rst![Position]
sLen(i)= rst![Length]
rst.MoveNext

Loop

sMax = i
rst.Close
dbs.Close

'CREATE THE TABLE WITH THE FIRST RECORD
strSQL = "CREATE TABLE Results ([" & sName(1) & "] TEXT(" & sLen(1) & "));"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL

'ADD THE COLUMNS SELECTED TO THE TABLE
For i = 2 To sMax
DoCmd.RunSQL "ALTER TABLE Results ADD COLUMN [" & sName(i)& "] TEXT(" & sLen(i)& ");"
Next i

Open txtFileIn For Input As #1

Do While Not EOF(1)
Line Input #1, sRecord
For i = 1 To sMaxField
sDataRpt(i) = Trim(Mid(sRecord, sPos(i), sLen(i)))
If i = 1 Then
sFldRpt = "[" & sName(i) & "]"
sValRpt = "'" & sDataRpt(i) & "'"
Else
sFldRpt = sFldRpt & ", [" & sName(i) & "]"
sValRpt = sValRpt & ", '" & sDataRpt(i) & "'"
End If
Next i

DoCmd.RunSQL "INSERT INTO Results(" & sFldRpt & ") VALUES(" & sValRpt & ");"

Loop

Close #1




What's strange is that it works perfectly if there is only 5 columns to add, even up to 15. As soon as you have 16 columns to add, it doesn't work...

I have to agree with Bob, Why?

 

[desmond]

It reads a file of raw data, and populates a table of user selected columns. The raw data is mapped to an offset db which I've created so it knows which fields are in which position. It's supposed to be dynamic so that the fields / values can change without the need to recode / perform a macro.

Does anyone know what the problem is?