Access FE - SQL BE

[Miskacee]

I'm new to stored procedures on SQL Server 2005. I created a stored
procedure but can't seem to call it from Access 2000. I know I use Execute
sp_storedProc but don't know what else I need.

Please help if possible and thank you!

 

[Douglas J. Steele]

You need to create a pass-through query that has the appropriate connection
information to allow it to run on the SQL Server.

 

[Miskacee]

I created the pass-through query but am asked each time for the connection.
how can I get this to automatically link without having to type in my
connection?

 

[Miskacee]

Do I create all of my queries as a pass-through queries and create a stored
procedure for my parameter queries? My objective is to speed up my program.
People in Europe will be accessing this program. Right now I have a SQL
backend up queries in Access and am trying to get the queries to run from
SQL. Is this the best possible way to achieve my objective?

Thank you Douglas!

 

[Douglas J. Steele]

Probably, but be aware that pass-through queries are read-only.

Also, if your stored procedures require parameters, you must use VBA to
dynamically rewrite the SQL of the pass-through query: pass-through queries
can't refer to Access parameters or controls on Access forms like other
queries can.

 

[Douglas J. Steele]

What are you using as a connection? Is it a DSN that doesn't include user
information? Are you using trusted connections?

 

[Miskacee]

I'm using ODBC connection - DSN. I plan on creating stored procedures for my
parameters in SQL but I thought I'd create my other queries via pass-through
queries. Is this correct?

 

[Miskacee]

I've got my query connecting automatically - entered the connection string
in the properties area. Now I have another issue with one of my queries.
The beginning of the statements is: SELECT DISTINCT
([dbo_tbl_sat_survey]![lname]) & ", " & ([dbo_tbl_sat_survey]![fname]) AS
employee,. How do I enter this via a pass-through query? I'm getting an
error and assume it is referencing the fields. I want to combine lastname,
firstname on the drop down list box.

Thank you once again!

 

[Douglas J. Steele]

You have to remember that pass-through queries use the SQL dialect of the
server, not of the client.

SQL Server uses single quotes rather than double quote, and uses + rather
than &.

SELECT DISTINCT
[dbo_tbl_sat_survey]![lname] + ', ' + [dbo_tbl_sat_survey]![fname] AS
employee,.

I suspect, though, that the table name is actually dbo.tbl_sat_survey (if
you linked to SQL Server, Access would have converted the dot to an
underscore.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I've got my query connecting automatically - entered the connection
string
in the properties area. Now I have another issue with one of my queries.
The beginning of the statements is: SELECT DISTINCT
([dbo_tbl_sat_survey]![lname]) & ", " & ([dbo_tbl_sat_survey]![fname]) AS
employee,. How do I enter this via a pass-through query? I'm getting an
error and assume it is referencing the fields. I want to combine
lastname,
firstname on the drop down list box.

Thank you once again!

What are you using as a connection? Is it a DSN that doesn't include user
information? Are you using trusted connections?

 

[Miskacee]

Thank you Douglas. I will start working but may have more questions. I
really appreciate all of your assistance!

 

[Miskacee]

You have to remember that pass-through queries use the SQL dialect of the
server, not of the client.

SQL Server uses single quotes rather than double quote, and uses + rather
than &.

How do I convert this?
SELECT dbo_tbl_exit_resigning.SurveySentDate,
Format([SurveySentDate],"yyyy-mm") AS [Sent Date]
FROM dbo_tbl_exit_resigning
ORDER BY Format([SurveySentDate],"yyyy-mm");
I know to change the dbo_ to dbo. but can't quite figure out about the date
format.

Thank you!!!!

SELECT DISTINCT
[dbo_tbl_sat_survey]![lname] + ', ' + [dbo_tbl_sat_survey]![fname] AS
employee,.

I suspect, though, that the table name is actually dbo.tbl_sat_survey (if
you linked to SQL Server, Access would have converted the dot to an
underscore.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I've got my query connecting automatically - entered the connection
string
in the properties area. Now I have another issue with one of my queries.
The beginning of the statements is: SELECT DISTINCT
([dbo_tbl_sat_survey]![lname]) & ", " & ([dbo_tbl_sat_survey]![fname]) AS
employee,. How do I enter this via a pass-through query? I'm getting an
error and assume it is referencing the fields. I want to combine
lastname,
firstname on the drop down list box.

Thank you once again!

What are you using as a connection? Is it a DSN that doesn't include user
information? Are you using trusted connections?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I created the pass-through query but am asked each time for the
connection.
how can I get this to automatically link without having to type in my
connection?


:

You need to create a pass-through query that has the appropriate
connection
information to allow it to run on the SQL Server.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I'm new to stored procedures on SQL Server 2005. I created a stored
procedure but can't seem to call it from Access 2000. I know I use
Execute
sp_storedProc but don't know what else I need.

Please help if possible and thank you!

 

[Douglas J. Steele]

SQL Server doesn't have a Format function.

Try

SELECT dbo.tbl_exit_resigning.SurveySentDate,
Year([SurveySentDate]) + '-' & + Month([SurveySentDate]) AS [Sent Date]
FROM dbo.tbl_exit_resigning
ORDER BY 2


Hopefully you have BOL (Books Online), the SQL Server help file.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You have to remember that pass-through queries use the SQL dialect of the
server, not of the client.

SQL Server uses single quotes rather than double quote, and uses + rather
than &.

How do I convert this?
SELECT dbo_tbl_exit_resigning.SurveySentDate,
Format([SurveySentDate],"yyyy-mm") AS [Sent Date]
FROM dbo_tbl_exit_resigning
ORDER BY Format([SurveySentDate],"yyyy-mm");
I know to change the dbo_ to dbo. but can't quite figure out about the
date
format.

Thank you!!!!

SELECT DISTINCT
[dbo_tbl_sat_survey]![lname] + ', ' + [dbo_tbl_sat_survey]![fname] AS
employee,.

I suspect, though, that the table name is actually dbo.tbl_sat_survey (if
you linked to SQL Server, Access would have converted the dot to an
underscore.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I've got my query connecting automatically - entered the connection
string
in the properties area. Now I have another issue with one of my
queries.
The beginning of the statements is: SELECT DISTINCT
([dbo_tbl_sat_survey]![lname]) & ", " & ([dbo_tbl_sat_survey]![fname])
AS
employee,. How do I enter this via a pass-through query? I'm getting
an
error and assume it is referencing the fields. I want to combine
lastname,
firstname on the drop down list box.

Thank you once again!

:

What are you using as a connection? Is it a DSN that doesn't include
user
information? Are you using trusted connections?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I created the pass-through query but am asked each time for the
connection.
how can I get this to automatically link without having to type in
my
connection?


:

You need to create a pass-through query that has the appropriate
connection
information to allow it to run on the SQL Server.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I'm new to stored procedures on SQL Server 2005. I created a
stored
procedure but can't seem to call it from Access 2000. I know I
use
Execute
sp_storedProc but don't know what else I need.

Please help if possible and thank you!