upsize to sql server

G

giloosh

Hello All,
I wanted to try upsizing my mdb db to a sql server db...
i upsized my access mdb to a database on sql server 2005 and some
tables went through and some didn't. for those tables that did go
through i linked to my front end mdb file replacing all of the old
links from the mdb. i figured using sql server vs mdb an mdb file
database would be faster.
when i go into some of the linked tables in the front end mdb i cant
add new records (the "addnew" record selector is even grayed out).
Some tables let me add records and some do not. this can cause a
problem for my application not to work properly. why would this
happened.

so to make my questions clear:
1. why do some tables not make it through onto sql server 2005 during
the upsizing wizard process.
2. why do some of the sql server linked tables not allow me to add new
records in the table view?

thanks for you help.
 
A

Arvin Meyer [MVP]

The answer may be the same to both questions. You need a primary key to
update records.

Unless your database has sensitive data that requires a high level of
security, or you have a large number of users, or you anticipate more than a
gigabyte of data, there is little point in moving to SQL-Server. Without
rewriting everything to use unbound forms through stored procedures and
views, you will not gain any speed, and in fact, may actually slow down
considerably.
 
G

Guest

Hi:

I have a simmilar case, I have all tables linked through an E-R diagram in
SQL Server and all tables have primary keys set, but is odd that won't let
you add records, in SQL Server I can add records through the native
interface, but can't add from Access. Some tables let me add records and some
do not. The main independent table works fine and also catalog tables but
dependent tables are the ones who don't work.

Thanks in advance
Regards
Hugo
 
A

Arvin Meyer [MVP]

If you are working through Access, all the DRI rules must be followed
through Access as well as through SQL-Server. You may enter dependent data
only to records which have a main record matching the foreign key. From
Access that is usually done through a subform, that has the master/child
link set.

Also, I once had a similar problem when creating my tables through Access
within an Access ADP. I resolved it by using the SQL-Server Enterprise
Manager (SQL 2000) to build my tables.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
 
G

Guest

Hi:

Thanks for answering the question, I have a form with subforms, but the
subform won't create a record and the add record button is grayed. Do you
think I should create the tables again by hand? that is not a nice option. I
use MS Access 2003 and SQL Server 2000.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL upsize 2 databases 2
Access Front - SQL Back - File Locking 6
64 bit SQL? 3
Access Upsize Wizard to SQL Server 2
Upsizing Problem with number of records 2
Migrating Complicated Access 2000 DB to SQL Server 5
Access Front-End to SQL Server 2
form won't update, what to check 2

Top