SQL Insert into Access from Access module

[ogri]

Hi

I am writing an Access application to read various Excel spreadsheets into a database. These Excel spread sheets are only slightly different (half a dozen ways of reading a spec!), and I have used different subroutines to read them and insert to the table.

This was working fine, but on one of the versions it is failing to insert anything. I have built up the following SQL statement:-

insert_string = "insert into DispReadsTemp (InpFileName, BatchId, DateInit, InitSupp, ITContact, AssocSupp, AssocSuppContact, MPANCore, Address, Postcode, EffFromSetDate, AddInfo, RejReasonInd, MeterID, MeterRegID1, DispDayRead, DispReadType1, COSReadRejCode1, PropDayRead, PropReadStat1, MeterRegID2, DispNightRead, DispReadType2, PropNightRead, PropReadStat2 ) values ('IABCDEFGH14005.xlw','IABCDEFGH14005','','ABCD','C Smith','EFGH','','1415937770006','7','WV3 0HB','30/11/2000','A text string','','S99B12345','01','67387','D','16','65470','P','02','36546','D','34667','P');"

And have executed this with :-

dbsDispRead.Execute insert_string, dbSQLPassThrough

This has produced no errors (Err.Number is set to 0), but has also affected to rows. It has not inserted anything onto the table.

As I say this has worked fine on several other subroutines with only minor differences, but not here.

Any ideas?

All the best

Keith

 

[Dodgey]

Hi Keith,

Here's a few ideas on breaking the problem down

1) Really obvious stuff, ensure the number of values equals the number of fields you are using

2) Data types. If the table uses any other other data type than a string (or text) then you could have problems with a "type mismatch". An example would be the fields:
DateInit (or any other database field that is not string). I notice that you enter a blank string here (i.e. ,'',). '' is not null, but ''. It won't interpret '' as nothing and therefore use the default or leave it blank but would probably hemrage.

3) The data itself. Make sure it doesn't break any rules outlined by the table, i.e. trying to stick data of 51 characters long into a field that can only accomodate 50!


Good luck. Hope it helps.