sql server and access (2002)

Topic

New Reply

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

Reply | Quote

Replies

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:

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Yep, that’s it, basically.

Reply | Quote

Great! Thanks!

Reply | Quote

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%5B/url%5D
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.

Reply | Quote

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.

Reply | Quote

> “we recommend putting a SQL timestamp in each table”

This applies to SQL Server tables only. See post 699,758 for an explanation.

Reply | Quote

Thanks, Hans. Sounds like another MS “gotcha” to me.

Reply | Quote

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.

Reply | Quote

Thanks, Wendell.

re: “referential integrity in Access gets mapped to triggers in SQL rather then using the inherent DRI feature of SQL Server” may I ask the dangerous “why?” question? And what should I do about it? Is it possible to disable this feature of the upsizing wizard?

I did watch the video of the TechEd panel discussion and thank you for the link. Where can I get more technical information about issues in upsizing from Access 2003 to SQL Server 2005? The books I’ve found talk about earlier versions, and I have searched for a training course or video on upsizing from Access to SQL Server in vain.

Thank you for your help.

Reply | Quote

Triggers are sort of like Access events on forms, except you only have Add, Update and Delete events. And it is useful to have the trigger available for other tasks besides referential integrity. Also the built-in data referential integrity in SQL Server is useful in creating entity relational diagrams. Note that SQL Server does not support Cascading Deletes in the current version – most people think that’s a good thing by the way. And I believe you can control the creation of referentail integrity triggers in the upsizing wizard – the 2007 version is supposed to be the most flexible in terms of options.

As to a book, I don’t know of a current book that focuses on the migration from Access to SQL Server. The Baron and Chipman book “Microsoft Access Developer’s Guide to SQL Server” is sort of considered the bible of this topic, but is from 2000, and I also like the Harkin and Reid book “SQL: Access to SQL Server” which was published in 2002. Frankly not that much has changed in the last 10 years from an upsizing perspective – the change has been in the user interface for SQL Server and it’s management tools. I also like volume 2 of the 2002 version of “Alison Balter’s Mastering Access 2002 Enterprise Development” as it has some good practical suggestions on multi-user databases and when to use SQL Server. Unfortunately I don’t know of any training courses on the subject either….

Reply | Quote

Thanks, Wendell.

I have ordered Chipman’s and Harkins & Reid’s books, also Dobson’s Professional SQL Server Development with Access 2000 – I guess I’ll have to wade through all 2500 pages, although Dobson just talks about ADPs, which I understand are not very robust. I’m also trying to buy a DVD of Mary Chipman’s and Armen Stein’s TechEd 2008 session, DAT303 – SQL Server Data Solutions Using Access. What I was really hoping for is an SQL Server 2005 course for Access developers.

It seems to me that there must be a huge niche market for training in this area, considering the popularity of MS Access for RAD and the increasing need to upgrade these apps to SQL Server . . . any entrepreneurs out there?

Reply | Quote