strange error message following INSERT INTO statement

[Loane Sharp]

Hi there

I'm using ADO from within an Excel VBA procedure to import a fairly large
text file (1GB+) into an Access database.

Everything's running smoothly (thanks to the help from this group!), except
when I get to the final INSERT INTO statement. My code (a bit truncated) is
as follows ...

Dim cnNew As ADODB.Connection
Dim catNew As ADOX.Catalog
Dim tblNew As ADOX.Table
Dim colNew As ADOX.Column

strConnect = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DestinationFile.mdb;"
Set cnNew = New ADODB.Connection
cnNew.Open strConnect

Set catNew = New ADOX.Catalog
Set catNew.ActiveConnection = cnNew

Set tblNew = New ADOX.Table
tblNew.Name = tblName

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\PathToTextFile\TextFile.txt", 1)
strColHeaders = objFile.ReadLine
objFile.Close

arrColHeaders = Split(strColHeaders, "|", -1, 1)

For Each strColHeader in arrColHeaders
Set colNew = New ADOX.Column
colNew.Name = strColHeader
colNew.Type = adVarWChar
colNew.DefinedSize = 24
colNew.Attributes = adColNullable
tblNew.Columns.Append colNew.Name, colNew.Type, colNew.DefinedSize
Next

catNew.Tables.Append tblNew

cnNew.Execute "INSERT INTO " & tblName & " SELECT * FROM
[Text;Database=C:\PathToTextFile\;HDR=YES].[" & txtFileName & ".txt]"

At this point I get a run-time error '-2147217900 (80040e14)' ~ "The INSERT
INTO statement contains the following unknown field name: 'Inv# To'. Make
sure you have typed the name correctly, and try the operation again."

I don't know where the "#" character arises... For instance, when I manually
import the text file into an Access database, the field/column name (in
design view) is given as "Inv To" (words separated by a space), and the same
result is achieved if I import the text file (at least the first 2^16 rows
of the text file) into Excel.

However, if I view the text file in Windows Commander (the file is too big
to view in Notepad etc.), the field name is given as "Inv. To" (both a space
and a period), while the error message bizarrely suggests that the field
name contains a "#".

I'm about ready to fly off the handle!

Please help
Loane

 

[Jamie Collins]

I get a run-time error '-2147217900 (80040e14)' ~ "The INSERT
INTO statement contains the following unknown field name: 'Inv# To'. Make
sure you have typed the name correctly, and try the operation again."

I don't know where the "#" character arises... For instance, when I manually
import the text file into an Access database, the field/column name (in
design view) is given as "Inv To" (words separated by a space), and the same
result is achieved if I import the text file (at least the first 2^16 rows
of the text file) into Excel.

However, if I view the text file in Windows Commander (the file is too big
to view in Notepad etc.), the field name is given as "Inv. To" (both a space
and a period), while the error message bizarrely suggests that the field
name contains a "#".

The # is a placeholder for a period, which is invalid in a
column/table name or alias. For example, if my Excel sheet has a
column header My.Col1, then

SELECT [My.Col1] FROM [Sheet1$];

will error. However,

