Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2007
    Posts
    6

    Unanswered: Access front-end linking to sql server backend

    Hi, l 'm wondering if anybody can help, l 'm a sql server server person and am currently working on a project to migrate the access database to sql server and to create an access front-End. l have already done the data migration, created a link between access and sql server and also created a pass-through query in access linking up to sql server.

    l need help in creating a Front-End form in MSAccess for users to access the data as before,this is the last part.any help will be greatly appreciated.

    Thanks.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The easiest way consists in creating "attached" (or "linked") tables. If the tables have an identity column (almost an obligation for a SQL Server), they will be treated like any other (internal) tables by Access. You can create an attached table on any table or view existing on the server.

    However, working that way, you cannot benefit from the full power of the server. Access is not aware of how the server handles the data and what it can do with it. It can also greatly increase the network traffic and slown down the front-end.

    As an example, suppose that you have a date/time column (smalldatetime) in a table and you want to add a computed column to hold the corresponding week number. If this computed column is handled by the server, you'll receive a warning message in Access every time you change the value of the date/time column, saying that you and someone else tried to modify the current row simultaneously. Access does not "know" that the week column is a column computed by the server (it will consider it as a read-only column, though). Many problems similar to this one can occur.

    Using pass-trough queries seems to be the right thing to do, however you must be aware that these are read-only and that you'll have to provide a way to write-back the modified data as well as delete or create records.

    In the attached file, you'll find two VBA classes that work together to perform the 4 DB operations (SELECT, INSERT, DELETE, UPDATE). The error handling is very specific and you'll probably have to modify it.
    Attached Files Attached Files
    Have a nice day!

  3. #3
    Join Date
    Jul 2006
    Posts
    30
    TEE BOY777 ... I need help with the SQL Backend and have many years experience with Access front end. Would you be interested in working together on a test project? I have a SharePoint 2010 site that we can use to trade databases and info. Jim

  4. #4
    Join Date
    Feb 2007
    Posts
    6

    Re:

    Hi jimgriggs, l would be happy to help in anyway l can,

  5. #5
    Join Date
    Feb 2007
    Posts
    6
    Hi Sinndho , thanks for the advice and script. l will keep you posted on the progress.

    teeboy_777

  6. #6
    Join Date
    Jul 2006
    Posts
    30
    TEE BOY777 .... I have an Access front end to be linked to a SQL backend on a hosted SQL server or on a local SQL server. I have converted the Access tables to SQL several times but cannot link the SQL tables to the Access front end. If you send me what is required in your form, I will set up a sample form with queries to drive it. Send me an email to jim@litserv.com, I will give you access to the Sharepoint site for this project.

  7. #7
    Join Date
    Feb 2007
    Posts
    6
    Hi jimgriggs, l'm sorry for being off the radar for the past couple of weeks, l've not been around,what version of sql server are you running,what steps are u taking in linking your access tables to sql server?, you need to create a ODBC Data Source Name (DSN) before you can create a link between access front-end and sql server tables,

  8. #8
    Join Date
    Jul 2006
    Posts
    30
    Local and hosted SQL servers are 2008. I can connect to the local but not the hosted. They have a connection script but I do not know how to set it up. I created a ODBC connection to my local SQL server 2008 R2.


    Below are the connection strings from the hosted site. The ODBC setup on my Win 7 system does not (or at least I do not see it) a selection to set up a connection to a hosted SQL db. I have never set up a "connection string" so any detailed help you can provide will be greatly appreaciated.

    This is a sample connection string which you can use with MS SQL 2005 / 2008 under ASP.NET:

    <connectionStrings>
    <add name="connStr" providerName="System.Data.SqlClient" connectionString="Data Source=YYYY;Initial Catalog=XXXX;User ID=XXXXX;Password=XXXXX;" />
    </connectionStrings>

    OR

    MS SQL - SAMPLE CONNECTION STRING (ASP) This is a sample connection string which you can use with MS SQL 2005 / 2008 under ASP:

    <%
    dim conn
    dim rs
    strConn = "Driver={SQL Server};Server=YYYY;Database=XXXX;uid=XXXX;pwd=XXX X"
    Set cnt = Server.CreateObject("ADODB.Connection")
    cnt.ConnectionString= strConn
    cnt.Open
    %>

    Database server = YYYY - You need to use the server address listed in "Database server" field when you were creating your database in Plesk.
    For security reasons, we have changed specific settings with XXXX
    NOTE: You MUST include the full database name, which consit of the user prefix and the name that you have given, for example XXX_DB

Posting Permissions

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