Stored Procedures

  • Thread starter Thread starter Nick 'The Database Guy'
  • Start date Start date
N

Nick 'The Database Guy'

Hi All,

Is there any way of accessing a stored procedure from a .mdb, using
VBA (ADOX) maybe? Also is there any truth in the rumour that
Microsoft are abandoning support for .adp projects?

Thanks in advance.
 
hi Nick,
Is there any way of accessing a stored procedure from a .mdb, using
VBA (ADOX) maybe?
Click to expand...
You may use ADO with an extra connection to your database or you use a
passthrough query like

EXEC sp;

To connect it to your server use

With CurrentDb
.QueryDefs.Items("passThrough").Connect = _
.TableDefs.Items("linkedTable").Connect
End With

than it will use the same session already established to your server by
"linkedTable".


mfG
--> stefan <--
 
hi Nick,

Nick 'The Database Guy' wrote:> Is there any way of accessing a stored procedure from a .mdb, using

You may use ADO with an extra connection to your database or you use a
passthrough query like

EXEC sp;

To connect it to your server use

With CurrentDb
.QueryDefs.Items("passThrough").Connect = _
.TableDefs.Items("linkedTable").Connect
End With

than it will use the same session already established to your server by
"linkedTable".

mfG
--> stefan <--
Click to expand...

Hi Stefan,

Does the above code use any special references, as I can't get it to
compile.

It fails on the TableDefs.Items with items/item (I tryed both) not
being found as a method or data member.

Again thanks,

Nick McMillen
 
hi Nick,
Does the above code use any special references, as I can't get it to
compile.
Click to expand...
A typo. Try using it in the immediate window (make it one line):

CurrentDb.QueryDefs.Item("passThrough").Connect = _
CurrentDb.TableDefs.Item("linkedTable").Connect
It fails on the TableDefs.Items with items/item (I tryed both) not
being found as a method or data member.
Click to expand...
It must be .Item in both cases.


mfG
--> stefan <--
 
hi Nick,

Nick 'The Database Guy' wrote:>> With CurrentDb

A typo. Try using it in the immediate window (make it one line):

CurrentDb.QueryDefs.Item("passThrough").Connect = _
CurrentDb.TableDefs.Item("linkedTable").Connect


It must be .Item in both cases.

mfG
--> stefan <--
Click to expand...

Sorry still does not work I tryed both on both already, and was
wondering if there is something else that I could do. Which version
of Access you on?
 
All you should need is

CurrentDb.QueryDefs("passThrough").Connect = _
CurrentDb.TableDefs("linkedTable").Connect
 
I got it now so that it works fine however it is still promting me for
the connection name when I try to execute a pass-through query. Is
there any way around this?

Thanks, again, in advance.
 
hi Nick,
I got it now so that it works fine however it is still promting me for
the connection name when I try to execute a pass-through query. Is
there any way around this?
Click to expand...
Then you have not opened the linked table before accessing your query.
The assignment of .Connect is only to share the same session to the
server, it does not establish one.


mfG
--> stefan <--
 
hi Nick,

Nick 'The Database Guy' wrote:> I got it now so that it works fine however it is still promting me for

Then you have not opened the linked table before accessing your query.
The assignment of .Connect is only to share the same session to the
server, it does not establish one.

mfG
--> stefan <--
Click to expand...

Thank you Stefan for your continued help on this one, but I am now
having the problem that it is still promting me for the connection,
this is after I open a linked table a (in code) and leave it open
until after the procedure has been called. Below you can see an
except of code, can I ask what I am doing wrong?

DoCmd.OpenTable "tblUsers", acViewNormal, acReadOnly
CurrentDb.QueryDefs("qryDelClientDetails-PT").Connect = _
CurrentDb.TableDefs("tblUsers").Connect

DoCmd.OpenQuery "qryDelClientDetails-PT"
DoCmd.OpenQuery "qryDelCCDetails-PT"
DoCmd.Close acTable, "tblUsers", acSaveNo

Thanks
 
hi Nick,
Below you can see an
except of code, can I ask what I am doing wrong?

DoCmd.OpenTable "tblUsers", acViewNormal, acReadOnly
Click to expand...
Please run

? CurrentDb.TableDefs.Item("tblUsers").Connect

in the immediate window and post the connection string.


mfG
--> stefan <--
 
Thank you Stefan for your continued help on this one, but I am now
having the problem that it is still promting me for the connection,
this is after I open a linked table a (in code) and leave it open
until after the procedure has been called. Below you can see an
except of code, can I ask what I am doing wrong?

DoCmd.OpenTable "tblUsers", acViewNormal, acReadOnly
CurrentDb.QueryDefs("qryDelClientDetails-PT").Connect = _
CurrentDb.TableDefs("tblUsers").Connect

DoCmd.OpenQuery "qryDelClientDetails-PT"
DoCmd.OpenQuery "qryDelCCDetails-PT"
DoCmd.Close acTable, "tblUsers", acSaveNo

Thanks
Click to expand...

I mean 'exerpt of code'
 
hi Nick,

Nick 'The Database Guy' wrote:> Below you can see an


Please run

? CurrentDb.TableDefs.Item("tblUsers").Connect

in the immediate window and post the connection string.

mfG
--> stefan <--
Click to expand...

ODBC;Description=AddressData;DRIVER=SQL Server;SERVER=LON-
BUILDS;APP=Microsoft Office XP;WSID=LON-CTX07;DATABASE=AddressDB
 
ODBC;Description=AddressData;DRIVER=SQL Server;SERVER=LON-
BUILDS;APP=Microsoft Office XP;WSID=LON-CTX07;DATABASE=AddressDB
Click to expand...

Is it not working correctly because it is over a Citrix network?
 
Nick 'The Database Guy' said:
Is it not working correctly because it is over a Citrix network?
Click to expand...

I believe the Connect property should be:

ODBC;Description=AddressData;DRIVER={SQL
Server};SERVER=LON-BUILDS;APP=Microsoft Office
XP;WSID=LON-CTX07;DATABASE=AddressDB;Trusted_Connection=yes

Realistically, you should be able to simplify that to


ODBC;DRIVER={SQL
Server};SERVER=LON-BUILDS;;DATABASE=AddressDB;Trusted_Connection=yes
 
I believe the Connect property should be:

ODBC;Description=AddressData;DRIVER={SQL
Server};SERVER=LON-BUILDS;APP=Microsoft Office
XP;WSID=LON-CTX07;DATABASE=AddressDB;Trusted_Connection=yes

Realistically, you should be able to simplify that to

ODBC;DRIVER={SQL
Server};SERVER=LON-BUILDS;;DATABASE=AddressDB;Trusted_Connection=yes

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Doug,

Thanks for your code, however I am somewhat doubtful that this would
work as the connection is not a trusted one.
 
Nick 'The Database Guy' said:
Hi Doug,

Thanks for your code, however I am somewhat doubtful that this would
work as the connection is not a trusted one.
Click to expand...

In that case, if you want to prepopulate the credentials, use

ODBC;DRIVER={SQL
Server};SERVER=LON-BUILDS;DATABASE=AddressDB;Uid=username;Pwd=password

or, to have it prompt for credentials, use

ODBC;DRIVER={SQL Server};SERVER=LON-BUILDS;DATABASE=AddressDB
 
In that case, if you want to prepopulate the credentials, use

ODBC;DRIVER={SQL
Server};SERVER=LON-BUILDS;DATABASE=AddressDB;Uid=username;Pwd=password

or, to have it prompt for credentials, use

ODBC;DRIVER={SQL Server};SERVER=LON-BUILDS;DATABASE=AddressDB

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks chaps,

You saved the day again!
 
Back
Top