Deployment of MS access front and SQL Server back-end has been never been an easy task and there are no clear articles out there on how to copy the SQL Server back end and the MS Access front end together on flash storage, CD, or even emailing the same. Due to a lot of difficulties I went through when trying to transfer the said files from one computer to the other, I ended up discovering an easy method of accomplishing the same, I have dedicated this article to beginners who will carry forward our legacy when our time to retire comes.
It is believed as though the SQL Server back-end can never be copied except through indirect backup and then restoration. I say forget about that story, the SQL Server back-end can actually be copied like any files of any of the devices we have around as long as you follow a few steps listed below. The copying of files and pasting will continue like in the past with SQL the Server back-end without exception.
How is it done?
(1) First, let us acknowledge that to copy a SQL Server file, we need to ensure that the target file is closed otherwise it will not work out
(2) Login normally to the SQL Server Management Studio
(3) On the Explorer function, expand the database note to see all the databases
(4) Right click on the target database and then select Task, thereafter select Detach, remember above I said you cannot copy a SQL Server database which is still open. After detaching the database it will surely disappear from the list but do not fear, all is well. Actually, that is what we wanted to do.
(5) Log out of your SQL Server Management Studio safely
(6) Remember where you installed your SQL Server if you did not change the location, then your files must be sited right now in the following path, if you changed the location then locate it. Here is a full default path: C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA
(7) Please ensure that you have Administrator's rights, these are sensitive areas you are entering so without the proper rights you will be blocked
(8) After opening the last directory, in this case, DATA, assuming your detached database is called "accounts", you will then see two files like the following ( accounts.Mdf& accounts.Ldf). The two files you have seen in brackets are the one to copy on any of your devices and they can be either zipped or emailed to any destination.
Installing the copied files
(1) Now on the target computer where you installed the SQL Server 2016, follow this path C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA, in the data directory, paste those two files you copied
(2) After that, we need to login into the SQL Server Management Studio again like above
(3) Go to the Explorer and right click the databases folder and select Attach
(4) There will be a white box that will come, simply click on add, then all the databases will appear on the list, you will also see the database with the name "accounts", then select it and press ok, the final white box will appear with file copied above (accounts.Mdf & accounts.Ldf), then simply press Ok again, now you will see your "accounts" database appearing in the list of database, depending to FE links it will still work normally.
(5) That is it, it is done