Link Table ? (from Excel)

[McWhorter]

When I link an Excel worksheet into access97 the data types are automatically set. How can change them to what I want since it says you cant edit the link table?

Lets say to change it from a "Number" to "Text"

 

[mikey69]

I think that this is not possible directly. You could achieve the end effect by running a query on the Excel link and then using the Csng, Clng, Cvar functions to force the changes.

 

[McWhorter]

That may work but by then the data that is transeferred has already been lost... One column is a list of phone numbers. Access is labeling them as a "Number" but since the phone number has a "-" between them (123-4567), it reports the number incorrectly (#num!). So doing a query on that table wont fix it.

Any ideas, (besides fixing the excel sheet)

 

[RiverGuy]

Instead of linking a table, make a table first hand with all your fields and their respective data types. And then IMPORT the data into your existing table.

See if that works. If it does, you can set up a query to truncate the old data and re-import. You could then create a VB program to execute that query at specific times as well.

 

[mikey69]

Haven't tried it, but how about formatting the cells with phone numbers as text in Excel. That way they should be seen as text in Access. I have a feeling that this might work.