Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Lounger
    Join Date
    Sep 2005
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sql server and access (2002)

    hi all.
    i have written part of a stored proceedure, it is very simple as its my first one, but not quite finished. the code in access will pass the 2 parameters to the stored proceedure, but im a bit stuck. take a look.....

    ALTER PROCEDURE dbo.SetOption
    (@Opkey nvarchar(20),
    @OPSwitch bit)
    AS SELECT OPKey, OPSwitch
    FROM dbo.[System Options]
    WHERE (OPKey LIKE @opkey) AND (OPSwitch LIKE @opswitch)

    -- is the switch set to true?

    if (select count(*) from [system options]
    WHERE (OPKey LIKE @opkey) AND (OPSwitch LIKE @opswitch)) >= 1


    ** if there is a record when this runs i would like to return something back to the access code so that i can set a switch, if there are no records i dont want to set a switch.
    basically how do i finish this, and then what code do i need to run this from the adp and pass the parameters...and get a result back??
    please help me im hopeless

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: sql server and access (2002)

    Hi,
    You could write your stored procedure as:
    ALTER PROCEDURE dbo.SetOption
    (@Opkey nvarchar(20),
    @OPSwitch bit)
    AS
    IF (SELECT count(*) from [system options]
    WHERE (OPKey LIKE @opkey) AND (OPSwitch LIKE @opswitch)) >= 1
    RETURN 0
    ELSE
    RETURN 1

    and then you can call it with something like:
    <pre>Function TestSP(strKey As String, blnSwitch As Boolean)
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Set cnn = CurrentProject.Connection
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnn
    With cmd
    .CommandText = "SetOption"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters("@Opkey") = strKey
    .Parameters("@Opswitch") = blnSwitch
    .Execute
    If .Parameters("@return_value") = 0 Then
    Debug.Print "Found"
    'do whatever you need to do if it exists
    Else
    'do whatever you need to do if it doesn't exist
    Debug.Print "Not found"
    End If
    End With
    Set cmd = Nothing
    cnn.Close
    Set cmd = Nothing
    End Function

    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sql server and access (2002)

    Hi Wendell:

    I'm a 10-year Access development veteran. Several of my applications are in production here at work and everything is working fine. However, one is based on data from a commercial software which is not planning to continue to support its .mdb format. Also, our tables have grown to require two .mdb files to stay under the 2G .mdb limit. So, I plan to convert the back-end to SQL Server and need some advice. Since Microsoft is no longer developing ADPs, that does not seem to be a viable option for the client. Our systems guy hates Vista and is hoping to continue on XP.

    I am wondering if you still recommend retaining the Access .mdb client application with an SQL Server back-end or if we need to upgrade to, say dotNet at this point, now that Microsoft is threatening to pull the Jet database engine out from under us and possibly dump VBA on its 64 bit systems. Do you know their plans, and can you tell me what strategy you would recommend for Access developers going forward? I anticipate that the conversion will be quite a lot of work and want to make the best choice for a stable, scalable solution.

    Thank you, in advance, for your help.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sql server and access (2002)

    Warning: I'm not WendellB...

    In the beginning of this century, there was a feeling that Microsoft was going to give up on MS Access, but they seem to have changed their mind - they put considerable effort into Access 2007. Access is still promoted as front end to SQL Server databases, in the form of a regular database with linked tables instead of an .adp.

    Microsoft has been threatening to drop support for VBA for a long time, but the sheer amount of VBA code in existence will make it very difficult for them to do so.

    Of course, I cannot guarantee anything.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sql server and access (2002)

    Thanks, Hans.

    If we continue to develop client applications in Access .mdbs, what about Jet support? I have heard that Jet will no longer be included in MDAC going forward. Will we have to use pass-through queries for everything?

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: sql server and access (2002)

    Hi Kathryn,
    It's nice to be in the Lounge for a change! Life seems to have become a 24/7 support job these days. But to answer your questions - yes we still do virtually all of our development using an Access .MDB or .MDE as the client interface and use SQL Server for the backend tables. SQL presents numerous advantages over the Jet format - and a few disadvantages, the most obnoxious being occasional difficulties in establishing an ODBC connection if your network is not properly configured. (I guess that sticks in my head at the moment, as I spent a number of days chasing down a network issue that was causing problems with a client 1000 miles away.)

    As to your worries about Jet, it's not likely to go away anytime soon from what I understand. In point of fact as Hans notes, they made several improvements in it in the 2007 version, several of which are now being "ported" to SQL Server 2008. And I seriously doubt they will dump VBA, even on the 64 bit platforms - it is too widely used in all of Office. And I'm sure there will be at least one and probably several more versions of Access in the next 10 years - the Access team blogs indicate they are working on fixing some of the issues that surfaced with 2007.

    So my recommendation would be to create an ODBC data source that lets you connect to the tables in SQL Server - you can try the upsizing wizard, but use 2003 or 2007 if you can. As to the version of SQL Server, I would suggest either 2005 or 2008. You can play with the Express version of either, but in the long run, upgrade to either the Workgroup or Standard version as the tools to do such things as backups and maintenance are much better. In other words, you get what you pay for. Note that you may need to do some performance tuning in order to get improvements in response time - the basic trick is to limit the data being sent to the workstation to just what the client needs. Doing that we see performance improvements factors that range from 10 to 1000 depending on table sizes and the like. The version of Access to use is more tricky - there are some advantages in moving from 2002 to 2003, while going to 2007 presents some questions in terms of the user interface and what you want to do with the ribbon bar for example. Most of our clients are still on 2003, but several are looking at 2007 in the near future. Hope this helps some and doesn't confuse too much.
    Wendell

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sql server and access (2002)

    MS Jet used to be included in MDAC downloads, but starting with MDAC 2.6 it's not included any more (the current version is MDAC 8).

    MS Jet is included in Access, of course, and it's available as a separate download - see How to obtain the latest service pack for the Microsoft Jet 4.0 Database Engine.

  8. #8
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sql server and access (2002)

    Thanks to both of you.

    So, are you saying that I can continue to use my Access .mdb as a client development tool using the Access SQL query language on Jet from recordsets pulled from ODBC linked tables on SQL Server 5 and Microsoft will not pull the rug out from under my feet? All I have to do is either pull data into local Jet tables or re-write my DAO code as ADO using the ODBC connections?

    If that is the case, it would make life a lot easier.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sql server and access (2002)

    Yep, that's it, basically.

  10. #10
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sql server and access (2002)

    Great! Thanks!

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: sql server and access (2002)

    The basic issue with stored procedures is that they don't really support complex logical conditions very well, and they are a total pain to debug. Other than that they are wonderful if you have a performance issue or the like. What you need to do to complete your logical test is to use the CASE statement - that's about the only way of really working with T-SQL when you need to test conditions. And the syntax isn't the same as the VBA Select Case, so be sure to look at the Books Online to see how it is constructed. The ADP tools will help you some, but you ultimately have to use the SQL Server documentation to figure out how to do things.

    In a more general vein, why did you choose to use an ADP as opposed to an ODBC-linked MDB front-end? The official party line from Microsoft is that the ADP will continue to be supported in future versions, but will not receive any further enhancements. The experience of most professional developers who use Access as a front-end to SQL Server is that the MDB approach is much more efficient from a development cost - in other words, it takes less time to develop an application - estimates range as high as 3 or 4 to 1. Our own experience indicates that only when the application requires the absolute best performance should an ADP based solution be considered. In other cases, we develop using the MDB approach, then look for performance issues, and fix them using Pass-Through Queries and stored procedures. Hope this provides some perspective.
    Wendell

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: sql server and access (2002)

    Well, if Microsoft pulls the rug out from under your feet it will do it simultaneously to lots of us. And in general you don't need to pull data into Jet tables unless you want to do lots of massaging of the data. You do of course want to make sure that the tables in Access have a unique key - otherwise you will not be able to update it. And we recommend putting a SQL timestamp in each table - otherwise you are likely to get ODBC errors indicating that someone else has modified the record while you were working on it. The 2003 upsizing wizard will do that for you automatically as I recall. Finally, you can use DAO code as it is unless there is some compelling reason to use ADO - which I typically don't. DAO works just fine with ODBC linked tables.

    You also might want to look at the video that you can link to from this site: http://www.fmsinc.com/Consulting/sqlserverupsizing.aspx
    It is from the 2008 Tech-Ed and is a panel composed of Mary Chipman, Luke Chung and Armen Stein with two other people talking about some of the issues surrounding the migration from Access to SQL Server. No magic bullets, but lots of good practical advice.
    Wendell

  13. #13
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sql server and access (2002)

    Thank you, Wendell.

    re: "we recommend putting a SQL timestamp in each table" - do you mean any Access tables as well as the SQL Server tables?
    And do you recommend using the 2003 upsizing wizard for anything more than simply moving the tables over? I have heard negative things about the wizard.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sql server and access (2002)

    > "we recommend putting a SQL timestamp in each table"

    This applies to SQL Server tables only. See <post:=699,758>post 699,758</post:> for an explanation.

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: sql server and access (2002)

    Hans has answered your question about SQL Server timestamps - it's a special data type only available in SQL Server. Regarding the Upsizing wizard, I would suggest you try it to start, but take a hard look at the way the data types are mapped over - and be aware that referential integrity in Access gets mapped to triggers in SQL rather then using the inherent DRI feature of SQL Server. But it does give you a useful starting place. In the long run you may want to use the SQL Server tools to import the tables rather than using the upsizing wizard.
    Wendell

Page 1 of 2 12 LastLast

Posting Permissions

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