Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jun 2009
    Posts
    89

    Unanswered: Very stupid problem - how to link to sql server query?

    Hi. I'm new to SQL server, and am using an Access front end. I've migrated all the database, and have just started making queries using SQL server management studio.

    The queries execute fine, and i have them saved as .sql files.

    question is how do I share these so I can find them in the ODBC connection through the Access front end??

    I can't see them in stored procedures, and can see a way to add them.

    ANy help is appreciated (or if I got the wrong idea of how this works).

  2. #2
    Join Date
    May 2009
    Posts
    13
    You can make linked tables and views in Access so that Access can query them as if they weren't even in the SQL Server back end. In SQL Server Management Studio (SSMS), create a view such as the following:

    create view my_view as select column1, column2 from MigratedTable
    where column1 like '%somethingConvolutedToJustifyView%'.

    You can use the linked table manager to connect to your tables/views.

  3. #3
    Join Date
    Jun 2009
    Posts
    89
    So i can't run (from Access) just a basic SQL query stored on the SQL server?

  4. #4
    Join Date
    May 2009
    Posts
    13
    There's no way that I know of. If so, it would sure make my DBA duties easier with customers who have Access on their PCs. Then again, I haven't found many cases where you need an SQL Server back-end without the need for pre-defining and securing queries with views (and stored procedures).

  5. #5
    Join Date
    Jun 2009
    Posts
    89
    OK, so instead of having loads of queries like I do in Acces, I should just use loads of Views (for read-only stuff)?? Is that the proper way of doing things?


    What do you suggest using for editing? I was thinking of jsut opening the record up from Access but is it better if I do it through some SQL?

  6. #6
    Join Date
    May 2009
    Posts
    13
    I think the proper way of doing things is ignoring Access altogether and having SQL/MySQL/Oracle as a back end for a ASP, PHP, or ASP .net front end, but that's probably more than you've bargained for.

    Yes, when you've outgrown the database concurrency and security support of an Access database back-end, using centralized queries in the form of views and stored procedures in SQL is the way to go (views are not necessarily read-only by the way). You'll need to use SSMS to directly create/edit your SQL database at this point.

    I'm guessing the reason you went to an SQL back end is because you need more than a few folks banging away on the DB simultaneously. Is this the case? If not, you may be making things more complicated than they need to be. Is this for work or play/training?

  7. #7
    Join Date
    Jun 2009
    Posts
    89
    This is for work - there are only ever 3 users using it at the same time and the Access backend handled that fine. The issue was there was too much data and some of the forms were very slow (like to look at one it downloaded 10000 records and then filtered them to just 10). took like 10 seconds to open which is very annoying as it was the most common form.

    Reason I'm using Acces front end is becuase I have to migrate everything whilst keeping it alive. SO i'm oging to switch the backend first and leave the front end for now as it looks pretty :-)

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rudeboymcc View Post
    So i can't run (from Access) just a basic SQL query stored on the SQL server?
    Yes, you can. You can make what is called a "pass-through" query in MS Access, without linking the tables.
    In you query designer, select the SQL View, and then Pass-Through is one of the options for query type.

    But you are best off converting your Access Database into an Access Database Project (.adp file). This will be linked permanently to your sql server database, and your database objects will show up as objects in the Access object browser.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jun 2009
    Posts
    89
    wow, where/how did you find this thread??

    Anyway, I've been working on this project since (and expanded it a lot). It's now fully on the SQL server with many (very efficient ;-)) pass through queries and everything works very fast.

    I did check out Access projects at the start, and for some reason which I can't really remember I decided against it. Searching now though, it seems it's debatable which is best (DAO vs ADO), and the main advantage seems to be that i can access the stored procedures, tables and views through access (which i do perfectly fine in SQL Management Studio and would prefer to hide all the background tables from access otherwise the list of objects in access will go on forever).

    Would I see any real improvement if my longest table is 10000 rows and there are never more than 10 users at a time? It seems my current setup will be sufficient for many yeras to come, but if it will make ti faster I'm all for it!

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you use an Access Data Project, all your query processing will take place on the server. If you use linked tables, some of your query processing may occur locally, requiring that entire tables be copied across the network and possibly locking them in the process. Very bad for multi-user environments, large tables, or complex schemas.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It might be because Access Data Projects are now deprecated.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    First

    Right Church, wrong pew

    Second you need to tell us what version of Access you are usinsg


    We should probably move this thread
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jun 2009
    Posts
    89
    Access 2007.

    My own rule is if a table returns more than 200 rows than I make a paramaterised pass through query, so all processing is done on the server apart from the very light stuff which isn't an issue.

    and I also heard that projects is depreciating.

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well can't you just use stored procedures exclusively?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Jun 2009
    Posts
    89
    well I've converted everything that has even the slightest delay to a stored procedure. No real advantage to make eerything a stored procedure as it will take forever! (becuase you can't update data in a stored procedure query, guessin gyou have to use stored update queries or something?)

Posting Permissions

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