Setting up Access for SQL

[Yecenia]

I am currently building an Access database that will be up graded to SQL
within the next few months. We need the database now that is why we can't
wait until we receive a server to start building the database.

Right now there are several combo boxes in the tables. These combo boxes do
not come from a seperate lookup table, the content is inside the table. Will
I have issues in SQL if I don't changes these combo boxes to lookup tables?
Please advise.

I'm working with Access 2007. If there are any other suggestion you may
have about how I should be structuring the DB for SQL in Access, all
suggestions are welcomed!

Thanks!

 

[Paul Shapiro]

I think you could continue to use data lists in the Access forms with SQL
Server, but I don't think it's a very good practice. Data should be in the
database. That way you can enforce integrity, and when the time comes that
you need other "information" about that data, there's an appropriate place
to put it. So if you have a list of Product Categories, I would put it in a
ProductCategory table. You could have a category name, and maybe a shorter
code for use in reports where page space is tight. Etc. Relying on the user
interface to hold your data is likely to be a problem as things change in
the future, whether your data is in Access or SQL Server. Without the data
integrity enforced by database relationships, it is more likely that you
will end up with some invalid data. When the users need a new value, you
have to update the user interface. Much simpler to update data than
distribute a new UI. You don't save anything by putting it in the user
interface, so you might as well do it correctly.

 

[Mary Chipman [MSFT]]

To add to what Paul said, you should move as much of the data
processing to the server as possible, otherwise you would be missing
out on the benefits of moving the data to SQL Server (security, data
integrity/consistency, etc.). That being said, you can keep lookup
tables locally in Jet if the data is static and doesn't change much,
refreshing it as needed. The learning curve is fairly steep, and a lot
depends on your business needs. Here are some additional resources you
may find helpful:

TechEd Online Panel (video):
Go to http://msdn.microsoft.com/en-us/events/teched/cc676818.aspx and
search for: "Are we there yet? Successfully navigating the bumpy road
from Access to SQL Server"

Microsoft Access or SQL Server 2005: What's Right in Your
Organization?
http://www.microsoft.com/Sqlserver/2005/en/us/migration-access.aspx or
download.microsoft.com/download/a/4/7/a47b7b0e-976d-4f49-b15d-f02ade638ebe/SQLAccessWhatsRight.doc

Optimizing Microsoft Office Access Applications Linked to SQL Server
http://msdn.microsoft.com/en-us/library/bb188204.aspx

What are the main differences between Access and SQL Server?
http://sqlserver2000.databases.aspf...ifferences-between-access-and-sql-server.html

"The Best of Both Worlds--Access MDBs and SQL Server"
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

SQL Server Migration Assistant for Access (SSMA for Access)
http://www.microsoft.com/sql/solutions/migration/access/default.mspx

FMS Upsizing Center
http://www.fmsinc.com/Consulting/sqlupsizedocs.aspx

Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/dp/0672319446

Migration Considerations for Access 2007
http://technet.microsoft.com/en-us/library/cc178973.aspx

 

[Danielle Goyette]

I am currently building an Access database that will be up graded to SQL
within the next few months. We need the database now that is why we can't
wait until we receive a server to start building the database.

Right now there are several combo boxes in the tables. These combo boxes
do
not come from a seperate lookup table, the content is inside the table.
Will
I have issues in SQL if I don't changes these combo boxes to lookup
tables?
Please advise.

I'm working with Access 2007. If there are any other suggestion you may
have about how I should be structuring the DB for SQL in Access, all
suggestions are welcomed!

Thanks!