Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

Access 2007 subform not linking to parent with SQL Server Backend

Access 2007
SQL Server 2008

I have just finished linking all my Access 2007 front-ends to SQL Server 2008 databases.  I used DNS-less connections and the SQL Server driver.  Everything seems fine except in several front-ends I have a forms with a datasheet subforms ... and they are not linkling to the parent!?

The table for the parent form has a unique primary key "ORDERID" (int, Identity = yes, no nulls, clustered index) and the subform query has the same field ORDERID (int, Identity = no, nulls allowed, non-clustered index).  On the subform properties I have the Master/Child fields set to ORDERID.

If the record on the parent that is displayed already has records that match in both tables, then the subform is accessable, but if I am entering a new record, I can only enter data into the parent form and the subform remains disabled.

Can anyone tell me why?  With an Access backend, it works fine, but not with SQL Server backend.

Very important I find a solution ASAP! ... help!

Max
0
MaxwellTurner
Asked:
MaxwellTurner
  • 2
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What are you using as the Subform's Recordsource? Is it a table, or a query/view?

Views in SQL Server are (for the most part) not updateable.

0
 
MaxwellTurnerAuthor Commented:
Thanks LSM!  Wow this whole migration to SQL backend has been full of headaches, but one issue at a time!  Yes, I was using a query for the subforms ... works great now that I have changed it to the tables.

Max
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I feel your pain :)

Here's a very good writeup on some of "best practices" when moving to SQL Server from Access:

http://www.jstreettech.com/files/Best-of-Both-Worlds.zip

Also review some of the stuff from FMS:

http://www.fmsinc.com/tpapers/index.html#SQLPapers
0
 
MaxwellTurnerAuthor Commented:
Thanks LSM!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now