Linking Excel and Access

oyk

I hope someone could help me on this. I have linked an excel worksheet to
access but the some data shown in the access table has the #Num errors.
Presumably,
this must be because the data in the particular column in excel are of mixed
data-type. For example, there are numbers as well as texts like "abcd" etc.
in the same columns
I tried changing the cell format in excel to text but to no avail as the
problem still exist.

Really need some help here.

Thank you very much.

Nov 24 '06 #1

Klein-4

Couldn't you just change the date type in Access to Text...? If there
are text values in Excel, this seems to make sense...Otherwise -- and
this is somewhat superstitious, but I have seen it work -- you could
insert a new column in Excel, set the whole column to text, and the
copy the information over...

Ross La Haye
oyk wrote:

I hope someone could help me on this. I have linked an excel worksheet to
access but the some data shown in the access table has the #Num errors.
Presumably,
this must be because the data in the particular column in excel are of mixed
data-type. For example, there are numbers as well as texts like "abcd" etc.
in the same columns
I tried changing the cell format in excel to text but to no avail as the
problem still exist.

Really need some help here.

Thank you very much.

Nov 24 '06 #2

Klein-4

Sorry, "date type" should read "data type"...

Ross La Haye

Klein-4 wrote:

Couldn't you just change the date type in Access to Text...? If there
are text values in Excel, this seems to make sense...Otherwise -- and
this is somewhat superstitious, but I have seen it work -- you could
insert a new column in Excel, set the whole column to text, and the
copy the information over...

Ross La Haye
oyk wrote:

I hope someone could help me on this. I have linked an excel worksheet to
access but the some data shown in the access table has the #Num errors.
Presumably,
this must be because the data in the particular column in excel are of mixed
data-type. For example, there are numbers as well as texts like "abcd" etc.
in the same columns
I tried changing the cell format in excel to text but to no avail as the
problem still exist.

Really need some help here.

Thank you very much.

Nov 24 '06 #3

oyk

Hi Ross,

Very kind of you to respond but regret that the solution you give is not
implementable becoz I am trying Linking (not Importing) therefore, Access
does not allow editing of the properties of any field. Hope you have another
solution.

Thanks again for your willingness to help me.

"Klein-4" <rt****@new.rr.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.com...

>
Sorry, "date type" should read "data type"...

Ross La Haye

Klein-4 wrote:

>Couldn't you just change the date type in Access to Text...? If there
are text values in Excel, this seems to make sense...Otherwise -- and
this is somewhat superstitious, but I have seen it work -- you could
insert a new column in Excel, set the whole column to text, and the
copy the information over...

Ross La Haye
oyk wrote:

I hope someone could help me on this. I have linked an excel worksheet
to
access but the some data shown in the access table has the #Num errors.
Presumably,
this must be because the data in the particular column in excel are of
mixed
data-type. For example, there are numbers as well as texts like "abcd"
etc.
in the same columns
I tried changing the cell format in excel to text but to no avail as
the
problem still exist.

Really need some help here.

Thank you very much.

Nov 26 '06 #4

Klein-4

Ah yes. Then I would try to the second solution I proposed. Also,
paste just the values and not the formats when you do that...

Ross La Haye

oyk wrote:

Hi Ross,

Very kind of you to respond but regret that the solution you give is not
implementable becoz I am trying Linking (not Importing) therefore, Access
does not allow editing of the properties of any field. Hope you have another
solution.

Thanks again for your willingness to help me.

"Klein-4" <rt****@new.rr.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.com...

Sorry, "date type" should read "data type"...

Ross La Haye

Klein-4 wrote:

Couldn't you just change the date type in Access to Text...? If there
are text values in Excel, this seems to make sense...Otherwise -- and
this is somewhat superstitious, but I have seen it work -- you could
insert a new column in Excel, set the whole column to text, and the
copy the information over...

