INSERT INTO inserts a wrong record in Access 97

INSERT INTO inserts a wrong record in Access 97

Post by susanna » Tue, 30 Sep 2003 18:26:08

Hi Newsgroup

I have a problem with an INSERT INTO Statement:

In a recordset the actual record should be inserted into another
table. I use the insert into statement with a WHERE (refers to a
unique field) in the statement. The insert works (no error messages),
but it inserts a completly wrong record - only the referred field is
correct. When I delete the wrong one and run the code again, the
insert works properly.

The insert into should work from table to table, no forms, queries or
whatever are involved - just pure VBA-Code...

Any help would be appreciated!

Thanks in advance


Dim DB As Database, myTable1 As Recordset, strSQL As String
Dim strConnrn As String, loAnzahl As Long
Set DB = CurrentDb
Set myTable1 = DB.OpenRecordset("new_consignments", DB_OPEN_DYNASET)
loAnzahl = myTable1.RecordCount
If loAnzahl > 0 Then
Do Until myTable1.EOF
strConnrn = myTable1!connrn
If Left(strConnrn, 1) = 0 Then
myTable1!connrn = Right(strConnrn, Len(strConnrn) - 1)
strConnrn = myTable1!connrn
End If
If Not IsNull(myTable1!rname) And myTable1!rname <> "" And _
Not IsNull(myTable1!radr2) And myTable1!radr2 <> "" And _
Not IsNull(myTable1!rpostal) And myTable1!rpostal <> "" And
Not IsNull(myTable1!rcity) And myTable1!rcity <> "" And _
Not IsNull(myTable1!rcountry) And myTable1!rcountry <> ""
And _
Not IsNull(myTable1!inv) And myTable1!inv <> 0 And _
Not IsNull(myTable1!curr) And myTable1!curr <> "" And _
Not IsNull(myTable1!desc1) And myTable1!desc1 <> "" And _
Not IsNull(myTable1!no1) And myTable1!no1 <> 0 And _
Not IsNull(myTable1!wg1) And myTable1!wg1 <> 0 And _
Not IsNull(myTable1!serv) And myTable1!serv <> "" Then
If myTable1!rcountry <> "SWITZERLAND" And _
myTable1!dcountry = "SWITZERLAND" Then
strSQL = "INSERT INTO consignments SELECT
new_Consignments.* FROM new_consignments WHERE new_Consignments.connrn
= " & strConnrn
DB.Execute strSQL
Call Print_from_Automation(myTable1!totalno,
myTable1!serv, myTable1!connrn)
End If
End If
End If

1. inserting UK dates into MS Access from insert record behaviour

2. Do Not Keep NULLS using SSIS Bulk Insert - Insert Empty String Ins

I have two SSIS packages that import from the same flat file into the same
SQL 2005 table. Both packages use the same Connection Managers. The SQL
table allows NULL values for all fields. The flat file has "empty values"
for certain columns.

The first package uses the Data Flow Task with the "Keep nulls" property of
the OLE DB Destination Editor unchecked. When this task is executed no null
values are inserted into the SQL table for the "empty values" from the flat
file. Empty string values are inserted instead of NULL.

The second package uses the Bulk Insert Task with the "KeepNulls" property
for the task set to "False". When the task is executed NULL values are
inserted into the SQL table for the "empty values" from the flat file.

I want to have the exact same behavior on my data in the Bulk Insert Task as
I do with the Data Flow Task. Using the Bulk Insert Task, what must I do to
have the Empty String values inserted into the SQL table where there is an
"empty value" in the flat file? Why & how does this occur automatically in
the Data Flow Task?

3. Access 97 placing info in wrong field when record is edited or

4. Access 97 placing info in wrong field when record is edited or upd

5. BUG-Access 97 db insert via Jet 4.0 using stored procedure fails - any help?

6. access 97: INSERT and Append queries

7. access 97: select into/insert into sql question

8. access 97: insert into problems

9. access 97, insert from sql server slow

10. Weird INSERT INTO error with Access 97

11. INSERT trigger doing the wrong thing on multiple insert

12. Insert Record then give me new record ID (Access)

13. Getting records from Access 97 to Excel 97

14. Inserting a Bound Picture Which Changes from Record to Record in Access XP

15. Duplicate records are being inserted with one insert command.