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