Results 1 to 4 of 4
  1. #1
    majero is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    1

    SQL SERVER ADO Calls.

    Hi,
    was wondering if soe one could give me some advice.
    We are in the process of migrating an MS Access database to SQL server and wish to consolidate some of the Access Code into SQL Stored Procedures. Im a DBA with plenty of SQL Server experience but very little on Access. From the Client server point of view (which I am Use to , it just makes more sense).
    Yes,
    I already know about the migration tool (SSMA) to do this but just making link tables only does half the job as far as I am concerned. There are more efficiencies to be gained with a High level Server doing most of the work for intensive Reports and some of the Access List Population rather than denornmalising the Datbase just to have specific tables for access population, ( which the originial designers have done to get around the Spped problesm encountered ).

    Question is:


    I dont wish to ODBC,
    but rather the more efficient SQL OLEDB through ADO calls.

    Does anybody have any Good Articles or Books that would help me with this.??? I've had enough of reading migration articles but they always seem to stop short of the mark in taking that final step forward in migration fully into SQL Server.
    I have thought about the Access Project but I believe that this should be steered away from now days as it isnt supported??( Am I missing the boat here on this one??)

    Input / discussion greatly Appreciated.

    cheers
    Mike

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    I believe what you are looking for is to move the Access queries to the sqlserver. You can put an Access query, in design view, which offers an 'sql view' option - so that gives you the pure sql statement.

    Then with minor tweaks to that statement for Tsql norms - you can move the query to the server.

    The Access query was probably serving as the 'record source' for reports or forms - - these reports/forms would need to have their record source property changed to your new sql view or record source - - if you continue to use Access as the front end.

    Not sure this helps, but if you have more Access questions feel free to contact me.

  3. #3
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    Quote Originally Posted by majero View Post
    Question is:
    I dont wish to ODBC,
    but rather the more efficient SQL OLEDB through ADO calls.
    You can form the connect string and connect to the external data source (SQL SERVER, ORACLE, SYBASE, ACCESS DB .... etc).
    Browse through this website (http://www.connectionstrings.com/) or just google what you need.

    ODBC is just make connect string simplier. Access has something called "Upsizing" will migrate data to any datasource (I have only use for SQL Server).

    Access also have something called "Pass Through" query that will just pass the query to the database and let the database do the work. It has its limitation. It also has work around if you look hard enough.

    Help this helps.

  4. #4
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    If you're talking of transforming some of the access VBA code into SQL server stored procedures and functions I wouldn't thrust any conversion tool. Just redesign them: it's a great time to rethink and cleanup the application and make it run a lot smoother.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with Form & Report that calls a Query
    By bobfin in forum Reports
    Replies: 7
    Last Post: 08-14-2010, 12:05 PM
  2. function calls in a report?
    By kvon in forum Reports
    Replies: 11
    Last Post: 04-25-2010, 08:51 AM
  3. Follow up calls, put in automatic date
    By Loish in forum Access
    Replies: 1
    Last Post: 04-08-2010, 03:59 PM
  4. ole server
    By sean in forum Access
    Replies: 0
    Last Post: 09-24-2009, 05:13 AM
  5. SQL server
    By Mark Pomeroy in forum Programming
    Replies: 1
    Last Post: 04-30-2009, 09:59 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums