Access link to excel file

[delkath]

I have an excel file that I use as a template to store then replace new data in. I also have access link to this excel file.

I then have an access macro which takes the new excel data and uploads it into a table in access. Next, the macro runs a series of output queries and update queries.

When I first created this in my test DB it worked fine. Now that I want to take it into production, of course, it is not working because of a data mismatch in the Access to Excel link. In my access table I have column A stored as text and cannot change it to number because Access will dump my leading zeros. The data type in Excel column A (which is my link) is stored as number.

I cannot change the data type thru access because it is a link to an external source. I have tried formatting the excel cells (via Right-Click>Format Cells>Text) but access still reads the data type as text. Tried inserting a row above my data with A's in the cells in hopes to have access pick it up as text, no good.

Does anyone have any suggestions? My brain is fried for the day and I could use some help.

Thank you,

[NTC]

will what you posted has some incoherencies:

"access still reads the data type as text"
"hopes to have access pick it up as text, no good."

contradictory....

in any case it seems what you are trying is the right thing...but that you got yourself a bit mixed up....relook at it with a fresh eye and I'm sure you'll be able to make it work: focus on symmetry...have the excel column defined as text, insert some dummy alpha data in the first view lines to keep it as text, be sure the field type on the Access side is text, - - - and everything should work fine....

[delkath]

NTC, you are right, that was a little jumbled. Let me rephrase the statements and question for clarity to anyone reading the post and experiencing the same problem.

The problem occurs when I start my macro. The first command in the macro is to run a query that compares Column A in my Access Destination Table to the Externally linked Excel File, column A. This checks for duplicates and shows the user if any action needs to take place, and this is where I am getting blotched up with the Data Types. The rest of the macro performs fine. I cannot get Access to read the excel, Column A Data Type as text. I cannot change my Access column A Data Type to numerical because I loose data in the table from doing so.

I have since remedied the problem by instructing the macro to import the excel file into its own table with formatted data types. Then it runs a comparison query on the two access tables to check for dups, etc etc. That is working out fine. I just tend to bang my head on a process that worked great and then for what ever reason is not anymore. I want to know what happened and how I can get it back.

See below for revised contradictory statements noted by NTC. And if anyone knows how to fix the excel part please let me know.

Cheers!

>>I have tried formatting the excel cells (via Right-Click>Format Cells>Text) but access still reads the data type as text.
<<I have tried formatting the excel cells/column via Right-Click>Format Cells>Text, but when looking at the Data Type in Access thru Design View, the column in question (A), still turns up as a numerical value.

>>Tried inserting a row above my data with A's in the cells in hopes to have access pick it up as text, no good.
<<In this statement what I was trying to say is what you have suggested: "have the excel column defined as text, insert some dummy alpha data in the first view lines to keep it as text, be sure the field type on the Access side is text". Access>Design View>Data Type/ still shows the column as a numerical for some reason.

[siddhanth]

Hi,

try this - Copy all your data into an text file (I.e Notepad) and save the file. Next open your excel template and do data > import. make Sure this time you selected the data fields as text.

now save the file. Now you will see the fields as text in Access also. hope this helps.

please reply.

Thanks,
Siddhanth