SQL Pass Through Query...

[Guest]

I am using a DSN file to initiate a connection to a SQL server. All the
tables link up without a hitch and I can view data from all of them normally.

However, I have a Pass Through Query that I have to use with a table that
has a clustered index based on several columns, one of which is a BigInt
field. When I run the Pass Through query it requests the data source each
time its run.

Is it some sort of permissions issue on the SQL side, or do I need to run
this using a VBA module with an connection string stated in the code. I'm
kinda' at a loss on this one.

This only happens with the SQL specific queries.

 

[Guest]

Open the query in design view and pull up the properties for the query.
There is a place to put the ODBC connection string.

 

[Guest]

Thanks for the reply, but that won't work for an SQL specific pass through
query. I'm forced to retreive data with a Pass Through because one of the
key fields is a BigInt field, and if use an Access based query all the
records display #Deleted flags, even though there is data in the table.
Microsofts solution to this is to either use Int fields in the SQL table, or
text fields, neither of which will work in this instance.

So, to display data in a form I need to get the data with an SQL pass
through query.

Thanks though...

 

[Douglas J Steele]

Why won't it work? Pass-through queries need to have their Connect property
set.

 

[Guest]

Ever stare at a problem so long that you forget the basics...

Thank to the both of you, I've got it working and lo and behold, all I had
to do was click the properties button on the darn tool bar. I kept
right-clicking and in the SQL Pass-Through Query screen that displays the SQL
you don't get properties as an option in the shortcut menu.

You were both right and I can now move forward again.

Thanks...

 

[Guest]

Pass through queries not not look at the tables connection string. Each time
you are running the query it is asking for the connection string since you do
not have it saved in the query properties. You can choose to save the
password or not. If you don't save the password it will prompt each time it
has to reconnect.

 

[Douglas J Steele]

Who said anything about "the tables connection string"? The QueryDef object
has a Connect property as well. If the query doesn't have its Connect
property, it isn't a pass-through query.

 

[Guest]

My response was before I saw Kevin found the properties. His original post
made it sound like he thought that since his tables were linked that the pass
through query would connect, and I was just rying to explain that the query
has its own connection string that is required, but I guess I did not do it
clearly.