error possibly caused by OCDB link..?

[Toadums]

Hey, so I am trying to create a query based on 2 tables. first table is just a boring normal table, but the second one is an OCDB DNS link...

they are related by one field, but when I try to view the query in datasheet view, i get this error...not sure if it is because of the link...thats the only thing I can think of. here is a screen:

So Can i not make a query with a relationship set up that is a link to my sql server?

thanks!

 

[Banana]

If you open the sql_data table in design view (you'll get a dialog saying you can't do any design change blah blah. just ignore it and click 'Yes'), what does it say data type is for the "ProjectID"? Is that what you expect it to be?

Beyond that, the message means just that. You can't link on memo or any exotic data types and a good workaround is just to use a key... ideally an long integer (in backend, it may be called int but they're same size).


Note: Are you using MySQL? If so, I would strongly encourage you to not use the phrase 'SQL Server' as that's a Microsoft product and people will get confused. Call it MySQL or MySQL Server. Ignore if this is incorrect.

 

[Toadums]

sorry >.< i will try to get my terms correct lol.

but anyways...and as to if the data type is what I want, nope...it says that it is a memo...not a text like my mysql has...but I cant change it to text..when it is linked, it saves it as a memo, even though it is text in mysql...(an example of what these project numbers look like: 200-35504, and I cant change that...needs to be a text, not integer, unless I can include -'s in with the integer)

could it be because of the Collation in mysql? it is set to:
"utf8_generak_ci" but I dont know what that means, or what I should change it to..?

any ideas?

 

[Banana]

In MySQL, what is the data type for the same column? I think it may be a case of it being something like VARCHAR(2000) which would get interpreted as Memo in Access because Access can only handle 255 characters before it must use Memo, which is not what you want to do.

Also, your keys really shouldn't be a text; it's much more effective to use a number such as integer.

 

[Toadums]

In MySQL, what is the data type for the same column? I think it may be a case of it being something like VARCHAR(2000) which would get interpreted as Memo in Access because Access can only handle 255 characters before it must use Memo, which is not what you want to do.

Also, your keys really shouldn't be a text; it's much more effective to use a number such as integer.

its set to:

I tried changing it to an integer, but like i said, the project numbers are in the form "200-20503' so when I did that they all changed to just "200" or "100" dropped the stuff after the dashes (had to re add all manually >.<)

the only thing i can think of maybe is instead of using those project numbers, create unique keys without the dashes in my php code when the information is entered...?

so instead of mysql storing 200-20503 in the database, it will have 20020503, that way I can set it to an integer and just use a relation to display 200-20503 when it comes time to display stuff..

does this seem like the best option??

thanks!

 

[Banana]

Ah, no wonder.

"TEXT" in MySQL is basically equivalent to Access's "Memo", and you definitely don't want a Memo as a key!

Since you want to use your own format, integer isn't appropriate, so you need to use a smaller character data type. If your format is always same length, use CHAR(8) (assuming that 8 is the length of your format, "XX-XXXXX". If your format can vary, then it's more complicated. If it's only one or two characters, I'd stick with CHAR(X) with x being your biggest size, but if it can vary considerably (e.g., one can be "XXX-XXX" and other can be "XXXX-XXXX-XXXX", then use VARCHAR(X), with X again being your biggest size you ever need.

CHAR() means a fixed length string, so it always consume same amount of space, regardless of what is actually in it (all strings get padded with spaces to right), while VARCHAR() is variable-length string and only use as much space as needed to store the given string, plus a extra byte to give the size.

A even better solution is to add a new field and use it as key. That's named surrogate key because it's only for internal use, has no meaning, and will be much compact than a piece of string and can be functionally dependent on the current project ID. Call it "ProjectID" while the original ProjectiD would be probably something like "ProjectCode" because it's meaningful but not used as a key.


Regarding the question of storings ID as a number and display it at runtime: That is doable and with MySQL's extension for Zerofill, it's even possible with numeric fields which isn't possible in Access (e.g. you want leading zeroes; 000-0001... That can be done in MySQL by specifying ZEROFILL attribute.

The only consequence of doing this is that it coverts the integer to an unsigned integer, something that Access does not support. Assuming we're using INT here, we can certainly use 0 to 2 billion-something with no problem, but as we pass that mark, Access will not like it and the results may be undefined.

HTH.

 

[Toadums]

Ok. That is what I am doing right now. just making a new field, that is the same project id, except without the hyphon (you said int is best )

although ALL entries in this field will be of the format xxx-xxxxx, i think it would just be better to have an integer as a key

thanks so much for all your help!