Pass-Through Query Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

doodle - 01 Mar 2007 18:03 GMT

Greetings. access 97, sql 2005.

i am in the process of migrating the BE to sql. I am having some
performance issues and have decided that changing the record sources
for my combo boxes to reference pass-through queries may help
performance.

i have created a pass thru query for machine type:

SELECT * FROM tblLU_MachineType

and have set the following in the query propertiesODBC Connect Str:

ODBC;DSN=MCFLOSQL1;Description

Works fine. But I don't want my users to have to create the DSN. I did
a search and found some info on DSN-Less connections, but not for
Access 97.

How do I setup the DSN-Less connection for my pass-trough query?

-doodle

Reply to this Message

Douglas J. Steele - 01 Mar 2007 18:39 GMT

Try setting the Connect property to something like:

ODBC;DRIVER={sql
server};DATABASE=DatabaseName;SERVER=ServerName;Trusted_Connection=Yes;

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Greetings. access 97, sql 2005.
>
[quoted text clipped - 18 lines]
>
> -doodle

Reply to this Message

Ofer Cohen - 01 Mar 2007 19:15 GMT

When you are in query design, open the query properties, when the cursor is
placed on the ODBC connection property you'll see a button with three dots,
click it and the select the desire ODBC connection, before you close the
query save it with the new connection string.

One more thing, set the ODBC connection with authentication, so the user
wont need to log in when you run the query.

Signature

Good Luck
BS"D

> Greetings. access 97, sql 2005.
>
[quoted text clipped - 18 lines]
>
> -doodle

Reply to this Message

doodle - 01 Mar 2007 21:49 GMT

Doug,

I changed it to:

ODBC;DRIVER={sql
server};DATABASE=DatabaseName;SERVER=ServerName;Trusted_Connection=Yes;

i am still getting an error that the connection to the server failed.

ofer,

if i use the build button to select the DSN then my users will need to
add that DSN as well, right?

-doodle

Reply to this Message

doodle - 01 Mar 2007 22:31 GMT

i got it to work. there was a spacing issue in the connection string.
thanks again, doug.

adria
a.k.a. doodle

Reply to this Message

Douglas J. Steele - 01 Mar 2007 22:33 GMT

Assuming you replaced DatabaseName and ServerName with the appropriate
names, is your database set up for Trusted Connection, or are you using SQL
Authentication (which requires that you provide a user name and password)?

If you're using SQL Authentication, you'll need

ODBC;Driver={SQL
Server};Server=MyServerName;Database=myDatabaseName;Uid=myUsername;Pwd=myPassword

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Doug,
>
[quoted text clipped - 11 lines]
>
> -doodle

Reply to this Message

Prince - 28 May 2007 14:37 GMT

Hi,

I had a same issue I am trying to use above string it works on Server (I am
working on) but on client computer it's not working please see below eror:

Connection failed:
SQL State: 28000
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed fro
user 'AM\bpa..'

Any suggestions??

Regards,

> Assuming you replaced DatabaseName and ServerName with the appropriate
> names, is your database set up for Trusted Connection, or are you using SQL
[quoted text clipped - 20 lines]
> >
> > -doodle

Reply to this Message

Tom Wimpernark - 30 May 2007 16:46 GMT

you shouldn't be using pass-through, it is no longer the reccomended way to
use SQL Server data.

you should be using Access Data Projects

> Hi,
>
[quoted text clipped - 38 lines]
>> >
>> > -doodle

Reply to this Message

George Hepworth - 30 May 2007 17:43 GMT

This is not true, Aaron.

> you shouldn't be using pass-through, it is no longer the reccomended way
> to use SQL Server data.
[quoted text clipped - 44 lines]
>>> >
>>> > -doodle

Reply to this Message

Prince - 30 May 2007 18:42 GMT

Hi,
I got 20 million records in history table and I am using pass through
queries for report writing and doing most of the work on Server, could you
please advise in this senario what should I do And could you please send some
more details on "Access Data Projects" - thanks

a) Do I need to rewrite my reports??
b) Pass through queries will not work on client ?

Thanks for help

> you shouldn't be using pass-through, it is no longer the reccomended way to
> use SQL Server data.
[quoted text clipped - 43 lines]
> >> >
> >> > -doodle

Reply to this Message

George Hepworth - 30 May 2007 18:51 GMT

Please be advised that "Tom Wimpernark" is an alias used by Aaron Kempf.

> Hi,
> I got 20 million records in history table and I am using pass through
[quoted text clipped - 62 lines]
>> >> >
>> >> > -doodle

Reply to this Message

Prince - 30 May 2007 19:01 GMT

Is he around OR can anyone else advise on below question please!

> Please be advised that "Tom Wimpernark" is an alias used by Aaron Kempf.
>
[quoted text clipped - 64 lines]
> >> >> >
> >> >> > -doodle

Reply to this Message

George Hepworth - 30 May 2007 20:30 GMT

My point is that you don't need to be concerned about these issues.

SQL Pass-Thrus will be around for the foreseeable future.

ADPs and DAPs, while still viable, are NOT particularly high on the list of
approaches Microsoft recommends.

Pay attention to the recommendations from Doug Steele about working with
your data.

> Is he around OR can anyone else advise on below question please!
>
[quoted text clipped - 73 lines]
>> >> >> >
>> >> >> > -doodle

Reply to this Message