f



MS Access 2002 problem: linked form fields in ODBC queries

Ok, my problem is the following:

I have very complicated Access 97 databases that link through ODBC to
Sybase databases.

Now in some of the forms controls I had queries that used as "where
clause" parameters form field values; For example:

     select foo from bar where a_colmun = [forms]![MyForm]![MyField]

This worked in Access 97 but fails in Access 2002 (ODBC failure) when
the table *bar* is an ODBC Sybase (in my case) linked table.

If *bar* were a local Access table, the query would do fine.

if i did

      select foo from bar where a_colmun = (select
[forms]![MyForm]![MyField] from dual)

where *dual* is a table with only one line of data. That works, but it
slows my queries.

Someone have a solution ? (i cannot upgrade to newer versions of
Access)

Thanx in advance

0
1/9/2006 1:07:29 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

2 Replies
680 Views

Similar Articles

[PageSpeed] 17

Per monnomiznogoud@yahoo.com.ar:
>Now in some of the forms controls I had queries that used as "where
>clause" parameters form field values; For example:
>
>     select foo from bar where a_colmun = [forms]![MyForm]![MyField]
>
>This worked in Access 97 but fails in Access 2002 (ODBC failure) when
>the table *bar* is an ODBC Sybase (in my case) linked table.
>
>If *bar* were a local Access table, the query would do fine.

Is the SQL in the form's .RecordSource or are you applying it from VBA code?

If it's in .RecordSource, you can probably stop here and move on the next
post...



I've had slightly similar problems when VBS uses a query that refers to a form's
control value.   Query works find when invokes by double clicking the query
object, but can't find the referenced control when invoked from VBA.

Workaround for me was to define a parameter in the query and explicitly load it
in VBA before opening an RS.
-- 
PeteCresswell
0
PeteCresswell
1/9/2006 2:40:45 PM
PeteCresswell

>Is the SQL in the form's .RecordSource or are you applying it from VBA code?

It is in the a list's record source in fact.

The IT services suggested to put back the Access 97 Sybase's patch :)

The only solution i found is the one i posted above, an innner select
using the form's field value with a local table (dual, a one-line
oracle-like table i defined)

0
1/10/2006 9:37:35 AM
Reply: