Pass-Through Query Help

[doodle]

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

 

[Douglas J. Steele]

Try setting the Connect property to something like:

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

 

[Ofer Cohen]

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.

 

[doodle]

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

 

[doodle]

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

adria
a.k.a. doodle

 

[Douglas J. Steele]

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

 

[Prince]

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,

 

[Tom Wimpernark]

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,

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

 

[George Hepworth]

This is not true, Aaron.

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,

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

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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

 

[Prince]

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.

you should be using Access Data Projects

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

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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

 

[George Hepworth]

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
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.

you should be using Access Data Projects

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

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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

 

[Prince]

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.

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.

you should be using Access Data Projects



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

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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

 

[George Hepworth]

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!

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
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.

you should be using Access Data Projects



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

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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