Ross La Haye
oyk wrote:
I hope someone could help me on this. I have linked an excel worksheet
to
access but the some data shown in the access table has the #Num errors.
Presumably,
this must be because the data in the particular column in excel are of
mixed
data-type. For example, there are numbers as well as texts like "abcd"
etc.
in the same columns
I tried changing the cell format in excel to text but to no avail as
the
problem still exist.

Really need some help here.

Thank you very much.

Nov 26 '06 #5

oyk

Hi Ross,

I have checked from accessmonster and managed to find the solution to my
problem. I used the TransferSpreadsheet method to import to ms access. After
the import, or even before the import, I could change the data types in the
access table. Everything is working quite beautifully now although not 100
percent. I am quite satisfied for now.

Just want to say a big thank you to you anyway. Have a nice day.

"Klein-4" <rt****@new.rr.comwrote in message
news:11**********************@45g2000cws.googlegro ups.com...

Ah yes. Then I would try to the second solution I proposed. Also,
paste just the values and not the formats when you do that...

Ross La Haye

oyk wrote:

>Hi Ross,

Very kind of you to respond but regret that the solution you give is not
implementable becoz I am trying Linking (not Importing) therefore, Access
does not allow editing of the properties of any field. Hope you have
another
solution.

Thanks again for your willingness to help me.

"Klein-4" <rt****@new.rr.comwrote in message
news:11**********************@m7g2000cwm.googlegr oups.com...

>
Sorry, "date type" should read "data type"...

Ross La Haye

Klein-4 wrote:
Couldn't you just change the date type in Access to Text...? If there
are text values in Excel, this seems to make sense...Otherwise -- and
this is somewhat superstitious, but I have seen it work -- you could
insert a new column in Excel, set the whole column to text, and the
copy the information over...

Ross La Haye
oyk wrote:
I hope someone could help me on this. I have linked an excel
worksheet
to
access but the some data shown in the access table has the #Num
errors.
Presumably,
this must be because the data in the particular column in excel are
of
mixed
data-type. For example, there are numbers as well as texts like
"abcd"
etc.
in the same columns
I tried changing the cell format in excel to text but to no avail as
the
problem still exist.

Really need some help here.

Thank you very much.

Dec 6 '06 #6

Klein-4

Yes, that's a good idea also. You're welcome. :-)

Ross La Haye

oyk wrote:

Hi Ross,

I have checked from accessmonster and managed to find the solution to my
problem. I used the TransferSpreadsheet method to import to ms access. After
the import, or even before the import, I could change the data types in the
access table. Everything is working quite beautifully now although not 100
percent. I am quite satisfied for now.

Just want to say a big thank you to you anyway. Have a nice day.

"Klein-4" <rt****@new.rr.comwrote in message
news:11**********************@45g2000cws.googlegro ups.com...

Ah yes. Then I would try to the second solution I proposed. Also,
paste just the values and not the formats when you do that...

Ross La Haye

oyk wrote:

Hi Ross,

Very kind of you to respond but regret that the solution you give is not
implementable becoz I am trying Linking (not Importing) therefore, Access
does not allow editing of the properties of any field. Hope you have
another
solution.

Thanks again for your willingness to help me.

"Klein-4" <rt****@new.rr.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.com...

Sorry, "date type" should read "data type"...

Ross La Haye

Klein-4 wrote:
Couldn't you just change the date type in Access to Text...? If there
are text values in Excel, this seems to make sense...Otherwise -- and
this is somewhat superstitious, but I have seen it work -- you could
insert a new column in Excel, set the whole column to text, and the
copy the information over...

Ross La Haye
oyk wrote:
I hope someone could help me on this. I have linked an excel
worksheet
to
access but the some data shown in the access table has the #Num
errors.
Presumably,
this must be because the data in the particular column in excel are
of
mixed
data-type. For example, there are numbers as well as texts like
"abcd"
etc.
in the same columns
I tried changing the cell format in excel to text but to no avail as
the
problem still exist.

Really need some help here.

Thank you very much.

Dec 8 '06 #7