4

I'm having a problem with a button in MS Access querying a SQL View that doesn't have a foreign key.

My question is VERY similar to the question found here: MS Access error "ODBC--call failed. Invalid character value for cast specification (#0)" -- That seems to be my exact problem, but I'm not sure how to resolve it. Here are some more details. First, the exact error message:

ODBC--call failed.
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (#0)

Here's where I differ. I have a button in MS Access that does the following:

Private Sub btnMachineCutSheet_Click()
    Dim stDocName As String

    stDocName = "qryCutSheetByMachines"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

End Sub

The query you see listed there (qryCutSheetByMachines) is a pretty long query, but I've narrowed the problem down to one line. Here's the query:

SELECT 
  vwCutSheet.Network, 
  vwCutSheet.NetworkSpeed, 
  vwCutSheet.Duplex
FROM vwCutSheet 
INNER JOIN local_tblCreateCutSheet 
  ON vwCutSheet.EquipmentID = local_tblCreateCutSheet.EquipmentID;

Keep in mind that all the tables/views are linked tables from a MS SQL 2008 database

The first bit of oddness is that everything works just fine if I remove the vwCutSheet.NetworkSpeed, line. Unfortunately, I need that data.

Now, if I take out the JOIN statement the query works fine. Obviously I need the join or I wouldn't have it there. Now, the problem (I assume) is that the view (vwCutSheet) does not have a PK (should views have primary keys?). vwCutSheet.EquipmentID cannot be a PK though because there will usually always be a case of multiple EquipmentID's with the same value in this view.

And the last bit of information that might be messing things up is this. If I open up the view in Design view (in MS Access) I can see that Access is expecting a data type of "Number" - but if I look at the table that the view queries from originally, the data type is a varchar(5). I expect that Access is looking at the contents of the data and seeing nothing but numbers (values are 10,100,1000, and 10000).

I'm happy to say I didn't design this, so it's not my fault! Hah... but, I do have to support it, so it's up to me to make it work.

So... I think that's all the pertinent info. Let me know if you require more info and I'll edit my question as we go along.

Thanks in advance for any help!

EDIT: More info found:

The table that the view pulls from uses a column with a varchar(5) data type. The reason that field is a varchar(5) is because it's possible to specify a 'speed' of 10, 100, 1000, 10000, or Auto.

Additionaly, it is possible for the populated field in the view to be null.

Community
  • 1
  • 1
SeanW
  • 2,335
  • 6
  • 25
  • 26
  • If it's a casting error, I would look at the data type number/varchar FIRST...In SQL try doing a `SELECT * from table where ISNUMERIC(field) = 0`, though isnumeric has its own issues. – JNK Mar 15 '11 at 19:10
  • Is this a data problem? On the SQL Server end, are there empty strings in that field that should really be zeros, or some other value? Are there non-numeric characters in that field that should be cleansed? Can you execute a query on the sql server end that casts that column as a decimal datatype, or does that generate an error? – DCNYAM Mar 15 '11 at 19:24
  • Ahh yes, that's part of the problem I believe. I'll update my original post, but the reason that field is a varchar(5) is because it's possible to specify a 'speed' of 10, 100, 1000, 10000, or Auto. Additionaly, it is possible for the populated field in the view to be null. – SeanW Mar 15 '11 at 19:50
  • This is a wild suggestion, but some of the properties of a linked table are actually editable. Have you tried editing the linked table and trying to set the data type for the field? I assume this won't work, but it's worth a shot. – David-W-Fenton Mar 15 '11 at 20:06
  • Yeah, I tried editing that property without any luck. It thinks it's a number so I tried changing to text, but it wouldn't take it. – SeanW Mar 16 '11 at 01:20

2 Answers2

1

Not sure if you have permission to work on the SQL side, but if so, try creating your join there and see if it has a problem.

Also, consider changing your stored value 'auto' to something numeric and invalid, like -1, again assuming you can make changes on the sql side.

You may need to do some limiting or conversion on a linked table you can't change before you can join. Instead of the one query you have, you may need 3, if you need to manipulate data in both linked sources before you can join them.

In response to your comment, joining a table linked to SQL server and a local Access table should work, if the data types and values are compatible. If the linked table defined your key field as numeric, but contains text values, like 'auto', it can't work. You'll need to change the linked table to a text field instead of numeric so it can join with the text field in your local table.

Beth
  • 9,531
  • 1
  • 24
  • 43
  • I do have rights on the SQL side (I've got dbo rights). But, I can't do that. Here's why... I think you helped me identify the problem. If you look at my join, I'm joining a table named local_tblCreateCutSheet with my view named vwCutSheet. The view is in SQL, but the table is a local table in access. I'm off to google that up now, but I bet that's part of my problem. – SeanW Mar 16 '11 at 01:24
  • Thanks for the info Beth, good to know that it should work. You mention changing the linked table to a text field. The data type for that field in SQL is a varchar(5) so everything should be ok on the SQL side. I haven't seen that there's much I can change within Access on how it maps the linked tables, but I may be missing something. I'll keep investigating. Thanks! – SeanW Mar 16 '11 at 15:00
  • You can go into the designer of the linked table and see how Access mapped the varchar(5) field. If it isn't text(5), you can change it. – Beth Mar 16 '11 at 15:02
  • Right, it's set as number - but I can't change it. Or rather, I can change it, but I can't save it. By the way, this is Access 2003. – SeanW Mar 16 '11 at 18:21
  • well, then, you may need to change it to numeric on the sql side and use an invalid numeric value to mark 'auto', or back up the data in your table and delete all the rows with numeric values and refresh the link, then import the rows with numeric values again. – Beth Mar 16 '11 at 19:13
0

I'd say you have a data error. You're storing numbers in a text field, except for when you don't.

If instead you'd define 0 as meaning "Auto" then you could make it a numeric field. Or, use a lookup table and a foreign key value so that 1=10, 2=100, 3=1000, 4=100000 and 0=Auto. If you do that you could use the value as exponent (10^N), and the results would be 1, 10, 100, 1000, 10000, where 1 would be your Auto value.

Or make NULL = Auto, in which case 10^Null would still give you Null (Auto), while all the other values would work.

This would completely design away the problem.

But it does assume you have access to the data table.

As an alternative if you can't change the field's data type, you could create a view that does one of the above for you. To me the easiest thing to do is get rid of the damned text value, "Auto". Of I were doing that, I'd likely convert "Auto" to Null so the result would be a column of numbers with Nulls.

David-W-Fenton
  • 22,871
  • 4
  • 45
  • 58
  • Yeah, it sounds like something's going to have to give in the original design. I'm going to investigate what it's going to take to change up the original field tomorrow. I'll have to find everywhere else that this field is in use (between two or three different internal web apps that query against the same data). I may have to put a patch in place using a new view though. Thanks for the help! – SeanW Mar 17 '11 at 04:18