Access to SQL conversion ?

Hi,
I have an application developed in MS access. It consist of forms, report, queries and tables. I now want to deploy this application for use by 2-3 people, and at the same time want to move to MS Sql Free edition.
My idea is as follows ;
1.      Split access to two parts, frontend and backend database
2.      Convert backend to MS SQL
3.      Host MS SQL on my desktop, and let my employees to connect to this database from the front end. This will save cost of one additional server (I already have router in office so I guess this connectivity should not be an issue).

Are there any guidelines on how this should be done ? Is it fairly easy to do this ? Do you see any problems on uasing MS SQL free edition ?
LVL 1
Vaibhavjoshi2005Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
It should be pretty easy to do.
First, you will have to export all tables to the external database (MS SQL Free in your case, but it could be anything, even another MS Access database)
Then you will have to check if there are any table relationship. If there are, you need to replicate the same in the external database.
Now, you need to link all the exported tables back to your front end database making sure that the table names are the same as they were. I recommend you do this. Say for example you have a table called tblCustomer. First, rename it in your front-end database to tblCustomer1. Create a link to tblCustomer from the external database. The table will be automatically named by Access, and can be dbo_tblCustomer. Rename it to tblCustomer. Check the form and queries that use this table to make sure they still work. tblCustomer1 can then be deleted. Do this for all other tables.

Having said all that, I believe you can get away with your current database without implementing a central database. You can just put your database on a shared drive and open it by multiple users at the same time. Access supports this.
0
Vaibhavjoshi2005Author Commented:
Yes I know. However one concern is about the security. I dont want my employee to copy both files and take it outside. This will mean he / she has access to the data as well as the forms and reports in the database. The database includes some unique reports which are valuable for my profession. So the idea of moving to SQL server was for security.

Alternatively is publishing access on sharepoint a better and easier option ?
0
chaauCommented:
When the copy the file, they will take the reports with them. One way of protecting against this is to make an MDE file (ACCDE file in Access 2007 onwards). This is a compiled version of the file that users can't de-compile
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Vaibhavjoshi2005Author Commented:
Is there anyway to secure database file also ? Somebody can copy it and get access to all my data.
0
chaauCommented:
Yes, there are ways to secure the data in your database. Please read this comprehensive article by Microsoft
0
Gustav BrockCIOCommented:
I would advice against running the SQL Server on your local machine. It will do fine, but your machine has to be turned on always. Also, it is so much nicer to have a dedicated machine for the purpose. Just about any old pc of decent quality will do - you probably have on piled up in a stock room. And you wouldn't need to run the lastest version of SQL Server - 2008 or even 2005 will be alright and these older versions run fine on older Windows versions.

/gustav
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I think you're on the right path (with gustav's advice to run the MS SQL Server on a separate machine). Upsizing to SQL Server is generally a good idea, and it can definitely enhance your data security.

MSFT has a tool to help you with upsizing: AssistantSQL Server Migration Assistant. This points to the SSMA blog, and you can locate the correct download from there. SSMA does a pretty good job of moving your data over to the big brother.

You may also find that you have some cleanup to do after migrating, depending on your coding style. In some cases the database will port over fine with no changes, but in many cases you'll have to make some changes. For example, if you've used Access-specific syntax in your queries you'll have to change them. An example would be:

SELECT * FROM MyTable WHERE MyField=Forms!MyForm!MyControl

If you migrate your queries to Views, you'd have to change that.

Here are some resources which may help:

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp (Best of Both Worlds)
http://www.fmsinc.com/tpapers/index.html#SQLPapers

Other caveats:

-- Be sure you have a Primary Key identified on all tables
-- Be sure your table and column names conform to SQL conventions (no spaces, for example).
-- It's generally a good idea to add a TimeStamp column to your tables.
-- Yes/No fields in Access will become Bit fields in SQL Server. Be sure you've got a Default set on those Bit fields.

Oh, and Sharepoint is not the better option. You'll be much more pleased with the results if you stick to the methods you've already outlined.
0
Vaibhavjoshi2005Author Commented:
Hi,

Just a question. If I separate queries , forms etc in access file and connect it to SQL server database, I dont need to change any coding right ?
0
Gustav BrockCIOCommented:
Very little. However, if you use OpenRecordset you often need to add the parameter dbSeeChanges. No big deal. Look up the on-line help on this.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.