Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2006

    Question Unanswered: Access linked to SQL

    I have an Access 2003 program linked to Access tables via the linked table manager, which is 8 years old and has been modified every year. Because of the need to process larger amounts of data, I have converted the tables to SQL server 2005. I have introductory knowledge of SQL, so my questions are these: What is the advantage of SQL tables over Access tables? How should I query the information, Access or SQL? What do I need to do in order to take advantage of SQL in my Access front side? Should my reports be generated in Access, from Access queries, or from SQL views and procedures? Am I going to have to use Crystal reports in order to take advantage of SQL? Is it clear I am confused? Thanks for any help.

  2. #2
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 11
    lot of question.

    SQL server 2005 is bigger ,faster ,stronger so good move

    Querys or Views

    I would look @ the Querys or Views If they are doing a lot of Work put them
    on the "SQL server 2005" But Msaccess will see then as tables
    Also if you don't want the User to change delete then thenmake them views

    SQL in my Access front side?
    just and ODBC connection

    Should my reports be generated in Access
    yes SQL can't do that

    from SQL views and procedures?
    Use the views as Querys
    me i'm still getting my head around "procedures"

    Think 'SQL server 2005' as the BONES
    and MS accessas the Skin
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

  3. #3
    Join Date
    Dec 2004
    Madison, WI

    MSAccess and SQL Server

    If you are linking the tables into your Access application, you have a great combination where you can use things like triggers, stored procedures (like queries), dts, etc on your data via the SQL Server side and also run your typical queries on the Access side. SQL Server also has a much better lock preventing capabilities, better backup routine, ability to backup your data via tran logs on the hour, 4 hours, etc.. and is a very easy to use, access style database structure type. There is one thing you can do which will greatly enhance your application which is write your forms unbound and use techniques to write, read, update, and delete to and from the from via vba code. That way if someone pulls up a name in one of your forms (and then leaves for lunch), that record isn't locked into the the SQL Server table (college teaching routines). Your reports are easily generated just as if you they were Access tables but with a better management of several users. There are also a few neat tricks out there to take advantage of. For example, the below routine, clones the mde file and launches a new mde with the users loginID attatched. They are still connected to the same SQL Server linked tables but now I can see when they get into it (i.e. there's an ldb file present) and also copy new code changes to the "central" mde file without having to have everyone exit. In regards to crystal reports, I personally would stay far, far, far away from it. MSAccess reports are much better and more powerful with easy integration into Word, excel, etc. products. I've never found anything I didn't want to do with them.

    Note: if you want to use the vbs in the attachment, edit it with notepad, change the location of your mdb or mde file in the location line (preferably UNC), save it as a .vbs extension, and then put it on everyone's desktop (You can change the icon).
    Attached Files Attached Files
    Last edited by pkstormy; 10-15-06 at 15:47.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Feb 2004
    One Flump in One Place
    I cannot imagine an instance when I would use JET (read Access) as the RDBMS over SQL Server 2005 if I am given the choice and someone else is paying the licence costs. How much benefit you will gain however will depend on your app and your use of SQL Server.

    Consider using ADPs.
    Investigate fully disconnected access (i.e. no linked tables) - it is a very steep learning curve but results in better performance and will teach you a heck of a lot about SQL Server fast.
    In general, the more you can pass back to SQL Server the more benefit you will gain and the more you will learn. Even if you just stick your data on SQL Server, link to it and continue as before you will gain some benefit as Access will pass as much of its query processing to SQL Server as it can. But that just isn't as much fun

    Oh yeah - and you don't need to use Crystal - if it wasn't for Access's great reports I would have stopped using it years ago.

    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    One Flump in One Place
    Hi daystarmike

    Responding to your PM (more info re disconnected data access). The principal of a disconnected database is you open a connection, download the data and close the connection again. You then display, (optionally) edit, delete, add to the data at the client, open a connection and marshal the changes back to the server. This model is exactly how ADO.NET works.

    To create a disconnected Access database you need to learn ADO.
    ADO Tutorial:
    Note that ADO was not origianally designed to act as a disconnected client so you have to do a litle more work than you do in ADO.NET.

    Disconnected methods of list filling:

    Generally the principles used in are used in disconnected access databases so try looking at asp samples (not the code sytax does differ a little though).

    You will also want to investigate views and stored procedures in SQL Server. Personally all my data retrieval and manipulation is done via stored procedures (sometime known as a database API). Others will disagree and use inline SQL or update via recordsets. YMMV on this.

    The most important thing is to use parameters, not just build up SQL strings, if you want your application to be secure (i.e. prevents SQL Injection).

    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