Freaky (:S) insert "errors"

[Twanne]

Hi,

I've got a table called Aerosol and a table called LinkTabel.

Now I do an update to a table from an excell sheet, this table is called ExportAerosol. So far so good, the import works perfect.

Now when I want to transfer the data from the ExportAerosol table to the Aerosol table I have to make a unique key. I do this by adding a string to another key. Now there is no problem when I update the Aerosol table. It adds the values I wanted (presumed) normally. But It (for some reason) adds only a part to the link table.
E.g. I add 41 records to my Aerosol table trough a loop, in the same loop I add records to LinkTabel to. But this gives only 33 records.

This is when the link between the two tables (unikey) isn't layed (one on one).

When I lay this link it adds 33 records to LinkTabel but none to Aerosol.

I want to add an equal amount of records to both tables.

Here is my code:

Function BuildImport()
    Dim key As String
    Dim datum As String
    Dim gestart As Date
    Dim gestop As Date
 
 On Error GoTo BuildImport_Err
    Dim db As Database
    Dim rst As Recordset
    Dim i As Integer
 
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT * FROM ExportAerosol WHERE omit = 'i';")
    Do Until rst.EOF
        i = i + 1
 
        gestart = #1/1/9999#
        gestopt = #1/1/9999#
        If Not (rst.Fields(13).Value = "") Then
            gestart = rst.Fields(13).Value
        End If
        If Not (rst.Fields(14).Value = "") Then
            gestopt = rst.Fields(14).Value
        End If
 
        datum = Mid(rst.Fields(5).Value, 1, 10)
        datum = DateFunctions.dateFormat(datum)
        key = "aer" & rst.Fields(0).Value
        db.Execute "INSERT INTO LinkTabel (unikey, eadnr, datum) VALUES ('" & key & "'," & rst.Fields(3).Value & ",#" & datum & "#);"
        db.Execute "INSERT INTO Aerosol (dkey, eadnr, eersteZendtijd, VerslagNr, cnr, TypeToestel, Hulpstuk, Medicatie, Frequentie, Onderhoud, Start, Stop, unikey ) VALUES(" & rst.Fields(0).Value & "," & rst.Fields(3).Value & ",'" & rst.Fields(5).Value & "'," & rst.Fields(7).Value & "," & rst.Fields(6).Value & ",'" & rst.Fields(8).Value & "','" & rst.Fields(9).Value & "','" & rst.Fields(10).Value & "','" & rst.Fields(11).Value & "','" & rst.Fields(12).Value & "',#" & gestart & "#,#" & gestopt & "#,'" & key & "');"
 
        rst.MoveNext
    Loop
    Debug.Print i
 
BuildImport_Exit:
    Set rst = Nothing
    Set db = Nothing
    Exit Function
 
BuildImport_Err:
    MsgBox Error$
    Resume BuildImport_Exit
End Function

Please help me :D
(got to finish this today :s)

Greetz
Twanne

[Twanne]

Could somebody please help me??? I really need this done, but I have no idea where things go wrong.

[ADezii]

Hi,

I've got a table called Aerosol and a table called LinkTabel.

Now I do an update to a table from an excell sheet, this table is called ExportAerosol. So far so good, the import works perfect.

Now when I want to transfer the data from the ExportAerosol table to the Aerosol table I have to make a unique key. I do this by adding a string to another key. Now there is no problem when I update the Aerosol table. It adds the values I wanted (presumed) normally. But It (for some reason) adds only a part to the link table.
E.g. I add 41 records to my Aerosol table trough a loop, in the same loop I add records to LinkTabel to. But this gives only 33 records.

This is when the link between the two tables (unikey) isn't layed (one on one).

When I lay this link it adds 33 records to LinkTabel but none to Aerosol.

I want to add an equal amount of records to both tables.

Here is my code:

Function BuildImport()
    Dim key As String
    Dim datum As String
    Dim gestart As Date
    Dim gestop As Date
 
 On Error GoTo BuildImport_Err
    Dim db As Database
    Dim rst As Recordset
    Dim i As Integer
 
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT * FROM ExportAerosol WHERE omit = 'i';")
    Do Until rst.EOF
        i = i + 1
 
        gestart = #1/1/9999#
        gestopt = #1/1/9999#
        If Not (rst.Fields(13).Value = "") Then
            gestart = rst.Fields(13).Value
        End If
        If Not (rst.Fields(14).Value = "") Then
            gestopt = rst.Fields(14).Value
        End If
 
        datum = Mid(rst.Fields(5).Value, 1, 10)
        datum = DateFunctions.dateFormat(datum)
        key = "aer" & rst.Fields(0).Value
        db.Execute "INSERT INTO LinkTabel (unikey, eadnr, datum) VALUES ('" & key & "'," & rst.Fields(3).Value & ",#" & datum & "#);"
        db.Execute "INSERT INTO Aerosol (dkey, eadnr, eersteZendtijd, VerslagNr, cnr, TypeToestel, Hulpstuk, Medicatie, Frequentie, Onderhoud, Start, Stop, unikey ) VALUES(" & rst.Fields(0).Value & "," & rst.Fields(3).Value & ",'" & rst.Fields(5).Value & "'," & rst.Fields(7).Value & "," & rst.Fields(6).Value & ",'" & rst.Fields(8).Value & "','" & rst.Fields(9).Value & "','" & rst.Fields(10).Value & "','" & rst.Fields(11).Value & "','" & rst.Fields(12).Value & "',#" & gestart & "#,#" & gestopt & "#,'" & key & "');"
 
        rst.MoveNext
    Loop
    Debug.Print i
 
BuildImport_Exit:
    Set rst = Nothing
    Set db = Nothing
    Exit Function
 
BuildImport_Err:
    MsgBox Error$
    Resume BuildImport_Exit
End Function

Please help me :D
(got to finish this today :s)

Greetz
Twanne

1. In Line #10 you are declaring i as an Integer.
   

   Dim i As Integer

2. In Line #13, you are using i as a String by Delimiting it in while creating the Recordset.
   

   Set rst = db.OpenRecordset("SELECT * FROM ExportAerosol WHERE omit = 'i';")

   At this point i has not been initialized and has a value of 0 - are you aware of this? If you are and this is your intention, they try:
   

   Set rst = db.OpenRecordset("SELECT * FROM ExportAerosol WHERE omit = " & i ";")

[ADezii]

Could somebody please help me??? I really need this done, but I have no idea where things go wrong.

1. In Line #10 you are declaring i as an Integer.
   

   Dim i As Integer

2. In Line #13, you are using i as a String by Delimiting it in while creating the Recordset.
   

   Set rst = db.OpenRecordset("SELECT * FROM ExportAerosol WHERE omit = 'i';")

3. At this point i has not been initialized and has a value of 0 - are you aware of this? If you are and this is your intention, they try:
   

   Set rst = db.OpenRecordset("SELECT * FROM ExportAerosol WHERE omit = " & i & ";")

   which would be the same as
   

   Set rst = db.OpenRecordset("SELECT * FROM ExportAerosol WHERE omit = " & 0 ";")