Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Unanswered: Linked table <> Internal Table

    I have an interesting conumbrum:

    ImportLead query:
    SELECT * FROM [LEADDATA$] IN '' [Excel 5.0;HDR=NO;IMEX=2;DATABASE=C:\DOCUME~1\ADMINI~1\LO CALS~1\Temp\TCS20060808222906.XLS;TABLE='LEADDATA' $];

    UpdateQuery query:
    INSERT INTO tblLeads ( PublisherID, FileDate, FName, LName, EmailAdd, Zip, PhoneNum, State, CallTime, Add1, Add2, City, WhenStart, HowMuch, HowLong, HowMany, HowMuchInvest, Interest, Survey, Comments, Campaign, EmailBatchSubject )
    SELECT [ImportLeads].F1, [ImportLeads].F2, [ImportLeads].F3, [ImportLeads].F4, [ImportLeads].F5, [ImportLeads].F6, [ImportLeads].F7, [ImportLeads].F8, [ImportLeads].F9, [ImportLeads].F10, [ImportLeads].F11, [ImportLeads].F12, [ImportLeads].F13, [ImportLeads].F14, [ImportLeads].F15, [ImportLeads].F16, [ImportLeads].F17, [ImportLeads].F18, [ImportLeads].F19, [ImportLeads].F20, 2 AS Campaign, 'Fw: [Bulk] Lead Report 7/27/06' AS EmailBatchSubject
    FROM ImportLeads
    WHERE [ImportLeads].F1 Is Not Null;

    Works like a champ when the destination table is in the same database file. Causes an error when the destination table is linked.

    The error is:
    Numeric field overflow. No number given. The only numeric field being written to is the "Campaign" field, which is Long Integer and I am fairly certain can handle the number 2.

    Both queries provide a peview without error (reguardless of the destination table's location).

    To further test, I ran it with the table linked (it errored) so I imported the table and ran it again and it worked fine, went back to the linked and it errored again, so the only difference in the tables was linked versus native.

    Why does Microsoft do things like this ?????

    tc

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What happens if you apply a CInt() to the nuisance column? Also - have you tried building the query up a field at a time (or knocked out the suspect column) just to be sure it is that particular one?

    BTW - nice heterogeneous query
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Actually, the whole thing shown is drawn up dynamically. I copied the SQL statements from the immediate window and made a couple of queries just to test.

    About the numeric field, here's the funny thing: there's a couple of Yes/No fields, a couple of Memo fields and the rest are Text. The only Number field is Campaign, which is assigned a value of 2.

    I will try eliminating fields later today. Which ones should I eliminate - Text, Memo, Boolean or the only numeric?

    tc

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would get it down to:

    Code:
    INSERT INTO MyDestinationTable (IThinkThisIsMyProblemField)
    SELECT IThinkThisIsMyProblemField
    FROM MySourceTable
    Just to be 100% sure it is that field that is cocking up. If you don't get an error then you have saved yourself a lot of wasted effort and you need to track down the field that is causing the error. It seems likely it is this one but.... you never know.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Angry Is this a genuine Access bug?

    ok, I sleuthed it down to the following strange cause, and was unable to spot a fix.

    In the Address field, which is Text (Add1 in the destination, F10 in the source) there is a number in one of the records.

    Open the XLS and it shows that one cell as a number. Open the Query and you get a #Num in that field for that record.

    So, saved the XLS as a different name with the entire column formatted as Text and got the same results. Although, I noticed it still turned the phone numbers into scientific notation even though they are now "Text" .....

    So, added to the query "Format([F10]) As Add1" and got the same result.

    So, made the registry change Teddy suggested earlier in this post (mine was Text and 19 respectively, I change the latter to 0) and got the same result.

    Most frustrating is that if the destination table is local, it runs without error and omits the data. If it runs with the destination table linked (same table otherwise) then it gives the error

    Now what?

    tc

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi tc

    Yeah - the excel cell format is just that - a format. It is not a data type constraint so nothing of any real substance is altered.

    Have you tried:
    Code:
    INSERT INTO tblLeads (Add1)
    SELECT CStr([ImportLeads].F10)
    FROM ImportLeads
    WHERE [ImportLeads].F1 Is Not Null
    or (inelegent and maybe not good enough for your purpose):
    Code:
    .....
    WHERE [ImportLeads].F1 Is Not Null
    AND Not IsNumeric([ImportLeads].F10)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Maybe this macro?:
    http://support.microsoft.com/default...b;en-us;815277
    Ah - refined here:
    http://www.experts-exchange.com/Appl..._21106703.html

    I've found a few people with precisely the same problem on t'web (i.e. linked text\ excel file -> importing locally fine but numeric overflow to linked table). I don't think any quite diagnosed it as well as you. So far to a man they decided to import locally and then copy the data from local to BE.

    Anyway - in case you spot something I missed:
    http://www.google.co.uk/search?q=%22...=&start=0&sa=N
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Another option - you could test this manually first - what if you put an ' infront of the numeric value? If that worked you could write a macro to do that....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    Code:
    INSERT INTO tblLeads (Add1)
    SELECT CStr([ImportLeads].F10)
    FROM ImportLeads
    WHERE [ImportLeads].F1 Is Not Null
    Nope
    Quote Originally Posted by pootle flump
    Nope
    Quote Originally Posted by pootle flump
    Another option - you could test this manually first - what if you put an ' infront of the numeric value? If that worked you could write a macro to do that....
    Aha!

    Code:
    Sub AddApost()
        Dim cell As Object
     
        For Each cell In ActiveSheet.UsedRange
            If IsNumeric(cell.Value) Then
     
                cell.Value = "'" & cell.Value
     
            End If
     
        Next
     
    End Sub
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    hmmm, adding the apostrophe looks like a likely candidate. The catch will be how much time it adds to the process, but the files are generally pretty small and I have to open each one and resave already anyway.

    Luckily, Excel considers Date/Time to be a separate data type (sometimes I have to wonder .....)

    I have a Temporary Table Manager in use in this project, so I could utilize a temp table without ballooning the db out of control. The drawback is that when it successfully imports to the local table, it skips the fields it doesn't like, which results in loss of data.

    I ran a mini-test and it looked good - I'll give this a spin on the real project tonight.

    Thanks!
    tc

  11. #11
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    pootle, can you repost the link to the experts exchange article - it's coming up with the '...' in the link and therefore not working.

    thx!

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It doesn't work in the quote but the original seems fine. Anyway:
    http://www.experts-exchange.com/Appl..._21106703.html

    BTW - I think you can make the code much more efficient. I don't think you need to loop through the cells - I am confident I have used the Range object to update masses of cells before.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    bummer ..... I can reproduce the error under different circumstances.

    If a date value is missing, Access puts a #Num in that field and generates the same error when the destination is linked. I thought a Date/Time field can handle a Null value, but not under these circumstances.

    Since I can not realistically rely on users of the www to correctly enter all of the information when submitting information, it would appear that this set of tools may not be up to the task.

    Hopefully Microsoft will remedy this bug as it will likely drive me to utilize a different platform (and steer my customers away from Access).

    Thanks for your help pootle!
    tc

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Works great for me. I only get a problem if an invalid date is entered. My version copes with valid and null dates fine.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can also validate input by highlighting cells and clicking Data-> Validation. This is the closest Excel has to constraints (TMK)
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •