 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / July 2007Stored Procedures
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? 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, > [quoted text clipped - 18 lines] > mfG > --> stefan <-- 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, >> With CurrentDb >> .QueryDefs.Items("passThrough").Connect = _ >> .TableDefs.Items("linkedTable").Connect >> End With > Does the above code use any special references, as I can't get it to > compile. 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. It must be .Item in both cases. mfG --> stefan <-- > hi Nick, > [quoted text clipped - 17 lines] > mfG > --> stefan <-- 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? >> hi Nick, >> [quoted text clipped - 4 lines] >> > Does the above code use any special references, as I can't get it to >> > compile. All you should need is CurrentDb.QueryDefs("passThrough").Connect = _ CurrentDb.TableDefs("linkedTable").Connect  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) 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? 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, > [quoted text clipped - 8 lines] > 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 hi Nick, > Below you can see an > except of code, can I ask what I am doing wrong? > > DoCmd.OpenTable "tblUsers", acViewNormal, acReadOnly Please run ? CurrentDb.TableDefs.Item("tblUsers").Connect in the immediate window and post the connection string. mfG --> stefan <-- > hi Nick, > [quoted text clipped - 11 lines] > mfG > --> stefan <-- ODBC;Description=AddressData;DRIVER=SQL Server;SERVER=LON- BUILDS;APP=Microsoft Office XP;WSID=LON-CTX07;DATABASE=AddressDB On 4 Jul, 14:49, Nick 'The Database Guy' <nick...@btinternet.com> wrote: > > hi Nick, > [quoted text clipped - 14 lines] > ODBC;Description=AddressData;DRIVER=SQL Server;SERVER=LON- > BUILDS;APP=Microsoft Office XP;WSID=LON-CTX07;DATABASE=AddressDB Is it not working correctly because it is over a Citrix network? > On 4 Jul, 14:49, Nick 'The Database Guy' <nick...@btinternet.com> > wrote: [quoted text clipped - 19 lines] > > Is it not working correctly because it is over a Citrix network? 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 SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) On 4 Jul, 15:48, "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote: > > On 4 Jul, 14:49, Nick 'The Database Guy' <nick...@btinternet.com> > > wrote: [quoted text clipped - 36 lines] > > - Show quoted text - Hi Doug, Thanks for your code, however I am somewhat doubtful that this would work as the connection is not a trusted one. >> I believe the Connect property should be: >> [quoted text clipped - 11 lines] > Thanks for your code, however I am somewhat doubtful that this would > work as the connection is not a trusted one. 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 SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) On 4 Jul, 16:30, "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote: > >> I believe the Connect property should be: > [quoted text clipped - 26 lines] > > - Show quoted text - Thanks chaps, You saved the day again! On 4 Jul, 14:34, Nick 'The Database Guy' <nick...@btinternet.com> wrote: > > hi Nick, > [quoted text clipped - 24 lines] > > Thanks I mean 'exerpt of code' |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.