Results 1 to 5 of 5
  1. #1
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74

    How to do : MSACCESS - SQL Table link

    Good afternoon everyone.
    I would like to underline that i never used SQL, i hear something about it and i was always interested to use as back-end database but for some reason i never went deep to understand what and how to do.
    My problem: i have an MS ACCESS application where the database is split in BE and FE. BE Database is actually located in a company server folder and you can understand how much is slow especially if working from home.
    I hear that using SQL as BE source and link the table in FE trough SQL should increase a lot the performance. The problem is that i don't have idea on:
    1. What i need;
    2. What to install;
    3. How to move my ms access BE tables to eventually SQL;
    4. If one day i will be able to cover the 3 previous points, how to link the SQL table to the FE database for the final user.

    Just some information: i'm working in a company where any required installation (if not present) need authorization.

    Hope in this fantastic group to get some tips how to do because the way we are working now it is ok but performance in term of speed very very low.


    Thank you for usual and kindly support.
    L.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    In windows open ODBC app,
    create a new DSN ,
    pick the sql server driver,
    enter the sql server path,
    click thru defaults until it asks for the database, pick the database,
    save.

    then in access, click external data source,link,
    select the DSN you made,
    select the table you need,

    done

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Be aware that moving the BE to SQL server involves a lot of work and it will not necessarily be any faster, actually it can be much slower if the front-end is not redesigned properly, here are some links:
    https://www.fmsinc.com/microsoftacce.../how/index.htm
    https://www.opengatesw.net/support/U...s-Database.htm
    https://support.microsoft.com/en-gb/...7-e6e759d72924

    Usually in your situation to allow users to access the work db from home you would use some sort of remote access (via the Windows remote connection, terminal services or a Citrix product) to a work server where each user would have access to their own front-end (located in their user profile subfolder on that server machine).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74
    Thank you for answers, i suppose it is not something easy especially for people like me who doesn't have experience on it. Also, i need to consider that even to have ODBC application, i need to have authorization so not really easy honestly, even if with good justification i may have it. With regards to the connection, actually we have a company VPN which allow to connect our server drivers, problem is that is really slow regardless i tried to manage my front end at the best (which is never the best).
    Cheers.

  5. #5
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Please take care when switching to SQL server that you take some time looking how it works. I have seen to many cases where the users disk gets flooded because the database was set to full recovery mode and they didn't take regular log backups. Cnsider:
    - which version are you going to use (express, standard, Azure, ...)
    - how you're going to set up security (ALWAYS disable the dba account)
    - design a good maintenance plan (DBBC checks, back-ups, ...)
    - for you're databases: which indexes do you need (very important for SQL server)
    - ...

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

Similar Threads

  1. Replies: 16
    Last Post: 04-02-2020, 04:49 PM
  2. Replies: 2
    Last Post: 11-04-2017, 05:06 PM
  3. Learn MSAccess by playing MSAccess Jeopardy
    By pkstormy in forum Sample Databases
    Replies: 4
    Last Post: 11-17-2016, 07:27 AM
  4. Replies: 1
    Last Post: 03-15-2012, 10:45 AM
  5. Replies: 1
    Last Post: 03-25-2011, 10:00 AM

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