RE: No Foreign Keys retrieved

I'm not really sure but you could try creating a stored proc in sql server
that returns the data, then read the stored proc instead of the sql server
table.
If they still don't come through, you could try changing the format of those
columns in the stored proc.
What version of Access are you using?

-David

"Wes from Scottsdale" wrote:

I'm working with SQL Server in an Access project. My query needs to retrieve
the details of a contract. My query runs fine but none of the foreign key
value are populated.
My query is:
strSQL = "SELECT [AuthFormDate], [ApprovedDate], [ExecutionDate],
[CouncilDate]" _
& ", [ExpirationDate], [ClosedDate], [ContractAmt], [Notes],
[ContractID], " _
& "[FirmID], [RenewalID], [InHouseCounsel], [DepartmentPayingID], " _
& "[ClientDepartmentID], [MatterID], [OriginalOnFile],
[AuthFormOnFile], " _
& "[SelectionMemoOnFile], [ConType], [OrigDoc], [AuthFormDoc],
[SelMemoDoc]" _
& " FROM [tContract] WHERE [ContractID] = '" & lstSearch.Column(0) & "'"

The foreign key values are FirmID, InHouseCounsel, DepartmentPayingID,
ClientDepartmentID and MatterID. They are all Not Null of Int data type.
Again, the row is retrieving everything except for the Foriegn Key values,
which are brought back blank. I've also tried the query using an * instead
of column names, with the same result. If I run the same query in SQL Server
Manager, all data exists so the problem only comes up in Access.

Any ideas?
.

Relevant Pages

  • Re: Stored procedure never finishes
    ... Perhaps when you run the query without the date range, ... You do this by issuing "exec sp_lock [spid]" with spid being ... SQL Server MVP ... > I have a stored proc that won't finish. ...
    (microsoft.public.sqlserver.server)
  • Re: Stored procedure never finishes
    ... I have found that if I don't use temp tables then the stored proc works. ... > that you may have a run away query (this can't be fixed by restarting the ... > SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: AMO question
    ... could be related to the fact that it is in a stored proc. ... in production I'm running it in a SQL Server job as an Analysis ... Services Query Step. ... That brings on the question...how can I call that without a transaction, ...
    (microsoft.public.sqlserver.olap)
  • RE: No Foreign Keys retrieved
    ... then read the stored proc instead of the sql server ... My query runs fine but none of the foreign key ... The foreign key values are FirmID, InHouseCounsel, DepartmentPayingID, ... If I run the same query in SQL Server ...
    (microsoft.public.access.modulesdaovba)
  • Re: Need Query For Enforce relationship fo replication
    ... Soura, ... I keep a list of this type of query at ... where constraint_type = 'foreign key' ... Paul Ibison SQL Server MVP, ...
    (microsoft.public.sqlserver.replication)
Loading