469,609 Members | 1,657 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,609 developers. It's quick & easy.

Freaky (:S) insert "errors"

65
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:
Expand|Select|Wrap|Line Numbers
  1. Function BuildImport()
  2.     Dim key As String
  3.     Dim datum As String
  4.     Dim gestart As Date
  5.     Dim gestop As Date
  6.  
  7.  On Error GoTo BuildImport_Err
  8.     Dim db As Database
  9.     Dim rst As Recordset
  10.     Dim i As Integer
  11.  
  12.     Set db = CurrentDb
  13.     Set rst = db.OpenRecordset("SELECT * FROM ExportAerosol WHERE omit = 'i';")
  14.     Do Until rst.EOF
  15.         i = i + 1
  16.  
  17.         gestart = #1/1/9999#
  18.         gestopt = #1/1/9999#
  19.         If Not (rst.Fields(13).Value = "") Then
  20.             gestart = rst.Fields(13).Value
  21.         End If
  22.         If Not (rst.Fields(14).Value = "") Then
  23.             gestopt = rst.Fields(14).Value
  24.         End If
  25.  
  26.         datum = Mid(rst.Fields(5).Value, 1, 10)
  27.         datum = DateFunctions.dateFormat(datum)
  28.         key = "aer" & rst.Fields(0).Value
  29.         db.Execute "INSERT INTO LinkTabel (unikey, eadnr, datum) VALUES ('" & key & "'," & rst.Fields(3).Value & ",#" & datum & "#);"
  30.         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 & "');"
  31.  
  32.         rst.MoveNext
  33.     Loop
  34.     Debug.Print i
  35.  
  36. BuildImport_Exit:
  37.     Set rst = Nothing
  38.     Set db = Nothing
  39.     Exit Function
  40.  
  41. BuildImport_Err:
  42.     MsgBox Error$
  43.     Resume BuildImport_Exit
  44. End Function
Please help me :D
(got to finish this today :s)

Greetz
Twanne
Aug 2 '07 #1
3 1462
Twanne
65
Could somebody please help me??? I really need this done, but I have no idea where things go wrong.
Aug 2 '07 #2
ADezii
8,800 Expert 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. Function BuildImport()
  2.     Dim key As String
  3.     Dim datum As String
  4.     Dim gestart As Date
  5.     Dim gestop As Date
  6.  
  7.  On Error GoTo BuildImport_Err
  8.     Dim db As Database
  9.     Dim rst As Recordset
  10.     Dim i As Integer
  11.  
  12.     Set db = CurrentDb
  13.     Set rst = db.OpenRecordset("SELECT * FROM ExportAerosol WHERE omit = 'i';")
  14.     Do Until rst.EOF
  15.         i = i + 1
  16.  
  17.         gestart = #1/1/9999#
  18.         gestopt = #1/1/9999#
  19.         If Not (rst.Fields(13).Value = "") Then
  20.             gestart = rst.Fields(13).Value
  21.         End If
  22.         If Not (rst.Fields(14).Value = "") Then
  23.             gestopt = rst.Fields(14).Value
  24.         End If
  25.  
  26.         datum = Mid(rst.Fields(5).Value, 1, 10)
  27.         datum = DateFunctions.dateFormat(datum)
  28.         key = "aer" & rst.Fields(0).Value
  29.         db.Execute "INSERT INTO LinkTabel (unikey, eadnr, datum) VALUES ('" & key & "'," & rst.Fields(3).Value & ",#" & datum & "#);"
  30.         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 & "');"
  31.  
  32.         rst.MoveNext
  33.     Loop
  34.     Debug.Print i
  35.  
  36. BuildImport_Exit:
  37.     Set rst = Nothing
  38.     Set db = Nothing
  39.     Exit Function
  40.  
  41. BuildImport_Err:
  42.     MsgBox Error$
  43.     Resume BuildImport_Exit
  44. 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.
    Expand|Select|Wrap|Line Numbers
    1. Dim i As Integer
  2. In Line #13, you are using i as a String by Delimiting it in while creating the Recordset.
    Expand|Select|Wrap|Line Numbers
    1. 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:
    Expand|Select|Wrap|Line Numbers
    1. Set rst = db.OpenRecordset("SELECT * FROM ExportAerosol WHERE omit = " & i ";")
Aug 7 '07 #3
ADezii
8,800 Expert 8TB
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.
    Expand|Select|Wrap|Line Numbers
    1. Dim i As Integer
  2. In Line #13, you are using i as a String by Delimiting it in while creating the Recordset.
    Expand|Select|Wrap|Line Numbers
    1. 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:
    Expand|Select|Wrap|Line Numbers
    1. Set rst = db.OpenRecordset("SELECT * FROM ExportAerosol WHERE omit = " & i & ";")
    which would be the same as
    Expand|Select|Wrap|Line Numbers
    1. Set rst = db.OpenRecordset("SELECT * FROM ExportAerosol WHERE omit = " & 0 ";")
Aug 7 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Coral Snake | last post: by
1 post views Thread by Robert Oschler | last post: by
reply views Thread by Steve Franks | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.