Linked table issues.

Question

Linked table issues.

Debbie Edwards

SSCrazy Eights

Points: 9217
More actions
February 20, 2007 at 8:49 am

#68190

Hello again all.
I have a table in SQL Server that contains catagories. I have linked this to an access database so I can create different queries using the catagories so
catagory = 'Data Received'
When I do this on the linked table it doesnt filter correctly but when I filter on the table after I have imported it it works fine.
Does anyone know of any issues in regards to this about linked tables. Im using a user DSN to link to SQL Server.
Thanks
February 23, 2007 at 8:00 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Yuri Nogin Grasshopper Points: 16 More actions · Answer 1

What kind of filter do you apply? Is it a where clause? Try to open the linked table in the query designer and use SQL. It should work OK. In general, Access Jet SQL is different from SQL Server or ODBC.

Andrew Collett SSC Enthusiast Points: 129 More actions · Answer 2

I'm assuming that you're not doing anything clever with lookups in Access...

By this I mean that you've got a category numeric field that's got a lookup setup on the table to another holding the category description - like all good normalised databases would have I suppose!

This causes problems as access will display the looked up value in all query results and in table views but in fact is storing the numeric value behind it, so a query won't find that category by description, only by numeric value.

Essentially, most Access bods shy away from lookups in table design for this reason, but it does make for a RAD style of form design - much easier!

Anyway, assuming that you've not got that issue it's a bit of an odd one. I'd be tempted to try a category with underscores_instead_of_spaces and all in lower case to ensure there's no case sensativity going on.

The other way to do that is to wrap your query attribute in UCASE() and then in the 'filter' options write it in upper case (LCASE works too IIRC).

Oh, and if there's spaces after the name then there's a risk too. So how about:

select * from tblWhatever where trim(lcase(FldSomething)) = 'criteria'

And see how that gets on...

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 3

Hello again,

Its very strange, filters just dont work but I do a MAKE table from the linked table and the filters work fine so this is how I have resolved it. Every time I go in I just click on the make table button and use this for querying.

Thanks again

Debbie

Andrew Collett SSC Enthusiast Points: 129 More actions · Answer 4

Ah, an incling of a memory on this one, I don't think you can use the apply filter routine within forms for linked tables.

I seem to remember having the same problem in fact.

IIRC (and I can check if you want a solution) what I did was...

Option1:

Build a query that generates the results set that I want - the issue here is that it's going to be non-updatable as there's linked tables.

Then on that query I applied a function that I'd written that was something along the lines of

Function StrOut(StrIn as string, BlChk as boolean) as string
if BlChk = true then
StrOut = "*"
Else
StrOut = StrIn
End IF
End Function

Of course, you'll want to pretty that up and sort out nulls.

Anyway, within query design, you can then set your criteria to a combo box made up of a distinct list built from the particular field you're trying to filter on, coupled with a check box for all (and some logic to ensure you unselect the check box as soon as you select something off the list).

So items:

1. Distinct list of field query - snapshot view - this is your prospective active filter list.

2. Build filter form, make the combo list look to this list so it's dynamic. Lets call it forms.filt.list

3. Put a check box for all on there too. Lets call it forms.filt.chk

4. Build some logic around said drop box and check box.

5. Build the code above.

6. Build a query around your data set - and in the criteria for that query, put something along the lines of: Like StrOut(forms.filt.list, forms.filt.chk)

7. This'll give you the ability to call that query, and depending on the values on the form you'll have a filtered list.

8. You can do this for every field you want in that query, so quite powerful.

The other option, Option 2, is essentially the same, but if you build a query into table output within the same form as a tab second to the main data table within the same form, and have a me.refresh on change on the list you'll have an updated list, then have an action on double click that applies a filter based on the unique ID of that particular record and pops back to the main table (tab) then you've got record level drill down from a summary level list with filters. And the table will be editable.

Or something like that...

Not that I'm missing writing databases or anything!

Hope this helps someone.

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 5

Thanks for that,

Its good to know that someone else has had the same problem with this too. I did it very simply by just making a table from the original linked table as Im not that great with Access.

Debbie