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.
 
I

ijazbof

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
 
I

ijazbof

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
Linked SQL Tables don't report Cascading Updates and Deletes 2
"MSysCompactError", "System resource exceeded" & "You do not have the necessary permissions to use the...." 0
Best way to deploy Access read-only database that is linked to SQL tables 6
64 bit SQL? 3
Adding data to MS Access from csv file 0
form won't update, what to check 2
SQL Server Backend: linked tables or no more bound forms ? 4

Top