I have an exisiting Access Database (FE and BE). I am planning tomove to
SQL Server back end and I have imported the existing Access table structure
into SQL Server
Interesting that you moved the table structures, but not the data? A very
minor point, just for the most part when you use some of the import tools
and you've managed to get the table structures over, usually at that point
you'll likely also did transfer the data also (I guess my point is you see
that both of these tasks and processes are usually intertwined).
, rewrite Views and made some modification to forms. I will needs some
recommendation on the following:
1 - Whether to stick on Access front end as MDB (thru ODBC) or to switch
to ADP
If you're a writing something 100% from scratch, and you don't have a lot of
experience with MS access, and your goal is to use SQL server for your back
end from day one, then I would consider a ADP.
However in your case, it's not even close to it even thinking of rewriting
everything and convering to a ADP....way..way too much work. This would be a
tremendous amount of work, and for little gain. furthermore there are some
restrictions on what version of the particular SQL server you use (in your
case I believe you would have to place SQL server 2005 in a previous sql
server compatibility mode to use it with ADP in access 2003 -- in a way this
compatibility issue and potential problem is another good reason as to why
to avoid an ADP in your situation)
I would strongly suggest you simply keep your front end, and now a link your
tables instead of to the mdb back end, simply link them to your SQL server
via odbc. The end result will be probably 99% or more of your forms and code
will work as "is" and not have to be modified at all.
About the only additional thing to watch for is that ensure that **all** of
your tables on the SQL server side have a unique primary key (usually an
auto number). The other critical concept and one that really helps MS access
work well is to ensure that all tables have a time stamp field as part of
the structure. This is **especially** the case for forms that are bound to
tables/queries that edit data (so, forms and sub forms that edit data need
both a primary key and the time stamp field exposed to those forms).
Obviously for things like reports and other things the time stamp and
primary key is not a whole lot of importance.
2 - How to move quickly the existing data in Access table to SQL Server
table.
I am using Access 2003 and SQL Server 2005.
For the most part you just use the SQL server management tools and use the
import options. I always used the enterprise tools, and have not yet used
the SQL server 2005 management tools (but I assume they have a similar set
of tools you can use to import data. As mentioned, usually the idea is to
import all the tables from the back end database. This gives you the table
strucites + you get your data moved also. as mentioned a few do this to your
tables will need some tweaking to ensure that all tables have the primary
key, and for any tables that you edit directly via forms, you want to add
and expose a time stamp field...