Page 1 of 3 123 LastLast
Results 1 to 15 of 43
  1. #1
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049

    Unanswered: Is it possible to use an SQL Server backend and NOT have any linked tables?

    Hi all

    I have a customer who wants this specific requirement. As I have not done this before I thought I'd just put the question forward.

    Is it possible to have an MSAccess front end with an SQL Server back-end but not have any table links in the database window? I'm assuming this is a security related requirement.

    And of course, if so, how?

    I'd like to know before I stumble forward and just say yes :P

    Cheers

    ST
    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

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes. I do this sometimes. You basically open a connection to the SQL Server table, supplying the servername, user, and password.

    Here's an example I use (for a DSN-Less connection - no ODBC):

    Dim aCnn As ADODB.Connection, Cn As String
    Cn = "Driver={SQL Server};Server=MySQLServerName;Database=SQLServerD BName;UID=UserLoginToUse;PWD=SomePasswordToUseForU serLogin"
    'Note: make sure UserLoginToUse is in SQL Server security for the db and that user login is set up to use a specific password.
    Set aCnn = New ADODB.Connection
    aCnn.CursorLocation = adUseClient
    aCnn.Open Cn
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open "Select * From MySQLTable", aCnn, adOpenDynamic, adLockOptimistic, adCmdText
    rs.addnew
    rs!SomeField = somestring
    rs.update
    rs.Close
    Set rs = Nothing
    aCnn.Close
    Set aCnn = Nothing

    Only thing is, without linking the SQL Server tables into the mdb, you lose the power of creating queries and will need to write a lot of code in leui of the queries and to do things like populate the form with data, populate comboboxes, save data, etc.. Without linked tables, all your data returns/saves must be done via code. You might want to relate that it will most likely take you 2-3 times longer or more verses using linked tables.

    You can also use an ODBC Connection string (one you've already created to the SQL Server db) instead of using the SQL Server driver (i.e. DSN-Less connection) as in the example above (and utilize Windows Authentication verses SQL Server authentication and don't need to supply a uid/pwd.)

    You can also create a stored procedure in SQL Server and call the stored procedure in the mdb.

    Also keep in mind that the user login and password are hardcoded as in the example above. You'll want to most likely make this an mde to protect from users getting into the code and seeing the password.

    ADP's are also something to consider if you're taking a non-linked tables route.

    Personally though, I would encourage using the linked tables and making the front-end locked down so to speak so users can't get behind the scenes.

    For security what I have is (using linked tables):
    1. Users need to have access to the server/folder the mde resides on
    2. I create a "permissions" table (with a security level integer field) in the SQL Server db and control users who can get into the frontend only if their user loginID is in the permissions table and if they are, allow certain abilities depending on their security level. Then I let the admin users control who gets added to the permissions table and what security level.
    3. Establish Users/Roles on SQL Server to make sure only the specific user loginID's can get into the db and SQL Server role permissions on what tables they can update/not update.
    4. Never let the user get behind the scenes by locking down the interface (and create an mde)
    5. If need be, also establish MSAccess security to the mde.

    I also have code to automatically create the ODBC DSN on the user's computer when they first get into the mde for linked tables.

    I prefer using linked tables with the security layers above. It's a lot easier being able to utilize the power of queries and not always having to write code for everything. Again, you may also want to consider an ADP verses an mdb. It's definately going to mean a fair amount of work on your end though if you're not going to utilize linked tables.
    Last edited by pkstormy; 01-14-08 at 03:26.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ah I see, thanks muchly
    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

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This lovely thing called recordsets
    I never use linked tables anymore!

    Personally, I don't use ODBC connections either; I know that my method isn't fantastic, but I hard code my connection string in the application and then compile it as an MDE...

    Obviously, as soon as the server/database changes I have to recompile the appp; but I find this to be the most secure way anyway.

    Oh and permissions / logons to the SQL Server instance are definately the key!
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2007
    Posts
    26
    pkstormy.
    Can the database connection information be made into a CALL function i.e

    Public Function databaseconnection()

    Dim aCnn As ADODB.Connection, Cn As String
    Cn = "Driver={SQL Server};Server=MySQLServerName;Database=SQLServerD BName;UID=UserLoginToUse;PWD=SomePasswordToUseForU serLogin"
    'Note: make sure UserLoginToUse is in SQL Server security for the db and that user login is set up to use a specific password.
    Set aCnn = New ADODB.Connection
    aCnn.CursorLocation = adUseClient
    aCnn.Open Cn
    Dim rs As ADODB.Recordset

    End Function

    So instead of having the above information written many times through out the database I could just call the connection i.e.

    Call databaseconnection()
    ‘then the recordset information etc
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open "Select * From MySQLTable", aCnn, adOpenDynamic, adLockOptimistic, adCmdText
    rs.addnew
    rs!SomeField = somestring
    rs.update
    rs.Close


    Set rs = Nothing
    aCnn.Close
    Set aCnn = Nothing

    your input would be appreciated

    casey

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hey casey, I must apologise for missing your previous thread!
    I have posted a reply there now; let me know how you get on with it :0

    http://www.dbforums.com/showthread.php?t=1626013
    George
    Home | Blog

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    I never use linked tables anymore!
    GO georgev!

    linked tables are cute with Jet-based BE on the local disk.
    for SQL-aware servers, my view is that linking just makes everything heavy and slow without bringing anything worthwhile to the party.

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    My process is to first create the tables in MSAccess (so I can easily make mods to any tables.) Once I've completely finished with the designing of the tables, I use the upsizing wizard to upsize the tables to SQL Server and then I create an ODBC Connection and link in the tables. After that and depending on the project's timeline, I will modify the app for not using linked tables. I'd like to disregard linked tables all together but a lot of my apps require a short deadline where I just don't have the time to write the code for unbound forms, etc.. (for the smaller apps - on the large recordset apps I take a different route.) I've never really had problems using SQL Server linked tables even though I know ideally it'd be nice not to use them (still better than using MSAccess tables though.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Btw Paul, belated congrats on the 2K
    George
    Home | Blog

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Thanks George. I didn't even notice. Guess I'll need to have a beer or two tonight.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by pkstormy
    I also have code to automatically create the ODBC DSN on the user's computer when they first get into the mde for linked tables.

    I prefer using linked tables with the security layers above. It's a lot easier being able to utilize the power of queries and not always having to write code for everything.
    Ok, since I have a shorter time frame that I thought I'd have, I'm going for what you said; using linked tables and secure by using an MDE etc. I need my queries!! :P

    Once I get that all working right, which is close now, I'm then consider removing the links again, but for now, I just want the thing up and running so my customer is smiling

    I'm interested in the code you said you had for creating the DSN if it's not there on client machines. Can you share that please... pretty please!?!

    Cheers!

    Quote Originally Posted by georgev
    This lovely thing called recordsets
    I never use linked tables anymore!

    Personally, I don't use ODBC connections either; I know that my method isn't fantastic, but I hard code my connection string in the application and then compile it as an MDE...

    Obviously, as soon as the server/database changes I have to recompile the appp; but I find this to be the most secure way anyway.
    I have no problems re-compiling if server settings change, but what do you mean about recordsets here? Are you saying you have a quicker way of having no linked tables (DSN-less) and also not have to code absolutely every single database access component?

    Got an example I can check out?

    Thanks guys for your input on this so far... muchly appreciated!

    ST
    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

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I will use (or more accurately have used - I don't use Access any more) linked tables for tight deadline or tiddly apps. Killer apps with a decent projected time scale are not linked.

    As far as queries being a problem - no way. If you go unbound (try googling "Access unbound forms" for more info on this stuff - I know you asked for resources) then get as much processing as you can on the server (sprocs, views, functions etc). All you are doing is moving your queries from Access to SQL Server objects. Apart from linguistic changes, and the fact you have more available to you which you will want to use, there should be no real time cost. You will hoever get a major benefit as ALL data processing will be on the server.

    I find the real time cost is for writing data via forms however once you have done a few you should have template code for navigating recordsets (be they real or pseudo), populating forms, saving data etc.. I also use naming conventions so form population\ saving is automated and iterative rather than coding each individual control.

    Unbound stuff like this far more closely mirrors how other FE apps interact with data sources so the knowledge you pick up is applicable to other environments. You also decouple your DB and FE which is a Good Thing.

    Considerations -
    Reporting: Reports need an Access object as a record source (NOT a reocrdset) so pass through queries to sprocs are, AFAIK, the best means available to you (though not perfect - SQL Injectable).
    Concurrency: There are no locks on records whilst they are being viewed as there are with linked tables so you need to roll your own. Izy had a good discussion on this years ago.

    Ah poop - Just realised - I assumed the BE is not JET . If it is JET then I wouldn't bother with unbound forms personally. But then any JET system for me falls into the tiddly app category.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pkstormy
    I also have code to automatically create the ODBC DSN on the user's computer when they first get into the mde for linked tables.
    Paul - this interests me. Why do you do this?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Thanks Poots

    Since you don't use Access anymore, may I ask what you use now?

    And your assumption is correct, the BE is SQL Server.

    Is there any chance of kickstarting me off with a head start? Can you post a tiny FE with some good examples of what you're explaining here? I realise such an application wouldn't actually work, but the code would lead me in the right direction

    I understand the meaning of what you are saying... I've developed unbound forms before with saving and navigation etc, and I understand what you mean about moving all queries to SQL Server.

    The "roll your own" concept of record locking is something quite new to me though... I'd just like to have the ability to get off on the right foot. I wish I was here years ago, to catch the topic you mentioned! Would that topic still be discoverable here? Any clues on what to search for?

    Truly appreciate it
    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

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    SQL Server BE, ASP.NET FE.
    I've fancied popping something on code bank but never got chance. I doubt I'll get chance to clean up an example (left my templates at the last gig - knew I would not need them).

    If you've dealt with unbound forms before then you are 90% there.

    I really don't know. I'll see if I can dig that one up. The gist is you need to either row version your data or compare the values of the data at the time of retrieval with the values of the data at the time of saving and handle it if they differ.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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