SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard silver trophy KLB's Avatar
    Join Date
    Nov 2003
    Location
    Maine USA
    Posts
    3,781
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question MS-SQL Stored Procedures

    I have several questions in regards to MS-SQL and stored procedures:

    1) How can one write a stored procedure into a saved view such that the view's SQL statement treats the stored procedure as a dataset? I have successfully called a stored procedure from a MS-SQL view, but I cannot get MS-SQL's Enterprise Manager to allow me to save the view.

    2) How can I call a stored procedure from an MS-Access 2000 database using VBA? I've tried finding documentation on this, and I know it can be done, but most of what I have found is poorly written or incorrect. In particular, if I need to write a database connection string, I'd prefer that Access refer to a separate file DSN that the Access database uses access its linked tables.

    For purposes of any examples, it should be assumed that the DSN file for my connection string is "\\myserver\databases\dsn\MyDatabase.dsn", the SQL database is "\\myserver\MyDatabase", the stored procedure is "spMyStoredProcedure". If the connection string must be written into the VBA code rather than referring to a separate file DSN, then Windows authentication should be used rather than passing a user name and password.

    I really appreciate any advice or guidance.
    Ken Barbalace: EnvironmentalChemistry.com (Blog, Careers)
    InternetSAR.org
    Volunteers Assist Search and Rescue via Internet
    My Firefox Theme: Classic Compact
    Based onFirefox's default theme but uses much less window space

  2. #2
    SitePoint Member
    Join Date
    May 2004
    Location
    London (sometimes Sheffield), UK
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by KLB
    1) How can one write a stored procedure into a saved view such that the view's SQL statement treats the stored procedure as a dataset? I have successfully called a stored procedure from a MS-SQL view, but I cannot get MS-SQL's Enterprise Manager to allow me to save the view.
    I'm not sure I understand what you are trying to do. Why are you trying to write a Stored Procedure into a view? Do you mean you want to use the returned dataset from the Stored Procedure in the view? Could you explain what you are trying to achieve? A bit of background?

    Quote Originally Posted by KLB
    2) How can I call a stored procedure from an MS-Access 2000 database using VBA? I've tried finding documentation on this, and I know it can be done, but most of what I have found is poorly written or incorrect. In particular, if I need to write a database connection string, I'd prefer that Access refer to a separate file DSN that the Access database uses access its linked tables.
    I have some sample code for this at work. Do you still need this? Do you need to pass parameters to the Stored Procedure?

    Thanks

    Dave

  3. #3
    SitePoint Wizard silver trophy KLB's Avatar
    Join Date
    Nov 2003
    Location
    Maine USA
    Posts
    3,781
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I discovered that there are two different Access formats. There are Access databases (MDB) that we all know about, and then there are Access Database Projects (ADP). When working with a SQL server as the backend, it is preferable to use ADPs instead of MDBs. ADPs give direct access to stored proceedures where as MDBs do not. Hence my problem was solved once I converted to ADP from an MDB.

    It was one of those cases where my MS-SQL instructors ignored Access as it wasn't the thrust of their courses and my Access instructors ignored MS-SQL and thus ADPs because it wasn't the thrust of their course.

    Documentation was pretty slim on the matter as everyone just assumed that one knew there were two Access database formats.
    Ken Barbalace: EnvironmentalChemistry.com (Blog, Careers)
    InternetSAR.org
    Volunteers Assist Search and Rescue via Internet
    My Firefox Theme: Classic Compact
    Based onFirefox's default theme but uses much less window space

  4. #4
    SitePoint Member
    Join Date
    May 2004
    Location
    London (sometimes Sheffield), UK
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by KLB
    I discovered that there are two different Access formats. There are Access databases (MDB) that we all know about, and then there are Access Database Projects (ADP). When working with a SQL server as the backend, it is preferable to use ADPs instead of MDBs. ADPs give direct access to stored proceedures where as MDBs do not. Hence my problem was solved once I converted to ADP from an MDB.
    Yeah I discovered that a couple of years ago when I looked into app developement with MS SQL and Access. I have found that ADPs can be a bit restrictive at times though. The main disadvantage is that you cannot set up local cache or temp tables. If it works for you though then go for it.

    There are a number of different ways in VBA to call stored procedures. You can also use a Pass-through Query to call a stored procedure.

    A good book to get your hands on is Chipman & Baron - Microsoft Access Developers Guide to SQL Server (ISBN 0-672-31944-6). It has been invaluable to me!

    Good luck with the rest of your project.

    Dave

  5. #5
    SitePoint Wizard silver trophy KLB's Avatar
    Join Date
    Nov 2003
    Location
    Maine USA
    Posts
    3,781
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dubya
    Yeah I discovered that a couple of years ago when I looked into app developement with MS SQL and Access. I have found that ADPs can be a bit restrictive at times though. The main disadvantage is that you cannot set up local cache or temp tables. If it works for you though then go for it.
    The only thing I really miss in MDBs that ADPs don't do very well is the query designer. The ADP query/view designer seems like it is a couple of generations behind enterprise manager.

    Temporary stored tables aren't a big deal for me. I never liked them to begin with.

    Unfortunatly I'm converting an Access database to a MS-SQL backend and the original creator(s) had a perverse love of Rube Goldburg style VBA with multiple interlocking functions stored in different modules to acomplish something I'd accomplish inside of a single query. I'll spend a couple hours just back tracking a group of functions just to figure out what the intent was. Where it would normally take a couple of hours to convert a form or report and clean it up, I'm spending a good day on each form or report for this database and then usually end up going back a half dozen times as I find new ways things are tangled together.

    Quote Originally Posted by dubya
    There are a number of different ways in VBA to call stored procedures. You can also use a Pass-through Query to call a stored procedure.
    I'll have to remember pass-through queries.

    Quote Originally Posted by dubya
    A good book to get your hands on is Chipman & Baron - Microsoft Access Developers Guide to SQL Server (ISBN 0-672-31944-6). It has been invaluable to me!
    Thanks for the book, I'll look it up.

    Ken
    Ken Barbalace: EnvironmentalChemistry.com (Blog, Careers)
    InternetSAR.org
    Volunteers Assist Search and Rescue via Internet
    My Firefox Theme: Classic Compact
    Based onFirefox's default theme but uses much less window space


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •