No Foreign Keys retrieved

  • Thread starter Wes from Scottsdale
  • Start date
W

Wes from Scottsdale

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?
 
M

mscertified

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
 
W

Wes from Scottsdale

2003.

I'm going to try re-writing it again, maybe splitting it into two queries.

Thanks.

mscertified said:
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 said:
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?
Click to expand...
Click to expand...
 
K

Klatuu

If it is working in SQL Server, make it a passthrough query and see if it
works that way.

Wes from Scottsdale said:
2003.

I'm going to try re-writing it again, maybe splitting it into two queries.

Thanks.

mscertified said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
W

Wes from Scottsdale

What do you mean by a passthrough query. I've not heard that term before.

The query is dynamically built using data selected on a form.

Thanks.

Klatuu said:
If it is working in SQL Server, make it a passthrough query and see if it
works that way.

Wes from Scottsdale said:
2003.

I'm going to try re-writing it again, maybe splitting it into two queries.

Thanks.

mscertified said:
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

:

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?
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

If the query is constructed dynamically, a direct passthrough will not work.

Here is another approach, create an SQL view that returns the columns you
need and include any universal filtering (filtering that would apply in all
circumstances) and link to it from your Access be. Now, if you get the
values you want when opening the view, code your query to use the view.
When you link to an SQL View in Access, it looks and acts just like a table.


Wes from Scottsdale said:
What do you mean by a passthrough query. I've not heard that term before.

The query is dynamically built using data selected on a form.

Thanks.

Klatuu said:
If it is working in SQL Server, make it a passthrough query and see if it
works that way.

in
message news:[email protected]...
2003.

I'm going to try re-writing it again, maybe splitting it into two
queries.

Thanks.

:

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

:

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?
Click to expand...
Click to expand...
Click to expand...
 
W

Wes from Scottsdale

I think I figured it out. I was accessing the recordset fields out of order.
For example, if i access rst.fields(3) before rst.fields(2), rst.fields(2)
would show as null. I changed my assignments to step through the fields in
order and all the data shows up.

I have absolutely no idea why this happened this way. I even had a test
loop msgbox the contents of each field in order after the assignments and the
fields accessed out of order previously would still show blank.

I was not aware it made a difference which order fields were accessed in.
 
K

Klatuu

I don't think that is correct. There should be no difference based on the
order.

Wes from Scottsdale said:
I think I figured it out. I was accessing the recordset fields out of
order.
For example, if i access rst.fields(3) before rst.fields(2), rst.fields(2)
would show as null. I changed my assignments to step through the fields
in
order and all the data shows up.

I have absolutely no idea why this happened this way. I even had a test
loop msgbox the contents of each field in order after the assignments and
the
fields accessed out of order previously would still show blank.

I was not aware it made a difference which order fields were accessed in.

Wes from Scottsdale said:
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?
Click to expand...
Click to expand...
 
W

Wes from Scottsdale

I agree, but it made a difference.

I'd originally placed my code handling foreign keys to the end of module for
clarity and handled all the standard data variable assignments in one block.
I was skipping columns in the recordset to get the values I needed. When I
went back to a previously skipped column to handle processing a foreign key,
the value was null. Once I reorganized my code, assigning the foreign keys
to variables in column order, all data was available.

The only logic change was that I was using a variable containg the foreign
key in a subsequent query, rather than the column of a record in a seperate
recordset.

I've used columns out of order and also column references in other locations
in my code, so I still don't know what the underlying problem is.

Thanks.

Klatuu said:
I don't think that is correct. There should be no difference based on the
order.

Wes from Scottsdale said:
I think I figured it out. I was accessing the recordset fields out of
order.
For example, if i access rst.fields(3) before rst.fields(2), rst.fields(2)
would show as null. I changed my assignments to step through the fields
in
order and all the data shows up.

I have absolutely no idea why this happened this way. I even had a test
loop msgbox the contents of each field in order after the assignments and
the
fields accessed out of order previously would still show blank.

I was not aware it made a difference which order fields were accessed in.

Wes from Scottsdale said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Top