[RESOLVED] Problem converting an EXCEL worksheet into an ACCESS table

[camoore]

Am developing a VB6 application in which an ACCESS table is linked using an ADODC. This all works fine.

The problem encountered is that our source data is in an EXCEL worksheet. We are using the MS Access import data routine to convert the EXCEL worksheet into an Access table. Sometimes this works fine, but sometimes for no immediately obvious reason the conversion routine will not copy any more than 14 fields across into an Access table. Additional fields come across as blank. When this happens, MS Access flags up that there has been an error, but does not assist by saying what it was.

The source EXCEL file worksheet has 27 columns and up to 1000 rows.

Thus this is not strictly a VB query but I am hoping that a VB6 forum member may have encountered a similar problem and be able to offer a suggestion.

Are different versions of EXCEL under which files are generated incompatible with different (earlier) versions of Access?

As an alternative (preferred not to be taken up) can anyone please point me to how to link an ADODC directly to an EXCEL worksheet rather than to an Access table.

camoore

Wales, UK

[Siddharth Rout]

1) Post a sample of the Excel file with which you are interacting or post the Excel File that is giving you the Error.
2) Also post the code where you are connecting with Excel and then exporting data to Access..

[camoore]

Thank you Koolsid.

I am not using VB6 to effect the EXCEL to ACCESS database change. I am using MS ACCESS as came with Office 2000. Hence my saying that this is strictly not a VB problem but maybe more of a MS one. But hopefully people expert in database manipulation might be able to offer suggestions.

I do not know how to interface VB6 with an EXCEL datafile / worksheet. If I could do thatI might be able to write my own routine to load up an ACCESS table. Hence my asking for a hint as to how to make VB6 talk to EXCEL. The interface via. ACCESS and an adodc control is straightforward, I find.

camoore

Wales, UK

[Siddharth Rout]

I do not know how to interface VB6 with an EXCEL datafile / worksheet. If I could do thatI might be able to write my own routine to load up an ACCESS table.

This will help you...

http://www.vbforums.com/showthread.php?t=391665

and this

http://www.vbforums.com/showthread.php?t=402060

[camoore]

Thank you Koolsid. through your pointers, and through MS article kb/257819 (which gave me the hint) I believe I have solved the problem posted, and so will mark the thread as resolved.

Missing data cells problem.

It seems that ACCESS / EXCEL programs make a "decision" as to the nature of a database column (field) based on its contents in the first few rows. If there are non-numeric characters present, it classes the column as STRING but if there are only numbers it classes it as NUMERIC. If it is STRING, then the programs are happy to allow any characters in any row of that field but if it is classified as NUMERIC then they will only allow numbers and reject any letters or mixture of numbers and letters.

That is what I think was happening. In the .xls file causing the problem, right at the top there were cells containing letters in the first few rows of fields 1 - 14 but after that nothing except pure numbers and/or voids. Therefore columns 15 onwards were getting classified as NUMERIC.

When you come to do the EXCEL - ACCESS conversion using the Import Data facility of MS ACCESS if it finds a non-numeric group in a NUMERIC column it just writes a null. That is why so many cells in fields 14+ were getting written across to the ACCESS table as voids and why the ACCESS import routine was flagging up an error (though rather unhelpfully did not say what it was).

So how to fix? I opened the "troublesome" .xls file in EXCEL, opened the relevant sheet and typed the word "TEST" (ie. a letter string) in the first row of every column which was either blank or only contained numerals in its first few rows. Then I repeated the ACCESS import routine and there was NO ERROR MESSAGE. By putting those non-numeric characters in the first row, I think that "convinced" the MS programs that all those columns were STRING and not NUMERIC.

Everything then appeared to copy across just fine - pure numerals and mixtures of numerals and letters - without any error message.

Obviously it is not elegant to require a user to do this typing into the head of every column of an .xls file.

I am now working on a way to link VB6 directly to an EXCEL file and to have the VB6 program automatically update the cells of the first row.

This is causing me another problem, but while closely related to the theme of this thread is a different coding issue and I will post it as a new thread if it continues to defeat my efforts.

Everything is easy once you know how! While the solution to this thread did not strictly lie within VB6, I hope that the solution found may be of use and interest to other database Forum members.

Thank you to responder(s).

camoore

Wales, UK