Convert Access to SQL

Other Tech Topics
1

I hired a freelance to create an access database with forms. The work is actually still in progress. The database is basically a trimmed / hacked down version of the Microsoft Desktop Project Management Template. It allows a person to create a project, assign tasks and print / view graphs. Employee performance is what is being tracked. Employees log in and enter the number of items they have produced. Each employee has their own login and can only see / enter information for tasks assigned to them.

My fear is that this is not going to work well in a multiuser environment. There will be 2 to 4 computers where employees can login and enter production numbers at various times during the day. Most will probably enter them at lunch and end of day. I'm thinking I will need to allow multiple logins.

Can this access database with forms be converted to SQL to allow this ? We have the SQL Server.

2

You can move the data to SQL and use Access as a front end, that way the forms are still usable.

Before converting, make sure to learn what you can as Microsoft can make decisions that are not always the best.

1 Like
3

So I'm assuming that the access part of it would just need to be pointed in the direction of the data on the sql server after its moved over ?

4

The term that you want to search for is upsizing. See some documentation here.

1 Like
5

its a while since I've done it so may have Faulty Memory, or it may have changed!! but my recollection is that you could make a Linked Table in Access. So in place of a proprietary Access table "TableA" you create a Linked Table, also called "TableA", which points to the database in SQL Server (by the same mechanics it is possible to Link it to some other source of data). The table in SQL Serv does not have to be called "TableA" - but it probably helps a lot if it is!!

That may be the Top and the Bottom of it ... however, there are probably a few gotchas. For example Access uses "*" for wildcard data matches and SQL uses "%", and you may find that some things are inefficient as Access (as you have it) will be doing them "locally" [to Access] whereas SQL will perform much much faster if it can do them as a single SET Operation (i.e. both "Local to SQL Server" and also using Set-based syntax. Fingers crossed that "it will just work", which is all the more likely if the Database and Number of users is small (bear in mind that if you are sticking the database on a shared server then if the programming quality of the newly upsized App/Databases is poor then that will have ramifications on performance of other databases on that server. Again, unlikely to be a problem id databases size and number of users is small.

The [Access to SQL Server] "Upsizing Wizard" (again, from memory) points out things that are likely to need changing