I have an Access database that is linked to Oracle. Usign ADO, I am 
accessign Oracle stored procedures to insert new records, update records 
and delete records (i have multiple table operations. so i need procedures)

I do not want to hardcode the login details in the connection string as it 
is a multi-user  environment with varying priileges.
I am using Set cnxn = CurrentProject.Connection, so that the user's login 
and password that they entered at the beginnign are used. 

But it does not recognize the ORacle stored procedure. The Microsoft jet 
engine is looking for an input table or query by that name

My debug.print of cnxn shows that the provider is Microsoft.Jet.OLEDB.4.0, 
and not MSDAORA. 

If I hardcode the connection, the procedure is accepted. So problem is not 
the procedure
strcnxn = "Provider=MSDAORA;Data Source=prod;User ID=usr;Password=pwd;"

Please let me know how to tackle this issue. 
Thanks
Padmaja

Instead of linking the table to Oracle... why not use an Access Project file..
that way you will have access to the SPs..

--Tomm

> -----Original Message-----
> From: Padmaja Raghavapudi [mailto:padmaja_1@h...]
> Sent: Thursday, May 16, 2002 4:36 PM
> To: ActiveX_Data_Objects
> Subject: [activex_data_objects] Oracle stored procedures not recognized
> in linked Access table
>
>
> I have an Access database that is linked to Oracle. Usign ADO, I am
> accessign Oracle stored procedures to insert new records, update records
> and delete records (i have multiple table operations. so i need procedures)
>
> I do not want to hardcode the login details in the connection string as it
> is a multi-user  environment with varying priileges.
> I am using Set cnxn = CurrentProject.Connection, so that the user's login
> and password that they entered at the beginnign are used.
>
> But it does not recognize the ORacle stored procedure. The Microsoft jet
> engine is looking for an input table or query by that name
>
> My debug.print of cnxn shows that the provider is Microsoft.Jet.OLEDB.4.0,
> and not MSDAORA.
>
> If I hardcode the connection, the procedure is accepted. So problem is not
> the procedure
> strcnxn = "Provider=MSDAORA;Data Source=prod;User ID=usr;Password=pwd;"
>
> Please let me know how to tackle this issue.
> Thanks
> Padmaja

I might be getting this all wrong, but you could have a form which asks 
for the Username and Password, store these in variables then use these in 
the connection sting.

trcnxn = "Provider=MSDAORA;Data Source=prod;User ID=" & strUserName 
& ";Password=" & strPassWord & ";"

This might help. I also use this method to store Data Source in a Constant 
or an INI file so I can change it quickly if I neeed to.

I hope this might help you

Rob

> I have an Access database that is linked to Oracle. Usign ADO, I am 
a> ccessign Oracle stored procedures to insert new records, update records 
a> nd delete records (i have multiple table operations. so i need 
procedures)

> I do not want to hardcode the login details in the connection string as 
it 
i> s a multi-user  environment with varying priileges.
I>  am using Set cnxn = CurrentProject.Connection, so that the user's 
login 
a> nd password that they entered at the beginnign are used. 

> But it does not recognize the ORacle stored procedure. The Microsoft jet 
e> ngine is looking for an input table or query by that name

> My debug.print of cnxn shows that the provider is 
Microsoft.Jet.OLEDB.4.0, 
a> nd not MSDAORA. 

> If I hardcode the connection, the procedure is accepted. So problem is 
not 
t> he procedure
s> trcnxn = "Provider=MSDAORA;Data Source=prod;User ID=usr;Password=pwd;"

> Please let me know how to tackle this issue. 
T> hanks
P> admaja

Rob

I tried adding a login page and I was able to connect to the Oracle 
password using the input values for userID and Password. I have even set 
Persist Security Info = True for the connection.

Once the user is validated, he is transferred to the main menu. However 
when I click on any form that is accessing the linked tables, it prompts 
me again for the user name and password.

