running Access prodedures using VB - Connection vs OpenCurrentDatabase

[jojones]

Hi

I am trying to run some prodecures in Access databases but have the following dilemma:

We have a user workgroup set so a username and password have to be entered before getting into Access. By using a Connection object (in VB) I can get past this security by putting the username and password in the dsn setup, but I cannot seem to kick of sub procedures through the connection object. Is it possible to do this?

The other way I have tried is to instantiate an Access.Application. Through this I can run my code, but I cannot find a way to automatically get past the user workgroup security.

I need some help... (clearly if you had met me!). Anyone!?

Working with Access 97 and VB6.
Let me know if you need more information.
ta muchly

 

[sunaj]

Hi,

You can use the adodb.command object to execute stored procedures.
Let me know if you need more help....
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[jojones]

Yes I do need more help. I tried to do this after having a look at the FAQ post re this, but it said it was looking for a table or query and I just couldn't work it out...

pls advise.

 

[sunaj]

Hi agian,

The syntax depends on if you want to return a recordset or not. I found this example on returning a recordset :

http://www.freevbcode.com/ShowCode.Asp?ID=3687

To execute a SP with input variables, but no return:
---------------------------------------------------
Dim Com as ADO.command

Set Com = new Command
Com.ActiveConnection = YourConnectionString
Com.YourSPName inputvar1,inputvar2
set com = nothing
---------------------------------------------------

To execute a SP with no input and no return:
---------------------------------------------------
Dim Conn as ado.Connection

Set Conn = new connection
conn.open YourConnectionString
conn.execute "exec YourSPName"
conn.close
set conn = nothing
---------------------------------------------------

Hope that helps.

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[jojones]

Sunaj - thanks. I have tried what you suggested, but get an error.

as my procedure has no input or return I used

Dim Conn as ADODB.Connection

Set Conn = new connection
conn.open mydsn
conn.execute "exec ElgarLibUpdate"
conn.close
set conn = nothing

But I get the error message:
"The Microsoft Jet database cannot find the input table or query ElgarLibUpdate. Make sure it exists or is spelled correctly"

I have a Sub ElgarLibUpdate() in a module called modMain in the db which I have creted the connection to. Is this not what I want?

Sorry to be such a pain.

 

[sunaj]

jojones,
I misunderstood you. I though you where talking about a stored procedure.
But I see know that you want to call a VBA sub in Access from VB. I think that only way to do that is indeed to instantiate an Access.Application
I have no answer to the workgroup security issue - sorry.
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[sunaj]

See thread222-7493 Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[jojones]

Thanks Sunaj

My next idea was to move all the code from the VBA Sub into the VB Sub, so I might try this. The thread you sent me should be very helpful.

thanks again for your help.