Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Question Unanswered: MS SQL to MS Access

    Hi, Ive been doing some ASP.NET application development for some time using a MS SQL database backend. Im trying to make my applications more affordable to smaller businesses by using Access 2003.

    I was wondering if anyone knows a good reference on going from MS SQL Server to MS Access (I know it seems like I am going backwards.) For example can I declare variables in the queries and do IF statements like:

    DECLARE @Time datetime
    SET @TIME = SELECT myField FROM myTable

    or

    IF EXISTS( SELECT myField FROM myTable)

    Are these things possible in Access of would I have to do that all through the ASP.NET code now?

    Thanks for any help.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It is possible to do what you'd like to do, and I struggle with this same thing all the time

    You cannot use @variables in the access context. You CAN do just about anything youw ant, but the syntax is wildly different, you can plan on having do to some extensive recoding.

    Examples of translations for the situations provided:

    DECLARE @Time datetime
    SET @TIME = SELECT myField FROM myTable

    Dim time As datetime
    time = DLookup("yourField", "yourTable", "criteria")

    IF EXISTS( SELECT myField FROM myTable)

    If DCount("yourField", "yourTable", "criteria) Then..

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Thomas,

    Just to clarify . . .

    . . . you said that your current code uses variable is MSSQL queries and you want to know if you can do the same thing in Access.

    The code you showed, in fact, was not part of a query, but part of a stored procedure. Access does not have stored procedures, but it allows you to make VBA calls from within queries. So, in many respects, it is far more powerful than MSSQL. There are two problems, however. The first being the extensive coding that Teddy mentioned. The second being that all of your processing will now be happening on your client machine rather than on your central datastore and server. This may not be an issue, given that your intent is for small-scale users. But if you were dealing with very large amounts of data, and very complex VBA, you might run in to performace issues.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  4. #4
    Join Date
    Jan 2004
    Posts
    9

    Thumbs up

    Yes, sorry PracticalProgram is right it was more along the lines of stored procedures. I know from ASP.NET you can call Access queries much like stored procedures from SQLClient code but just using the OLEDB classes.

    Is it possible to insert those functions DCOUNT, etc... to muster up a Access equivalent of a MS SQL stored procedure?

    Im looking up information on those functions Teddy mentioned, thanks for the help guys.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Well, in a way yes, but not exactly..

    You can create a module that will hold variables and pass information between recordsets/do maintainence, run queries etc etc... But it's not really one single stored procedure, it could be a Function or public sub that could be called from wherever, it really depends on EXACTLY what you want to do.

  6. #6
    Join Date
    Jan 2004
    Posts
    9
    Hey Teddy do you have a sample Access query that you could show using the DCOUNT and DLOOKUP, or do I have to call those through VBA?

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by Thomas_Szafran
    Hey Teddy do you have a sample Access query that you could show using the DCOUNT and DLOOKUP, or do I have to call those through VBA?
    You can use them in a query or VBA, as I mentioned, the specific application will depend on what you would like to accomplish.

    You can use VBA functions to return values much the same as using subselects in MSSQL:

    SELECT stuff, (SELECT count(stuff) FROM somewhere)

    becomes

    SELECT stuff, DCOUNT("stuff", "somewhere")

    Of course you could still use the subquery if you'd like, but that's just a working example.

Posting Permissions

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