When to migrate from Access to SQL

[dalebra]

I've read that at some point I may need to migrate my database from a MS Access database to a SQL database.

Sounds like MS SQL Express will be my choice. So can anyone provide some insight as to when I may need to do this and then I was wondering if someone could point me to a tutorial or a list of steps to do this without taking my WEB app down?

Thanks in advance for your help.

[agillbb]

Re: When to migrate from Access to SQL

If your app does not have a lot of traffic (i.e. the app still performs well), and you are happy performing file-based backups, then stick with Access because of its simplicity.
Otherwise, consider moving up. There are pros and cons to both. I have used both, and can say that using MS Access as a DB backend has been extremely reliable in an in-house intranet environment. For web-based apps (on a remote server), I would more go with SQL because of data management, backups, etc.

[Pat Bremkamp]

Re: When to migrate from Access to SQL

Dale,

It doesn't have to be all or nothing. Alpha lets you have multiple connection strings, so you can set up the SQL database and create one of your simple tables there, like customer or contact or one of your lookup tables, and set only the grids that add and edit that table and the dropdowns that use it. That way you can gain experience with the SQL before you jump in with both feet. Once you are comfortable, switch everything or continue to convert a few tables at a time.

[dalebra]

Re: When to migrate from Access to SQL

Thanks for the advise. Since it's only on our intranet and gets limited hits I think I'll take the slow approach and start looking at a test database to try sql.

[GGandhi]

Re: When to migrate from Access to SQL

hello
I know one of the forum member uses access as her backend for her professional site and no complaints.
when I find her name I will post it.
you can still use access since you are comfortable with it.

I am pretty sure it is
kingcarol
you can pm her and see if what I am saying is true and if she has had any problems.

[-Jinx-]

Re: When to migrate from Access to SQL

If you do migrate, the easiest way IMHO is to get a test environment with both the access files and an sql instance running on your PC. Then, with the right drivers and the SQL server management studio you can just query your access files and dump them into SQL tables.

An example of SQL querying an Acess DB file looks something like this:

SELECT *
from OPENDATASOURCE
('Microsoft.Jet.OLEDB.4.0','Data Source=c:\TRANSFER\MP250DB.MDB')...[INVY]

I've converted numerous Access DB's over to SQL in this way. You can clean the tables and add a Unique ID field (if they don't currently contain one) as part of the query that dumps them in.

[RobPolley]

Re: When to migrate from Access to SQL

Pat,

This is a very helpful clarification; thanks!

--Rob Polley