473,246 Members | 1,389 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,246 software developers and data experts.

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 1670
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,834 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,834 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

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

Similar topics

6
by: Coral Snake | last post by:
I am having problems with programming even simple "Hello World" programs from books and tutorials that use Python GUI libraries. Such Programs cause python to throw "Attribute Errors" even when the...
5
by: Michelle Kinsey-Clinton | last post by:
Hello, I am writing an ASP app which is giving me some very frustrating errors. They appear intermittently, no real pattern to them, and often go away if you reload, or back up a few pages and...
2
by: George Hester | last post by:
with Invalid ProgID. For additional information specific to this message please visit the Microsoft Online Support site located at: http://www.microsoft.com/contentredirect.asp. My other...
0
by: Henry | last post by:
Hi, I have cases where I connect to the web site and MySQL is not working : Message : "Too many connection errors", Unblock with 'mysqladmin flush-hosts'. What I did is a script that check...
1
by: Robert Oschler | last post by:
How can I get "parse" errors, not runtime errors, output to a log file of my choice (and preferably in a format I can choose)? I'm running PHP4. Thanks.
1
by: Derek | last post by:
I am writing a web application for job seekers, and am adding a section that allows administrators to upload jobs to the system via xml. I have used an xml schema to validate the xml they upload....
0
by: Steve Franks | last post by:
When I compile my application if there are any .net coding errors they show up fine in the error list in VS.NET 2005 following my attempt to compile. However there are also a ton of other errors...
7
by: iKiLL | last post by:
Hi all I am still pretty new to .Net and C#. I have come from a VB6 Background.
0
by: Gregory Gadow | last post by:
We have a number of development machines in our IT department, all running the same version of VS 2005 sp 1. Our company website and several compiled components were all written in VB.Net 2.0 using...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.