I currently have a MS Access 2002 database that is sitting on a file server and is split into a front-end and back-end. (with quite a few users)

I have been 'delegated' to move the back end to SQL server to comply with some corporate guidelines, but have a few questions as to where to start.

will SQL server express work for this?

but my main question is this: do I need to move the database OFF the file server and ONTO a standard Windows Server box?? do I need to move the entire db (front and back ends) to a windows server machine, or just the back en, and leave the front end on the file server??

thanks for any insight you may have!

-Tyhooke

First, a question about your FEs...

Do all of your users have there OWN copy of the FE on their local PC? (See this post)

Converting your DB to SQL eliminates the Access BE.

I am not sure about the limitations of SQL Server Express. As to your main question though, if you are moving to SQL Server, your Backend File will go away. It will not be used anymore. SQL Server runs only on Windows machines. Your front end does not matter where it is located as long as it can "talk" to your SQL Server. You may want to consider redoing your front end as well to use an Access ADP instead of a standard MDB because ADP more tightly integrate with SQL Server. Access has an "Upsizing Wizard" that should help you migrate the back end from Access to SQL Server.

thank you both for responding... let me address both threads:

the FE and BE are both stored on the file server. probably not the cleanest way to use the DB, but it works for this instance. (of course all users have MS Access installed on their local machines)

currently we have a BE that has a database password securing it, and a FE that implements user-level security. what we were HOPING for was an easy way to convert JUST the BE to SQL Server, but leave the FE exactly the same. it (the FE) is quite complex, has a lot of VBA behind it, and ties into other applications, so any way of changing the BE without touching the FE would be best (we dont' have a lot of resources to put onto any new development of the FE).

I've looked into ADP but don't think they will work in this instance.

so, if I move the FE to a Windows Server box, and move the BE to the same box using SQL server, I imagine I'll need to setup a ODBC connection, right? I also assume this needs to be done ONLY on the Access FE on the Windows Server box, an dnot on every users's machine?

thanks again for your response, I may not have much time to implement this so am hoping to gain enough knowledge b/4 I start.

-Tyhooke

Import the tables from the BE into a new SQL database and then delete the current Access Links and relink to the SQL Server db, making sure the table names are the same as they were in Access. Every thing should work as before.
I would suggest that you begin changing any code and queries to make use of the client/server capabilitites of SQL Server as you have the time.

IMHO, you're asking for trouble if every user doesn't have their own FE. It's just a matter of time.

As far as MDB vs ADP, I've never come across anything I couldn't do in an ADP that I could in an MDB.

again thanks all for your comments. sounds like this may be a fairly 'do-able' project for my limited timeline..

niesz,
Initially I was thinking ADP for 'data access pages' - oops, obvously I was way off on that one! I've looked into ADP (Access project) just a bit. However myy current database has multiple enviroments (dev, test, prod) and b/c of this I've got a single table that is stored in the FE (so this table in NOT linked) whos purpose is to help the developer/adminstarator with the ovehead of managing the different enviroments. Will I run into problems trying to create a ADP with all my tables linked to a BE except for one I'd like to keep stored in the FE?

truittb,
You stated, "changing any code and queries to make use of the client/server capabilitites of SQL Server" - could you touch on that a bit further? I'm not sure I'm following what changes would need to be implemented..

thanks !!!!

-Tyhooke

ADPs do not support local tables. Essentially the DB container is removed from the ADP and just sits on top of SQL. However, you can still embed "Project Variables" as properties of the ADP to track versions if you want.

If your BE fits in an Access MDB, it will fit in SQL Server Express (which has twice the capacity of an Access MDB -- 4GB rather than 2GB).

I, too, would highly recommend you NOT have a "shared" FE. Each user needs their own copy.

If you are using ADO to connect to the back end convert the cursors to serverside. Create stored procedures on the server to do the processing where you are currently using VBA. Use pass thru queries to execute stored procs or to return records.