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' $];
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
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.
INSERT INTO MyDestinationTable (IThinkThisIsMyProblemField)
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.
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
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.
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.
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).