August 9, 2008 at 7:52 am
You're starting out with two Access files: FrontEnd.mdb and BackEnd.mdb
BackEnd.mdb holds all the tables
FrontEnd.mdb has the user interface, and has links to the data in BackEnd.mdb
1. Run the SSMA on the BackEnd.mdb which results in the data being copied into MSSQL, the local Access tables have been renamed, and it now has links to the MSSQL data that use the original table names.
2. Delete all the links from FrontEnd.mdb into BackEnd.mdb.
3. Import the links from BackEnd.mdb into FrontEnd.mdb. Those links now pull data directly from MSSQL. Because SSMA used the original table names, all the links should work the same. You don't need BackEnd.mdb any more.
EDITED: My original version did not work, this one does. I thought that you could link to another link but Access doesn't allow that. Sorry for any confusion.
August 9, 2008 at 8:47 am
My original idea did not work, please see the edited post for the corrections...
August 12, 2008 at 4:27 pm
One more question: end users connect to an mdb backend which is installed on a networked server disk, O:\ in my case. If I were to migrate that backend to SQL Server 2005(or Express), should I then install SQL Server 2005(or Express) on the O:\ disk beforehand?
August 13, 2008 at 6:14 am
No, that's not how SQL Server works.
First, you need to have the SQL Server program installed & running on a Windows server on your network. (If you don't have a server, you can install the free Express version on Windows XP or Vista just for testing purposes.)
Then, when you run the SSMA you are prompted to select that server, you specify a name for the new SQL database, and the SSMA creates the SQL database, copies the data from Access, and creates the links from Access to the new SQL database. The SQL database file (MDF) physically sits on the server, but you don't touch the file directly - the SQL Server program manages the file.
The location of the Access database doesn't matter, because it connects to the new SQL database using the name of the server & the name of the SQL database.
You might want to download & install the Express edition on your PC to get familiar with how SQL Server works, if you haven't worked with it before.
Hope that helps.
August 13, 2008 at 7:31 am
Thank you Bill, things are much clearer now; when you say the location of the Access database doesn't matter, you mean the Access frontend, right?
If I download & install the Express edition on my PC, I cannot run the SSMA against it, right?
August 13, 2008 at 8:40 am
Grovelli,
Why do you keep differentiating between "frontend" and "backend" ? If the tables are within the .mdb file, then THAT file IS the Access Database, and that's the file you migrate the data from. If you have a 2nd Access database that has code and forms and such, that exists as a separate .mdb file from another one that only has the tables, I would recommend you combine these first, and then use SSMA to migrate from the one remaining .mdb file.
The reason I ask this is because separating your .mdb between files for code and for data just doesn't seem to me to have any benefit. You still have to use the Compact and Repair functionality regularly anyway, so what difference does it make?
Steve
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 13, 2008 at 10:48 am
You're right Steve, after the procedure outlined by Bill above, the backend isn't needed anymore and gets discarded.
If I download and install the Express edition on my PC, I cannot run the SSMA against it, right?
August 13, 2008 at 11:00 am
Why not? SSMA has no reason to care that the server involved happens to be local. At least no reason I can think of, anyway. One way to find out is to try it.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply