Access database with SQL Server

  • Thread starter Thread starter Ngan
  • Start date Start date
N

Ngan

Hi!

I have an Access database & it's running fine. Then I
import all my tables to SQL server and link the data back
from SQL to Access. But my problem I can't update the data
by using Access input form. Please help.

Thanks

Ngan
 
To be updatable, linked SQL Server tables must have at least one unique
index or constraint. Do they?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
After importing *.mdb data into a database in SQL Server, most likely, you
need to do some extra fixing on the imported table on the SQL Server
database. Usually, you have to set the primary key and/or index, which is
not imported from Access DB. Yo may also want to adjust some column types.
For example, Memo type in Access would become Text/NText type in Sql Server.
But if the data in that column is only text information and not longer tha a
few KB, you may want to change it to VarChar/NVarChar type instead, so that
you can perform sorting on it. It is rarely the case that you import, link
and then it works like nothing changed.
 
Hi,

Did you link the tables or did you turn your database into
Access.adp?

Changing from .mdb to .adp will help, but you will still
need to check your indexes and character types.
 
Ngan said:
I have an Access database & it's running fine. Then I
import all my tables to SQL server and link the data back
from SQL to Access. But my problem I can't update the data
by using Access input form. Please help.
Click to expand...

In addition to the comments about primary keys Access also really
likes a rowversion (formerly called timestamp) field.

Do a search at the Knowledge Base at support.microsoft.com using the
keywords "upsizing" to review the various white papers on upsizing
Access to SQL Server as well as to ensure you have any updates
required.

Also see my Random Thoughts on SQL Server Upsizing from Microsoft
Access Tips page at
http://www.granite.ab.ca/access/sqlserverupsizing.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Back
Top