Can you please advise

Thanks
Padmaja

> I might be getting this all wrong, but you could have a form which asks 
f> or the Username and Password, store these in variables then use these 
in 
t> he connection sting.

> trcnxn = "Provider=MSDAORA;Data Source=prod;User ID=" & strUserName 
&>  ";Password=" & strPassWord & ";"

> This might help. I also use this method to store Data Source in a 
Constant 
o> r an INI file so I can change it quickly if I neeed to.

> I hope this might help you

> Rob

> > I have an Access database that is linked to Oracle. Usign ADO, I am 
a> > ccessign Oracle stored procedures to insert new records, update 
records 
a> > nd delete records (i have multiple table operations. so i need 
p> rocedures)

> > I do not want to hardcode the login details in the connection string 
as 
i> t 
i> > s a multi-user  environment with varying priileges.
I> >  am using Set cnxn = CurrentProject.Connection, so that the user's 
l> ogin 
a> > nd password that they entered at the beginnign are used. 

> > But it does not recognize the ORacle stored procedure. The Microsoft 
jet 
e> > ngine is looking for an input table or query by that name

> > My debug.print of cnxn shows that the provider is 
M> icrosoft.Jet.OLEDB.4.0, 
a> > nd not MSDAORA. 

> > If I hardcode the connection, the procedure is accepted. So problem is 
n> ot 
t> > he procedure
s> > trcnxn = "Provider=MSDAORA;Data Source=prod;User ID=usr;Password=pwd;"

> > Please let me know how to tackle this issue. 
T> > hanks
P> > admaja

What do you have on the form that connects to the Database. The ADO Data 
Control for example has settings for Connection String, Username and 
Password. If you do have one of these on your form, maybe you need to pass 
the username and password in to these as well. 
I have not had this problem my self, but on the times that the user is 
asked to enter a username and password I have not need to display data out 
of the database.

Rob

> Rob

> I tried adding a login page and I was able to connect to the Oracle 
p> assword using the input values for userID and Password. I have even set 
P> ersist Security Info = True for the connection.

> Once the user is validated, he is transferred to the main menu. However 
w> hen I click on any form that is accessing the linked tables, it prompts 
m> e again for the user name and password.

> Can you please advise

> Thanks
P> admaja

> > I might be getting this all wrong, but you could have a form which 
asks 
f> > or the Username and Password, store these in variables then use these 
i> n 
t> > he connection sting.

> > trcnxn = "Provider=MSDAORA;Data Source=prod;User ID=" & strUserName 
&> >  ";Password=" & strPassWord & ";"

> > This might help. I also use this method to store Data Source in a 
C> onstant 
o> > r an INI file so I can change it quickly if I neeed to.

> > I hope this might help you

> > Rob

> > > I have an Access database that is linked to Oracle. Usign ADO, I am 
a> > > ccessign Oracle stored procedures to insert new records, update 
r> ecords 
a> > > nd delete records (i have multiple table operations. so i need 
p> > rocedures)

> > > I do not want to hardcode the login details in the connection string 
a> s 
i> > t 
i> > > s a multi-user  environment with varying priileges.
I> > >  am using Set cnxn = CurrentProject.Connection, so that the user's 
l> > ogin 
a> > > nd password that they entered at the beginnign are used. 

> > > But it does not recognize the ORacle stored procedure. The Microsoft 
j> et 
e> > > ngine is looking for an input table or query by that name

> > > My debug.print of cnxn shows that the provider is 
M> > icrosoft.Jet.OLEDB.4.0, 
a> > > nd not MSDAORA. 

> > > If I hardcode the connection, the procedure is accepted. So problem 
is 
n> > ot 
t> > > he procedure
s> > > trcnxn = "Provider=MSDAORA;Data Source=prod;User 
ID=usr;Password=pwd;"

> > > Please let me know how to tackle this issue. 
T> > > hanks
P> > > admaja