converting Access db To SQL Server

[Alfie Higgin]

We have an MS Access database of over 100MB. It is running very slowly and beginning to crash with increasing frequency.

We want to consider going to a SQL Server back end with MS Access front end programming. However, we are afraid of making a large investment until we learn some basic facts.

We know we can re-program our mdb databases to .adp databases.

1) Can we use any SQL Server version? We are running mostly MS Access 2007 but want to move to MS Access 2010. Will a SQL express edition allow .adp programming?

2) Must we have a server to run SQL Server with multiple users? We are currently a pier-to-pier network with about 5 regular users and maybe 5 more occasional users. We had Windows Server 2000 in the distant past but at the time it didn't seem to offer us anything we needed to do that wouldn't work more easily in a pier-to-pier network.

Any input I could get from SQL users would be much appreciated.

We are reluctant to move until we know how things work.

one other thing: we have a mixture of Windows Vista and Windows 7 workstations. I think the Windows 7 are 64 bit versions. I see the SQL Server 2008 comes with 32-bit and 64-bit versions. Does that mean anything to us in a mixed-Windows environment?

[dhutch75]

Alphie, you posted your questions a month ago, so this might be to late to be useful. I have migrated several Access DBs to SQL server and it is a simple process. You create an ODBC connection to the server, whether it's a MS SQL Server or SQL Express and then link the tables, just as you would link to an Access Back End. I've never used .adp. My DBs are .mdb or compiled into .mde. SQL versions don't matter, nor will the Windows OS cause a problem. I haven't upgraded to Access 2010, but have used Access 2000 and 2003. The biggest difficulty I encountered when converting had to do with binary fields. If you neglect to set a default value in the SQL Server table, you will have serious data problems that will take you forever to troubleshoot. I can't remember the specific issue, but remember that it was a critical problem until I identified it. SQL is more particular about field names, also. Avoid spaces and special symbols in field names, as well as fields with keyword names like "Name" and "Date".