Excel Link Design

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am linking to an excel file that will be constantly updated with new data.
How can I define the data type. When I use the link option is quesses at the
wrong data type for a few fields.
 
You should probably write a macro that runs on the "close file" event to update your data ranges to the format you need to view in Access. What data types are you having problems with?
 
If you link, you are linking directly to the source. You cannot set different
datatypes in a direct link i presume...
 
You cannot set different datatypes in a direct link i presume...
How can I define the data type.
Click to expand...
Click to expand...

I don't think they are asking about specifying different data types in the two different files, it's probably an import issue....sounds like they are having problems with the default behavior of Access with imported files.
 
This issue is that Access is determing the datatypes based upon the data it
sees in the cells and it is getting it wrong. This is my issue. Is there a
way to link to an excel file where I set the datatypes?
 
Yes, there is...Access reads the first few records to determine the datatype of the fields. What data types in Excel do you need to change? What do you need to change them to in Access?

You cannot have different data types in both programs simultaneously with a link, but you can change the data type in Excel so its readable in Access.

Are you getting messages like #Num! and #Name!?
 
Last edited:
Paul,

In such cases I opt for the import option. I create an import specification
and use that in combination with some sort of automation. Within the
importspecification you are able to set your own datatypes and with VBA you
can automate the process of importing by using the docmd.transfer option.

hth
 
Thanks for the info. I am not VBA maven, so I am hoping that I can get it to
work with macros. In essence I need to setup a table to meet my spec. I
then need to create a procedure that will erase the existing data and then
import the new data.

Thanks for the help
 
In such cases I opt for the import option.
Click to expand...
so I am hoping that I can get it to work with macros. In essence I need to setup a table to meet my spec. I then need to create a procedure that will erase the existing data and then import the new data.
Click to expand...
Importing everytime from a macro or manually will probably get annoying and its cumbersome. You might want to try the same thing that's in the Auto_Close macro of this Excel attachment. Everytime this file is closed, it runs a macro that forces text strings into the cells so that its readable in Access. This is a file that was shared on a network, edited in Excel, and used by some of the workers in Access. Thus, it needed to be compatible in both programs. Something like this would probably be better than importing, seeing that it never has to be messed with after you create the macros in Excel.
 

Attachments

This issue is that Access is determing the datatypes based upon the data it
sees in the cells and it is getting it wrong. This is my issue. Is there a
way to link to an excel file where I set the datatypes?
Click to expand...

You cannot explicitly set the data types (like you can with a
schema.ini file when querying a text file) but you can influence the
choice of data type. For details, see:

External Data - Mixed Data Types
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

Jamie.

--
 
Back
Top