Results 1 to 11 of 11
  1. #1
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370

    SQL Server, ODBC, DSN, Access

    Hello all,



    I'm currently operating/developing in a split access database. The front end is on multiple machines and the back end is on the network.

    I'm looking into stepping up the back end to SQL Server.

    I was able to do all the correct things when setting it up and its working great. However, there were several steps I went through that I don't know why, and I would would greatly appreciate it if someone could explain.

    I'll simply list my steps for linking up to an already created blank database on a SQL Server(no tables, no data), and I'll ask questions regarding the step number.

    Steps:
    1) Create a file DSN.
    2) Export tables from Access back end to the DSN.
    3) Set PKs and FKs in SQL Server, set relationships in SQL Server
    4) Add timestamp field to every table in SQL Server
    5) Import ODBC database, select File DSN created in Step(1).
    6) Compact and repair

    Questions:
    A) In step 1, what exactly is the DSN, I know it stands for Data Source Name. I know there are 3 different types, User DSN , System DSN, and File DSN. I'm told from online sources to create a File DSN for the purpose of linking an access frontend to a SQL Server Backend. Also, If several computers will be using this Access front end, is there anywhere specific I need to store the File DSN? Do I keep it in a network folder? Or can I simply delete it after creating all the links?
    B) In step 3, is there anyway to carry my relationships and PKs and FKs from my Access back end when exporting the tables to SQL Server?
    C) In step 4, This was a solution I came to after several hours of trying to figure out why I couldn't edit data in my tables. If I was to edit a record, go to a different record, and go back to the original record I was editing, I could no longer edit that record. It would say that an edit is still taking place. After doing research on it, I found that adding a timestamp would allow SQL Server to track when the record was last edited. I'm still not clear on why I did this, however, I'm not getting the error I was originally getting and it works fine now. I would really like an explanation of this though...

    So I've figured out the HOW to move my Access back end to SQL Server, now I would like to know the WHY component.

    Thank you

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    It has been a very long time since I have created a DSN. IIRC, all you need to do is install a System DSN on the server. This tutorial seems solid
    http://hodentekmsss.blogspot.com/201...sn-to-sql.html

    The trick is using the correct driver. Perhaps the best approach is to use Native Client 11. You would need to install this client on each of the Client Machines. Check out this thread ...
    https://www.accessforums.net/sql-ser...ere-52914.html

  3. #3
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    Yes I know ItsMe, this is exactly how I created mine.

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Regarding A, I create a file DSN, stored on a hidden share on the SQL Server (hidden just to prevent "oops" moments). You can't delete it when done, it is how the front end communicates with the back end. I guess you can think of it as a translator, letting Access and SQL Server talk to each other.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    Quote Originally Posted by pbaldy View Post
    stored on a hidden share on the SQL Server (hidden just to prevent "oops" moments).
    "On the SQL Server"

    Are you talking about:

    C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by cbende2 View Post
    Yes I know ItsMe, this is exactly how I created mine.
    OK, just trying to help out where I can. I thought you were trying to create file DSN's. It has been a while for me and it is not in my best interest to research this stuff to come up with good and concise answers to your specific questions. I use the driver I linked to and connection strings. The DBA hands me a connection string and I use that. Makes my life easy.

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Could be there I suppose, but I create a folder on the server:

    D:\FileDSN
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    Quote Originally Posted by ItsMe View Post
    OK, just trying to help out where I can. I thought you were trying to create file DSN's. It has been a while for me and it is not in my best interest to research this stuff to come up with good and concise answers to your specific questions. I use the driver I linked to and connection strings. The DBA hands me a connection string and I use that. Makes my life easy.
    I understand, I din't mean to be harsh

    I got thrown into a DBA position, granted... It's only a small DB, however we are looking to be moving to production on SQL Server pretty soon.

  9. #9
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    Quote Originally Posted by pbaldy View Post
    Could be there I suppose, but I create a folder on the server:

    D:\FileDSN
    I see, I think this answers some questions about the DSN. And as I was researching more, I came across a great explanation on why to add timestamp fields here: http://stackoverflow.com/questions/1...write-conflict

    Still waiting to see if all the relationships have to be re-created in the SQL Server after exporting tables from my Access back end.

  10. #10
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If you just exported tables, relationships were not likely created. Long ago I used the Access upsizing wizard, and I recall it adding referential integrity for me. I've heard the SQL Server Migration Assistant is good, but have not used it myself. Not sure if it handles the relationships, but I'd guess it might. They don't "need" to be set up if that's your question, any more than they do in Access.

    Like you, I was just thrown into a dba-like position, so there are large gaps in my knowledge.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    Gotcha, Ok, thanks guys

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

Similar Threads

  1. Replies: 1
    Last Post: 11-15-2014, 12:41 PM
  2. Replies: 7
    Last Post: 11-03-2014, 03:32 AM
  3. Access security ODBC to SQL Server
    By Mark@CHP in forum Security
    Replies: 3
    Last Post: 03-27-2012, 08:31 AM
  4. Replies: 0
    Last Post: 03-08-2012, 03:12 AM
  5. ODBC link from Access to SQL Server changes to read-only
    By Petr Danes in forum Import/Export Data
    Replies: 1
    Last Post: 06-08-2011, 09:46 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