Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Oct 2003
    Posts
    103

    Unanswered: Access 2003 and SQL Server 2005

    So I have access 2003 and SQL Server 2005 and 2008 Express. I can't modify tables (not a real problem since I can use SSMS), but I also can't create queries; do I need to upgrade to 2007, is there a patch for 2003, or do I just need to get smart on how to create views and stored procedures in SSMS?

    I realize this is an Access forum, but which applies to which kind of query?
    Select, Select across multiple tables, Union, and crosstab? I am thinking I could just do straight SQL commands from the forms (dcmd.sql . . .) but I'm guessing that's slower, and also I might have trouble with some of the joins (not being a sql guy).

    What can you guys tell me about this?

    Thanks, and Happy Thanksgiving.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I am thinking I could just do straight SQL commands from the forms
    I think that is your answer... assuming that the reason you can't create queries is that you don't have linked tables back to SQL Server.

    If you DO have linked tables, then you should be able to create queries since query creation is independent of the back end.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm getting ready to install a Server OS and then SQL Server on a box here at the University of Wisconsin. I'm actually going to install Server 2003 though versus 2008 Express and SQL Server 2000 versus 2005.
    Reasons -
    1. I've already had issues with installing Server 2008 Express (it didn't like my RAID configuration and wouldn't recognize the harddrives.)
    2. I've heard a lot of good stuff about Server 2003. Although I might opt to go with Server 2000 if I want to also do all the service packs as I have a bit of experience with Server 2000.
    3. I think SQL Server 2005 is "bloated". SQL Server 2000 (Enterprise edition) was fast (even with it's little quirks) and was MUCH easier to use (ie. security.) Although I do like some of the wizards with SQL Server 2005, I picked up learning 2000 within a few days versus a few weeks with 2005 (reminds me of a an old trustworthy, simple to fix car versus a new "high" tech, plastic gizmo, all computerized car with all the fancy features.) Even SQL Server 2000 (Personal edition) has worked very well on my home computer with all the other software I run (as "Personal edition" is only for XP and "Enterprise edition" is for Server OS's and mult-user connectivity.)
    4. I can probably find a SQL Server 2000 version somewhere fairly cheaply.

    SQL Server 2005 versus SQL Server 2000 is actually like MSAccess 2007 versus MSAccess 2003 in that they moved all the menu's and everything around. If you like MSAccess 2003 versus 2007, you'll probably like SQL Server 2000 versus 2005.

    My guess is your issue is most likely related to the SQL Server 2005 security and the main user security settings or the specific user security settings for that database itself. Remember that you should usually assign users to "roles" and then give the "roles" specific permissions for each database. For example, you could have "dbo" permissions as a user in SQL Server itself (or be part of an "admin" role) but your permissions for that database limit you otherwise (or vice versa.) SQL Server 2005 I think though changed how they did "roles." There might also be other permissions involved (as SQL Server 2005 (in my opinion) was designed with what seems like a crazy permission system) but security is the only thing I can think of regarding your ability to design views/stored procedures.

    If it's MSAccess queries you can't create, it's probably the MSAccess security (if you're using an *.mdw security file.)

    Also, when I design SQL Server views/stored procedures, I usually use the graphical query designer in MSAccess first (since my tables are linked) and then change the view from Designer view to SQL view to copy my SQL statement (from MSAccess into the SQL Server View or Stored Procedure). It works fairly well but you have to remember that the SQL Server syntax is a bit different (and the table names may be different). You may want to look at the differences in the syntax before you use this method.

    Re: "I could just do straight SQL commands from the forms (dcmd.sql . . .)"

    If your tables are linked into the MSAccess application, you can use any of the methods you would to update a normal MSAccess table (via queries) or you can write vba code to open a recordset and update it. You can even write vba code to connect directly to a SQL Server stored procedure and execute it. Connecting to the SQL Server database (in vba code) can be done using an ODBC DSN Name (which you create via "Data Sources (ODBC)" to connect to the SQL Server tables or you can use the SQL Server driver itself to connect (ie. ODBC DSN-Less connection.) I personally like to just link the tables into the mdb and then just open a recordset against the table (versus writing code to always connect to SQL Server any time I want to work with the data.)
    Last edited by pkstormy; 11-26-08 at 22:44.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Oct 2003
    Posts
    103
    I have to run at the moment, I'll read your reply in further depth later.

    I am actually using ADP project, will convert to ADE later.
    I use the following:
    Public Const MAIN_CONN_STR As String = _
    "PROVIDER=SQLOLEDB.1;" _
    & "Persist Security Info=False;" _
    & "Data Source=" & DB_SERVER & ";" _
    & "User ID=" & DBUSER & ";" _
    & "Integrated Security=SSPI;" _
    & "Initial Catalog=" & DB_NAME & ";" _
    & "Data Provider=SQLOLEDB.1"

    Public Const SBOX_CONN_STR As String = _
    "PROVIDER=SQLOLEDB.1;" _
    & "Persist Security Info=False;" _
    & "Data Source=" & DB_SERVER & ";" _
    & "User ID=" & DBUSER & ";" _
    & "Integrated Security=SSPI;" _
    & "Initial Catalog=" & SBDB_NAME & ";" _
    & "Data Provider=SQLOLEDB.1"

    and use a select case for command (as a startup option to determine which I will use) with a connection as follows:
    CurrentProject.OpenConnection MAIN_CONN_STR

    This allows me to have the users startup say with "/cmd Sandbox" to go into a sandbox version - say for training or whatever where they won't be messing with live data.

    So are these "linked" tables - by my way of understanding yes, but you may know better than I.

    If I try to go into design mode for a table or query I get:
    "This version of Microsoft Access does not support design changes with the version of Microsoft SQL Server you are connected to . . ." It refers me to check for updates. I understood this to be the case because 2005 had features or something beyond what Access 2003 can work with.
    I

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    They are not really linked tables for an ADP! For example, in an ADP if you delete the table, you're REALLY deleting it from SQL Server!!!

    ADP's are a little more difficult to work with versus an mdb. I found that I could design mdb's (with linked tables) and get just as good as performance (without all the other extra coding) versus an ADP. Just something to consider as an ADP probably takes 3-5 times longer to develop versus an mdb.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Ah....yes. There are some versions of MSAccess which don't work for ADP's with SQL Server 2005. You may have to either re-consider designing it into an mdb or upgrade your MSAccess version (which I wouldn't recommend.) (or downsize to SQL Server 2000.)

    Again, you may want to consider just an mdb. Trust me - I designed several ADP's and they can be a headache to design and there's not much benefit in an ADP over an mdb (actually the opposite - there's more benefit with an mdb than an ADP.)
    Last edited by pkstormy; 11-27-08 at 02:32.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Oct 2003
    Posts
    103
    Quote Originally Posted by pkstormy
    Ah....yes. There are some versions of MSAccess which don't work for ADP's with SQL Server 2005. You may have to either re-consider designing it into an mdb or upgrade your MSAccess version (which I wouldn't recommend.) (or downsize to SQL Server 2000.)

    Again, you may want to consider just an mdb. Trust me - I designed several ADP's and they can be a headache to design and there's not much benefit in an ADP over an mdb (actually the opposite - there's more benefit with an mdb than an ADP.)
    Really? In what way? The database I am replacing is one I wrote a few years ago - of course I have learned a few things since then, but it is very slow. Admittedly, it has an MDB back end as well, but I was also told that SQL backend with Jet frontend might still have performance issues. So, I started down the ADP road.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    are you using unbound forms? if not that may be a cause of the problems
    are you using pass through queries for SQL server data?
    have you designed your applciation using best practice from one of ther quality books on Access development (such as Access Enterprise Development)
    have you made certain your indexes are appropriate

    if you can't update queries could it be that you don't have permission to modify tables

    Im not a fan of using linked tables, as in many ways they perpetuate the perfomance problems of JET.... some may argue bound controls, linked tabels give you the worst of both worlds, certainly few of the advantages of using a server back end
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ADP is now deprecated - don't start a new ADP development.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Oct 2003
    Posts
    103
    Quote Originally Posted by healdem
    are you using unbound forms? if not that may be a cause of the problems
    are you using pass through queries for SQL server data?
    have you designed your applciation using best practice from one of ther quality books on Access development (such as Access Enterprise Development)
    have you made certain your indexes are appropriate

    if you can't update queries could it be that you don't have permission to modify tables

    Im not a fan of using linked tables, as in many ways they perpetuate the perfomance problems of JET.... some may argue bound controls, linked tabels give you the worst of both worlds, certainly few of the advantages of using a server back end
    Well, in the "old" version of the database. I made most of these mistakes including that the back end was not sql server but mdb. In the "new" version I am writing, I am planning/trying to take all of this into account, and not make any of those mistakes.

    Quote Originally Posted by pootle flump
    ADP is now deprecated - don't start a new ADP development.
    I am getting that message loud and clear from you guys. It's funny, I thought it was supposed to be the "superior" of the two MDB vs ADP.

    So if I give up on ADP, that means I have to give up on using ADO record sets? That appears to be the case because in an old mdb I was trying to use ADO record sets, and it would not work, but DAO record sets would?

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jdostie
    So if I give up on ADP, that means I have to give up on using ADO record sets? That appears to be the case because in an old mdb I was trying to use ADO record sets, and it would not work, but DAO record sets would?
    Nope - defo not. What do you mean by "do not work"?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    go DAO
    we'll outlive the ba$7@rds yet

    izy
    currently using SS 2008R2

  13. #13
    Join Date
    Oct 2003
    Posts
    103
    Quote Originally Posted by pootle flump
    Nope - defo not. What do you mean by "do not work"?
    I think, looking back that maybe I didn't have a library loaded.

    Here is a sample code:
    Private Sub Command0_Click()
    Dim rs As ADODB.Recordset
    Dim query As String

    query = "select * from tblfsrlog"
    rs.Open query, CurrentProject.Connection

    rs.Find ("[FSRNumber]=5339")
    If rs.EOF Then
    MsgBox "5339 NOT FOUND"
    Else
    MsgBox "FSR 5339 has an index of " & rs.Fields!FSRUID
    End If
    rs.Close
    End Sub

    Gives me:
    run-time error '91':

    Object variable or With block variable not set
    on rs.Open query, CurrentProject.Connection

  14. #14
    Join Date
    Oct 2003
    Posts
    103
    Wierd, I tried adding the ActiveX 2.1 library, same problem.

    I use almost identical syntax (different tables exist) in the ADP project I had started.

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I am so lucky on this... I dodged ADP completely. I intended to get into it when needed, but I never saw the need for it, so I'm quite happy for ADP to be deprecated
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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