Acces/ODBC query

C

ccxc2003

I am trying to fill in a form with a query of an oracle database for some of
the data to then be put into an access database. I currently can query the
oracle database and get good results, and i have a form to post to the access
database. However when i try to set the results of the oracle query into
values on the access database i can not figure out the syntax.

This is what i have tried:
:tables!CarrierTable!ComponentName = queries!wdsqry!PART
and
:tempvar=wdsqry.part
then from there setting that into tables!CarrierTable!ComponentName

however none of that seems to work, if you have any ideas or any better ways
of doing this please let me know.


sql is as follows if curious.

SELECT WDS02_RCV.PART
'FROM WDS02_RCV
'WHERE (WDS02_RCV.RCVR) Like forms!CarrierTable!txtrvr+"*"
 
M

Mike Labosh

SELECT WDS02_RCV.PART
'FROM WDS02_RCV
'WHERE (WDS02_RCV.RCVR) Like forms!CarrierTable!txtrvr+"*"
Click to expand...

If this select statement is being sent to Oracle, keep in mind that Oracle
never heard of Access's peculiar syntax to refer to textboxes on a form. If
this is the case, try something like this in VBA:

Dim sql As String

sql = _
"SELECT WDS02_RCV.PART " & _
"FROM WDS02_RCV " & _
"WHERE (WDS02_RCV.RCVR) LIKE '" & _
forms!CarrierTable!txtrvr & "%'"

Me.RecordSource = sql
Me.Refresh

Also notice that Oracle uses a more ANSI compliant flavor of SQL, which uses
% instead of * for the wildcard. I'm not sure on quote usage, but in
Access, you can say:

....LIKE "pattern" <-- Double quotes

But in SQL Server (And Oracle also, I think):

....LIKE 'pattern' <-- Single quotes

--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS."
-- General Barringer, "War Games"
 
C

ccxc2003

Thanks for the help Mike,

However I think the query part works fine. I can run the query "wdsqry" and
it will give me a single result. Now i need to figure out how to put this
result into the database but listed with a few other queries with a new key
value in the access database.
I have a form on which there is a query button. When i put in the value for
txtrvr and click the query button i created, the query result appears in
table form. My biggest issue is getting that value to show up in another
text box on the form so that when i click the add record button i created on
the form, the values which now appeared in the form will be saved.

thanks again
 
Top