Master Work
asked on
how can I can filter external data when I connect to ODBC data source
how can I can filter external data when I connect to ODBC data source
hi Mohammed,
adding on to Jim's comment ...
if you don't know how to make a pass-through query:
An easy way is to link to the tables that you want, and make a regular query. Add some criteria too, whatever you might want to filter on. Then convert that query to a pass-through, which translates the SQL into its native format for the database server.
To change a regular query into a pass-through:
In the Query Type group of the Query Tools Design ribbon tab, click on "Pass-Through"
the SQL will be converted for you. Then you don't need the linked tables anymore.
To change the criteria, the SQL statement for the pass-through query can be modified.
have an awesome day,
crystal
adding on to Jim's comment ...
if you don't know how to make a pass-through query:
An easy way is to link to the tables that you want, and make a regular query. Add some criteria too, whatever you might want to filter on. Then convert that query to a pass-through, which translates the SQL into its native format for the database server.
To change a regular query into a pass-through:
In the Query Type group of the Query Tools Design ribbon tab, click on "Pass-Through"
the SQL will be converted for you. Then you don't need the linked tables anymore.
To change the criteria, the SQL statement for the pass-through query can be modified.
have an awesome day,
crystal
ASKER
The data is very huge and I need to select only the last date or last few days. How can I build the query. There is no option to do that.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you do a pass-thru query, you don't have to translate the SQL dialect. Your string is passed directly to the external database. The Access query engine doesn't see or alter the SQL.
Of course, the pass through query is not updateable.
Access makes every attempt to "pass through" all queries. Technically a pass through query is faster than an Access query because you bypass ODBC but usually not enough faster to make up for loosing the ability to update the recordset. You can certainly prevent that from happening but starting with a standard Access querydef is probably the path of least resistance. That way you have the GUI to help you build the query and the criteria. If you find that the query is too slow, then we can examine other options.
If you decide to use a pass through query, build and test it in the target system which sounds like it is DB2. Once it works, copy the SQL String and paste it into a querydef that you have defined as a pass through query. You will need to also supply a connection string.
Access makes every attempt to "pass through" all queries. Technically a pass through query is faster than an Access query because you bypass ODBC but usually not enough faster to make up for loosing the ability to update the recordset. You can certainly prevent that from happening but starting with a standard Access querydef is probably the path of least resistance. That way you have the GUI to help you build the query and the criteria. If you find that the query is too slow, then we can examine other options.
If you decide to use a pass through query, build and test it in the target system which sounds like it is DB2. Once it works, copy the SQL String and paste it into a querydef that you have defined as a pass through query. You will need to also supply a connection string.
<<The data is very huge and I need to select only the last date or last few days. How can I build the query. There is no option to do that.>>
Understand that there are many ways to do this, and it would help if you gave use a little more detail about what is involved (number of tables, number of records, what the query needs to return, using this for a form, report, etc).
As Pat said, the simplest is to start with a query in Access. If possible, Access will hand it off to the back end for processing even if it is not defined as a "pass through" query.
Be aware though that if you do define it as a pass through, then Access does not touch it in anyway. So the SQL statement must be something the back end will understand.
But with a normal query, as long as you:
1. Do not join to local tables
2.Do not use JET/ACE specific SQL
3. Do not use VBA expressions in your query
Then Access can hand off the query to the back end.
There is also a lot you can do in code with VBA using DAO and ADO data libs, so fill in some detail on what it is your trying to accomplish. and we can get you started in the right direction.
Jim.
Understand that there are many ways to do this, and it would help if you gave use a little more detail about what is involved (number of tables, number of records, what the query needs to return, using this for a form, report, etc).
As Pat said, the simplest is to start with a query in Access. If possible, Access will hand it off to the back end for processing even if it is not defined as a "pass through" query.
Be aware though that if you do define it as a pass through, then Access does not touch it in anyway. So the SQL statement must be something the back end will understand.
But with a normal query, as long as you:
1. Do not join to local tables
2.Do not use JET/ACE specific SQL
3. Do not use VBA expressions in your query
Then Access can hand off the query to the back end.
There is also a lot you can do in code with VBA using DAO and ADO data libs, so fill in some detail on what it is your trying to accomplish. and we can get you started in the right direction.
Jim.
ASKER
Please find the access file with the table I want to downlaoed directly from ODBC source. How can I write the SQL directly to the ODBC connection.
Database11.accdb
Database11.accdb
ASKER
Please find the steps needed in the attached document.
I-want-here-to-be-able-to-select-fr.docx
I-want-here-to-be-able-to-select-fr.docx
In order to get more support we need the end database with the imported table....BUT
since you have already reached this point just go to Query Designer Editor...select the imported table and start putting Criteria
On the last pic instead of my sheet1 you should find your imported table dbo_cp_case_attributes and select it
since you have already reached this point just go to Query Designer Editor...select the imported table and start putting Criteria
On the last pic instead of my sheet1 you should find your imported table dbo_cp_case_attributes and select it
ASKER
John,
I need this to be done before I got the table. The table is huge and it will take hours to be downloaded.
I need this to be done before I got the table. The table is huge and it will take hours to be downloaded.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Can you send me example
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
John,
Thank you so much for your help.
Regards,
Dallag
Thank you so much for your help.
Regards,
Dallag
Glad i helped you....:)
The later is far more efficient.
Also if it supports it, you can use a pass-through to execute a stored procedure on the server, which can build a recordset.
Jim.