Any way to force Access to use data type for linked tables?

[ggranger007]

I am having issues with an earlier join created against a linked table. Access decided it wanted to change from "text" to the "number" data type and now I am receiving a "type mismatch in expression" error.

The issue is, most of the data in the linked table is a number, however there are a few exceptions where it has to be checked against data with characters (i.e. 98A2). Is there any way to change or force the data type for a linked table?

 

[ggranger007]

Nevermind. I just found Sydney's short answer and long answer. Basically you can't. There is no way to do this practically or cleanly. Sort of renders the feature worthless or at best unreliable. I really wish people would start holding MS's feet to the fire for these kind of bugs.

I wonder if you can do this in Base...

http://www.mrexcel.com/forum/showthread.php?t=399148&highlight=linked+tables
http://www.mrexcel.com/forum/showthread.php?t=315752&highlight=linked+tables

 

[james_lankford]

What exactly is the problem? I didn't really understand your post.

Access doesn't change the datatype for linked tables. And you shouldn't be changing them either. You shouldn't be able to change things in a linked table; the idea is that its not your table, its someone else's and you're just borrowing the data.
Having a linked table doesn't mean you have your own private copy of the table. You just have a link to the actual table. You should not have the ability to change the data structure or datatype of a linked table. If you could then you would be changing the original table and that would affect other applications that depend on the table.
If you need your own private copy then import the table. Don't link it. When you import it you can set the datatype to be what you want.

 

[ggranger007]

When Access is linking the data, it could easily reinterpret its own copy to fit whatever data type the user sets - no need to change the actual linked table itself and apparently it used to do this in 2000. It’s misinterpreting the data and making assumptions on behalf of the user - it’s a bug. You don’t need to apologize for MS.

Microsoft, please fix this…

 

[martingaleh]

7 Years later. Still broken. Hate Microsoft

 

[xenou]

I don't think it's a bug.

Spreadsheets don't have data typing - or rather, each cell individually can store numbers, texts, or dates. So all you can do is have Access guess at the data type of a "column" (something that doesn't make sense in Excel). A wrong guess isn't a bug - it's a byproduct of using data that is mostly "numbers" but occasionally "text".

Long story short - either don't mix text and numbers or put some obvious text values in the first few rows to force Access to properly work out the data type for this "table" (so-called, since there are no relational tables in Excel).

 

[martingaleh]

So all you can do is have Access guess at the data type of a "column"

This is not all you can do. You can also let the programmer specify what the datatype is. Power query lets you do that. SSIS lets you do that. Talend, pentaho, cloveretl, cognos, oracle, they all let you do that. It's a byproduct of laziness that forces us to add 25 lines of fake data on anything we want to import. I try to avoid access like the plague, but I currently have to use it again.

 

[xenou]

Hi, if you have to use Access (I can see you dislike it immensely) then I suggest you put one of the records with one of the Text values at the top before you link it. There are other ways to get the result you want but this would be the simplest way that is easily understandable for the majority of users. If you find this unbearable then we could get into more details but would better to know more about your data and what you want to do, in order to consider alternative ways to work with Access/Excel.

For my part, I simply never link Access to Excel. I have never tried to do so in any other platform so I really don't know how Talend, Oracle etc. would handle a linked table to Excel spreadsheets - that's probably irrelevant in this forum anyway.

 

[martingaleh]

1 isn't enough. Try it. you need 25. The best solution I've found is to use vba to add the 25 rows to force type. Then open access. You can imagine how easy this is to do.

 

[xenou]

Yes I was wrong - 1 was not enough, but 4 seems work. Still, if you are scripting some added rows with VBA then 25 is probably as good a number as any - a few too many is better than a few too few. That does sound like a very easy solution to me.