SELECT [My#Col1] FROM [Sheet1$];

will work and show the column name as Score#ID.

Conclusions:
(1) it is a bad idea to include special characters, including spaces,
periods, hyphens, but excepting underscore, in data element names.
(2) it is a bad idea to use the * notation in production code,
excepting COUNT(*) (because it has special meaning) and perhaps SELECT
* in the subquery of an EXISTS clause.

Jamie.

--

 

[Loane Sharp]

Hi Jamie

Thanks for your patience. The text files I've received are in the most
appalling shape ... some |-delimited, some csv, inconsistent field headers
between files, you name it ...

On running the INSERT INTO statement, I get the following error message ~

"Run-time error '-2147467259 (80004005)': The field
'F01Achivements.CurrntEst' cannot contain a Null value because the Required
property for this field is set to true. Enter a value in this field."

I tried to solve this by defining the attributes and other properties of
each column created in the new table, as follows:

For n = 1 to NoCols
Set colNew = New ADOX.Column
colNew.Name = Cells(n, 1).Value
colNew.Type = adInteger
colNew.DefinedSize = 8
colNew.Attributes = adColNullable
tblNew.Columns.Append colNew.Name
Next n

catNew.Tables.Append tblNew
catNew.Tables.Refresh

cnNew.Execute "INSERT INTO " & tblName & " SELECT [AdmRef] FROM
[Text;Database=C:\PathToTextFiles\;HDR=YES].[" & tblName & ".txt]"

I get the table to be appended and refreshed alright, but on attempting to
execute the INSERT INTO ... statement I get the above error message. There
are indeed Nulls in the underlying data [if I'm correct in assuming that
"Null" means a completely blank (data never entered) record], but that
shouldn't be an uncommon thing?

Do you have any ideas?
Best regards
Loane

I get a run-time error '-2147217900 (80040e14)' ~ "The INSERT
INTO statement contains the following unknown field name: 'Inv# To'. Make
sure you have typed the name correctly, and try the operation again."

I don't know where the "#" character arises... For instance, when I manually
import the text file into an Access database, the field/column name (in
design view) is given as "Inv To" (words separated by a space), and the same
result is achieved if I import the text file (at least the first 2^16 rows
of the text file) into Excel.

However, if I view the text file in Windows Commander (the file is too big
to view in Notepad etc.), the field name is given as "Inv. To" (both a space
and a period), while the error message bizarrely suggests that the field
name contains a "#".

The # is a placeholder for a period, which is invalid in a
column/table name or alias. For example, if my Excel sheet has a
column header My.Col1, then

SELECT [My.Col1] FROM [Sheet1$];

will error. However,

SELECT [My#Col1] FROM [Sheet1$];

will work and show the column name as Score#ID.

Conclusions:
(1) it is a bad idea to include special characters, including spaces,
periods, hyphens, but excepting underscore, in data element names.
(2) it is a bad idea to use the * notation in production code,
excepting COUNT(*) (because it has special meaning) and perhaps SELECT
* in the subquery of an EXISTS clause.

Jamie.

--

 

[Jamie Collins]

On running the INSERT INTO statement, I get the following error message ~

"Run-time error '-2147467259 (80004005)': The field
'F01Achivements.CurrntEst' cannot contain a Null value because the Required
property for this field is set to true. Enter a value in this field."

I tried to solve this by defining the attributes and other properties of
each column created in the new table, as follows:

For n = 1 to NoCols
Set colNew = New ADOX.Column
colNew.Name = Cells(n, 1).Value
colNew.Type = adInteger
colNew.DefinedSize = 8
colNew.Attributes = adColNullable
tblNew.Columns.Append colNew.Name
Next n

Sorry, I'm not too familiar with creating schema objects using ADOX. I
prefer to use DDL (because it is portable and reusable) e.g.

For a new table:

CREATE TABLE MyTable (MyNewCol INTEGER NULL);

For an existing table:

ALTER TABLE MyTable ADD MyNewCol INTEGER NULL;

In the DDL, the NULL keyword means nulls are allowed.

There are indeed Nulls in the underlying
data [if I'm correct in assuming that
"Null" means a completely blank (data
never entered) record], but that shouldn't
be an uncommon thing?

In the database sense, 'null' means 'value unknown'. For a numeric
column, it is often the case that null and zero have the same meaning
(according to the business rules), so consider defining your column
as:

ALTER TABLE MyTable ADD MyNewCol INTEGER NOT NULL DEFAULT 0;

Nulls are generally to be avoided if the business rules allow; easier
for an applications to test for a known DEFAULT value than a missing
value.

ALTER TABLE MyTable ADD last_name VARCHAR(35) NOT NULL DEFAULT
'{{N/A}}';

In your data, is a zero length string the same as a null? a zero?

cnNew.Execute "INSERT INTO " & tblName & " SELECT [AdmRef] FROM
[Text;Database=C:\PathToTextFiles\;HDR=YES].[" & tblName & ".txt]"

I get the table to be appended and refreshed alright, but on attempting to
execute the INSERT INTO ... statement I get the above error message.

Something to note is that you are not using the full INSERT INTO
syntax, should be:

INSERT INTO MyTargetTable (MyCol) SELECT MyCol FROM MySourceTable;

Try specifying the column(s) in the target table; there's a chance
this may identify the problem area.

If you are creating your table based on the text file, the
SELECT..INTO syntax may be better because it creates a table for you.
You could then subsequently change the schema definitions.

Jamie.

--

 

[Loane Sharp]

Hi Jamie

Thanks very much ... your suggestions worked perfectly.

In one of the INSERT INTO operations, I have a single text file representing
132 fields and 107,288 records. I tried, as you suggested, to list each
field separately in the SELECT part of the statement (rather than SELECT *),
which turned out to be necessary (as well as elegant) because some fields
contain unusual characters necessitating the [ ] format.

However there seems to be a limit on the number of fields (and records for
that matter) that can be selected and inserted at once. Where does the
limitation arise? SQL? I've split the INSERT INTO ... SELECT operation into
units of 50 + 50 +32 fields, and this works well except that, after the
first operation, the top left corners of subsequent blocks of data (if you
can visualise them as rectangular blocks) sit at the bottom right corners of
the previous blocks. Any ideas?

what I got ...
__
|__|__
|__|__
|__|

what I need ...
__ __ __
|__|__|__|

or rather ...

__ __ __
|__ __ __|

Also, is there any way to split the records as well, for instance to insert
25,000 records at a time?

Best regards
Thanks for all your help
Loane

On running the INSERT INTO statement, I get the following error message ~

"Run-time error '-2147467259 (80004005)': The field
'F01Achivements.CurrntEst' cannot contain a Null value because the Required
property for this field is set to true. Enter a value in this field."

I tried to solve this by defining the attributes and other properties of
each column created in the new table, as follows:

For n = 1 to NoCols
Set colNew = New ADOX.Column
colNew.Name = Cells(n, 1).Value
colNew.Type = adInteger
colNew.DefinedSize = 8
colNew.Attributes = adColNullable
tblNew.Columns.Append colNew.Name
Next n

Sorry, I'm not too familiar with creating schema objects using ADOX. I
prefer to use DDL (because it is portable and reusable) e.g.

For a new table:

CREATE TABLE MyTable (MyNewCol INTEGER NULL);

For an existing table:

ALTER TABLE MyTable ADD MyNewCol INTEGER NULL;

In the DDL, the NULL keyword means nulls are allowed.

There are indeed Nulls in the underlying
data [if I'm correct in assuming that
"Null" means a completely blank (data
never entered) record], but that shouldn't
be an uncommon thing?

In the database sense, 'null' means 'value unknown'. For a numeric
column, it is often the case that null and zero have the same meaning
(according to the business rules), so consider defining your column
as:

ALTER TABLE MyTable ADD MyNewCol INTEGER NOT NULL DEFAULT 0;

Nulls are generally to be avoided if the business rules allow; easier
for an applications to test for a known DEFAULT value than a missing
value.

ALTER TABLE MyTable ADD last_name VARCHAR(35) NOT NULL DEFAULT
'{{N/A}}';

In your data, is a zero length string the same as a null? a zero?

cnNew.Execute "INSERT INTO " & tblName & " SELECT [AdmRef] FROM
[Text;Database=C:\PathToTextFiles\;HDR=YES].[" & tblName & ".txt]"

I get the table to be appended and refreshed alright, but on attempting to
execute the INSERT INTO ... statement I get the above error message.

Something to note is that you are not using the full INSERT INTO
syntax, should be:

INSERT INTO MyTargetTable (MyCol) SELECT MyCol FROM MySourceTable;

Try specifying the column(s) in the target table; there's a chance
this may identify the problem area.

If you are creating your table based on the text file, the
SELECT..INTO syntax may be better because it creates a table for you.
You could then subsequently change the schema definitions.

Jamie.

--

 

[Jamie Collins]

there seems to be a limit on the number of fields (and records for
that matter) that can be selected and inserted at once. Where does the
limitation arise? SQL?

You may be hitting a limit somewhere but I don't think there is
sub-133 column limit for Jet.

To test the limit isn't the SELECT clause ('...' meaning 'continue the
sequence'):

SELECT
RefID AS Col1,
RefID AS Col2,
RefID AS Col3,
...
RefID AS Col132
INTO
AnotherTable
FROM
MyTable
;

To test the limit isn't the INSERT INTO clause:

INSERT INTO
AnotherTable
(
Col1,
Col2,
Col3,
...
Col132
)
VALUES
(
1,
2,
3,
...
132
)
;

To test the limit isn't the INSERT INTO..SELECT syntax:

INSERT INTO
AnotherTable
(
Col1,
Col2,
Col3,
...
Col132
)
SELECT
RefID AS Col1,
RefID AS Col2,
RefID AS Col3,
...
RefID AS Col132
FROM
MyTable
;

All executed successfully.

It would be difficult similarly to test the row limit because for a
Jet .mdb it is determined by the overall file size limit of 2GB <g>.

Jamie.

--