First, if I'm posting this in the wrong newsgroup, please let me know
and I'll re-post elsewhere.
Trying to get SQL Server 2K data into an Access 2K front-end application
(we're upsizing our back-end DB's to SQL Server, want to keep mdb
front-ends). To limit the returned recordsets for both forms and
reports, we want to pass parameters (like customerid or orderid) to SQL
Server and return just the data needed.
What is the "best practice" way to do this? A stored procedure (that
accepts the parameter) linked to a view, or a user-defined function? Do
both allow data updates? Or, is one better for a form (data updates) and
the other for a report (view only)?
Sorry for the basic question. Been developing in Access for a few
years, but I'm REALLY new to SQL Server.
Thanks,
JLH
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Using linked mdb FE's can be very efficient if you code it right. You
need to create forms with restrictive WHERE clauses that load data for
editing, preferably one record at a time (tables are find as long as
you don't base forms on the entire table). You can also take advantage
of local storage for caching static data for use in listboxes, etc.
Use stored procedures with pass-through queries for reports. You can
base a report directly on a pass-through query that executes a stored
procedure.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Tue, 06 Jul 2004 06:25:39 -0700, Jeffery Hawkins
<jhawkins@.villagepress.com> wrote:
>First, if I'm posting this in the wrong newsgroup, please let me know
>and I'll re-post elsewhere.
>Trying to get SQL Server 2K data into an Access 2K front-end application
>(we're upsizing our back-end DB's to SQL Server, want to keep mdb
>front-ends). To limit the returned recordsets for both forms and
>reports, we want to pass parameters (like customerid or orderid) to SQL
>Server and return just the data needed.
>What is the "best practice" way to do this? A stored procedure (that
>accepts the parameter) linked to a view, or a user-defined function? Do
>both allow data updates? Or, is one better for a form (data updates) and
>the other for a report (view only)?
>Sorry for the basic question. Been developing in Access for a few
>years, but I'm REALLY new to SQL Server.
>Thanks,
>JLH
>
>*** Sent via Devdex http://www.devdex.com ***
>Don't just participate in USENET...get rewarded for it!
|||Thanks, Mary. By linked FE's are you meaning that we should link the
SQL Server tables directly into the Access FE, and not pass parameters
back to SQL server SP's for data retrieval? (Great book, by the way --
as I've told you before!).
Thanks,
JLH
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||No, not at all -- absolutely you *should* use parameterized stored
procedures wherever possible. You can supply parameter values in code
to the .SQL property of a pass-through query (using DAO) so that it
would look something like:
myPSQ.SQL = "exec myproc " & varVal1 & ", '" & strVal & "'"
--Mary
On Thu, 08 Jul 2004 10:35:11 -0700, Jeffery Hawkins
<jhawkins@.villagepress.com> wrote:
>Thanks, Mary. By linked FE's are you meaning that we should link the
>SQL Server tables directly into the Access FE, and not pass parameters
>back to SQL server SP's for data retrieval? (Great book, by the way --
>as I've told you before!).
>Thanks,
>JLH
>
>*** Sent via Devdex http://www.devdex.com ***
>Don't just participate in USENET...get rewarded for it!
sql
Showing posts with label wrong. Show all posts
Showing posts with label wrong. Show all posts
Tuesday, March 20, 2012
Access 2K + SQL Server: Data Retrieval Q
First, if I'm posting this in the wrong newsgroup, please let me know
and I'll re-post elsewhere.
Trying to get SQL Server 2K data into an Access 2K front-end application
(we're upsizing our back-end DB's to SQL Server, want to keep mdb
front-ends). To limit the returned recordsets for both forms and
reports, we want to pass parameters (like customerid or orderid) to SQL
Server and return just the data needed.
What is the "best practice" way to do this? A stored procedure (that
accepts the parameter) linked to a view, or a user-defined function? Do
both allow data updates? Or, is one better for a form (data updates) and
the other for a report (view only)?
Sorry for the basic question. Been developing in Access for a few
years, but I'm REALLY new to SQL Server.
Thanks,
JLH
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Using linked mdb FE's can be very efficient if you code it right. You
need to create forms with restrictive WHERE clauses that load data for
editing, preferably one record at a time (tables are find as long as
you don't base forms on the entire table). You can also take advantage
of local storage for caching static data for use in listboxes, etc.
Use stored procedures with pass-through queries for reports. You can
base a report directly on a pass-through query that executes a stored
procedure.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Tue, 06 Jul 2004 06:25:39 -0700, Jeffery Hawkins
<jhawkins@.villagepress.com> wrote:
>First, if I'm posting this in the wrong newsgroup, please let me know
>and I'll re-post elsewhere.
>Trying to get SQL Server 2K data into an Access 2K front-end application
>(we're upsizing our back-end DB's to SQL Server, want to keep mdb
>front-ends). To limit the returned recordsets for both forms and
>reports, we want to pass parameters (like customerid or orderid) to SQL
>Server and return just the data needed.
>What is the "best practice" way to do this? A stored procedure (that
>accepts the parameter) linked to a view, or a user-defined function? Do
>both allow data updates? Or, is one better for a form (data updates) and
>the other for a report (view only)?
>Sorry for the basic question. Been developing in Access for a few
>years, but I'm REALLY new to SQL Server.
>Thanks,
>JLH
>
>*** Sent via Devdex http://www.devdex.com ***
>Don't just participate in USENET...get rewarded for it!|||Thanks, Mary. By linked FE's are you meaning that we should link the
SQL Server tables directly into the Access FE, and not pass parameters
back to SQL server SP's for data retrieval? (Great book, by the way --
as I've told you before!).
Thanks,
JLH
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||No, not at all -- absolutely you *should* use parameterized stored
procedures wherever possible. You can supply parameter values in code
to the .SQL property of a pass-through query (using DAO) so that it
would look something like:
myPSQ.SQL = "exec myproc " & varVal1 & ", '" & strVal & "'"
--Mary
On Thu, 08 Jul 2004 10:35:11 -0700, Jeffery Hawkins
<jhawkins@.villagepress.com> wrote:
>Thanks, Mary. By linked FE's are you meaning that we should link the
>SQL Server tables directly into the Access FE, and not pass parameters
>back to SQL server SP's for data retrieval? (Great book, by the way --
>as I've told you before!).
>Thanks,
>JLH
>
>*** Sent via Devdex http://www.devdex.com ***
>Don't just participate in USENET...get rewarded for it!
and I'll re-post elsewhere.
Trying to get SQL Server 2K data into an Access 2K front-end application
(we're upsizing our back-end DB's to SQL Server, want to keep mdb
front-ends). To limit the returned recordsets for both forms and
reports, we want to pass parameters (like customerid or orderid) to SQL
Server and return just the data needed.
What is the "best practice" way to do this? A stored procedure (that
accepts the parameter) linked to a view, or a user-defined function? Do
both allow data updates? Or, is one better for a form (data updates) and
the other for a report (view only)?
Sorry for the basic question. Been developing in Access for a few
years, but I'm REALLY new to SQL Server.
Thanks,
JLH
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Using linked mdb FE's can be very efficient if you code it right. You
need to create forms with restrictive WHERE clauses that load data for
editing, preferably one record at a time (tables are find as long as
you don't base forms on the entire table). You can also take advantage
of local storage for caching static data for use in listboxes, etc.
Use stored procedures with pass-through queries for reports. You can
base a report directly on a pass-through query that executes a stored
procedure.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Tue, 06 Jul 2004 06:25:39 -0700, Jeffery Hawkins
<jhawkins@.villagepress.com> wrote:
>First, if I'm posting this in the wrong newsgroup, please let me know
>and I'll re-post elsewhere.
>Trying to get SQL Server 2K data into an Access 2K front-end application
>(we're upsizing our back-end DB's to SQL Server, want to keep mdb
>front-ends). To limit the returned recordsets for both forms and
>reports, we want to pass parameters (like customerid or orderid) to SQL
>Server and return just the data needed.
>What is the "best practice" way to do this? A stored procedure (that
>accepts the parameter) linked to a view, or a user-defined function? Do
>both allow data updates? Or, is one better for a form (data updates) and
>the other for a report (view only)?
>Sorry for the basic question. Been developing in Access for a few
>years, but I'm REALLY new to SQL Server.
>Thanks,
>JLH
>
>*** Sent via Devdex http://www.devdex.com ***
>Don't just participate in USENET...get rewarded for it!|||Thanks, Mary. By linked FE's are you meaning that we should link the
SQL Server tables directly into the Access FE, and not pass parameters
back to SQL server SP's for data retrieval? (Great book, by the way --
as I've told you before!).
Thanks,
JLH
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||No, not at all -- absolutely you *should* use parameterized stored
procedures wherever possible. You can supply parameter values in code
to the .SQL property of a pass-through query (using DAO) so that it
would look something like:
myPSQ.SQL = "exec myproc " & varVal1 & ", '" & strVal & "'"
--Mary
On Thu, 08 Jul 2004 10:35:11 -0700, Jeffery Hawkins
<jhawkins@.villagepress.com> wrote:
>Thanks, Mary. By linked FE's are you meaning that we should link the
>SQL Server tables directly into the Access FE, and not pass parameters
>back to SQL server SP's for data retrieval? (Great book, by the way --
>as I've told you before!).
>Thanks,
>JLH
>
>*** Sent via Devdex http://www.devdex.com ***
>Don't just participate in USENET...get rewarded for it!
Sunday, March 11, 2012
acc97 switchboard error. 3146 Can someone look and tell a rookie what is wrong?
Onl;y one user has the problem.
I have given them a new copy of the switchboard but same startup error.
IOSDSQL 664-964 ENTER SQLSetStmtOption
HSTMT 0A0B1EC8
UWORD 0 <SQL_QUERY_TIMEOUT>
SQLPOINTER 0x0000003C
IOSDSQL 664-964 EXIT SQLSetStmtOption with return code 0
(SQL_SUCCESS)
HSTMT 0A0B1EC8
UWORD 0 <SQL_QUERY_TIMEOUT>
SQLPOINTER 0x0000003C (BADMEM)
IOSDSQL 664-964 ENTER SQLExecDirect
HSTMT 0A0B1EC8
UCHAR * 0x09FB18C0 [ -3] "SELECT
"dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
"dbo"."New_OnCall" ORDER BY "Lang" \ 0"
SDWORD -3
IOSDSQL 664-964 EXIT SQLExecDirect with return code -1 (SQL_ERROR)
HSTMT 0A0B1EC8
UCHAR * 0x09FB18C0 [ -3] "SELECT
"dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
"dbo"."New_OnCall" ORDER BY "Lang" \ 0"
SDWORD -3
DIAG [37000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect syntax near '.'. (170)You may have better success posting this in one of the numerous
microsoft.public.access newsgroups.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BrianMultilLanguage" <brian@.clilang.com> wrote in message
news:12k1s5d2cdmo10@.corp.supernews.com...
> Onl;y one user has the problem.
> I have given them a new copy of the switchboard but same startup error.
> IOSDSQL 664-964 ENTER SQLSetStmtOption
> HSTMT 0A0B1EC8
> UWORD 0 <SQL_QUERY_TIMEOUT>
> SQLPOINTER 0x0000003C
> IOSDSQL 664-964 EXIT SQLSetStmtOption with return code 0
> (SQL_SUCCESS)
> HSTMT 0A0B1EC8
> UWORD 0 <SQL_QUERY_TIMEOUT>
> SQLPOINTER 0x0000003C (BADMEM)
> IOSDSQL 664-964 ENTER SQLExecDirect
> HSTMT 0A0B1EC8
> UCHAR * 0x09FB18C0 [ -3] "SELECT
> "dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
> "dbo"."New_OnCall" ORDER BY "Lang" \ 0"
> SDWORD -3
> IOSDSQL 664-964 EXIT SQLExecDirect with return code -1
> (SQL_ERROR)
> HSTMT 0A0B1EC8
> UCHAR * 0x09FB18C0 [ -3] "SELECT
> "dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
> "dbo"."New_OnCall" ORDER BY "Lang" \ 0"
> SDWORD -3
> DIAG [37000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> Incorrect syntax near '.'. (170)
>
I have given them a new copy of the switchboard but same startup error.
IOSDSQL 664-964 ENTER SQLSetStmtOption
HSTMT 0A0B1EC8
UWORD 0 <SQL_QUERY_TIMEOUT>
SQLPOINTER 0x0000003C
IOSDSQL 664-964 EXIT SQLSetStmtOption with return code 0
(SQL_SUCCESS)
HSTMT 0A0B1EC8
UWORD 0 <SQL_QUERY_TIMEOUT>
SQLPOINTER 0x0000003C (BADMEM)
IOSDSQL 664-964 ENTER SQLExecDirect
HSTMT 0A0B1EC8
UCHAR * 0x09FB18C0 [ -3] "SELECT
"dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
"dbo"."New_OnCall" ORDER BY "Lang" \ 0"
SDWORD -3
IOSDSQL 664-964 EXIT SQLExecDirect with return code -1 (SQL_ERROR)
HSTMT 0A0B1EC8
UCHAR * 0x09FB18C0 [ -3] "SELECT
"dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
"dbo"."New_OnCall" ORDER BY "Lang" \ 0"
SDWORD -3
DIAG [37000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect syntax near '.'. (170)You may have better success posting this in one of the numerous
microsoft.public.access newsgroups.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BrianMultilLanguage" <brian@.clilang.com> wrote in message
news:12k1s5d2cdmo10@.corp.supernews.com...
> Onl;y one user has the problem.
> I have given them a new copy of the switchboard but same startup error.
> IOSDSQL 664-964 ENTER SQLSetStmtOption
> HSTMT 0A0B1EC8
> UWORD 0 <SQL_QUERY_TIMEOUT>
> SQLPOINTER 0x0000003C
> IOSDSQL 664-964 EXIT SQLSetStmtOption with return code 0
> (SQL_SUCCESS)
> HSTMT 0A0B1EC8
> UWORD 0 <SQL_QUERY_TIMEOUT>
> SQLPOINTER 0x0000003C (BADMEM)
> IOSDSQL 664-964 ENTER SQLExecDirect
> HSTMT 0A0B1EC8
> UCHAR * 0x09FB18C0 [ -3] "SELECT
> "dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
> "dbo"."New_OnCall" ORDER BY "Lang" \ 0"
> SDWORD -3
> IOSDSQL 664-964 EXIT SQLExecDirect with return code -1
> (SQL_ERROR)
> HSTMT 0A0B1EC8
> UCHAR * 0x09FB18C0 [ -3] "SELECT
> "dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
> "dbo"."New_OnCall" ORDER BY "Lang" \ 0"
> SDWORD -3
> DIAG [37000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> Incorrect syntax near '.'. (170)
>
acc97 switchboard error. 3146 Can someone look and tell a rookie what is wrong?
Onl;y one user has the problem.
I have given them a new copy of the switchboard but same startup error.
IOSDSQL 664-964 ENTER SQLSetStmtOption
HSTMT 0A0B1EC8
UWORD 0 <SQL_QUERY_TIMEOUT>
SQLPOINTER 0x0000003C
IOSDSQL 664-964 EXIT SQLSetStmtOption with return code 0
(SQL_SUCCESS)
HSTMT 0A0B1EC8
UWORD 0 <SQL_QUERY_TIMEOUT>
SQLPOINTER 0x0000003C (BADMEM)
IOSDSQL 664-964 ENTER SQLExecDirect
HSTMT 0A0B1EC8
UCHAR * 0x09FB18C0 [ -3] "SELECT
"dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
"dbo"."New_OnCall" ORDER BY "Lang" \ 0"
SDWORD -3
IOSDSQL 664-964 EXIT SQLExecDirect with return code -1 (SQL_ERROR)
HSTMT 0A0B1EC8
UCHAR * 0x09FB18C0 [ -3] "SELECT
"dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
"dbo"."New_OnCall" ORDER BY "Lang" \ 0"
SDWORD -3
DIAG [37000] [Microsoft][ODBC SQL Server Driver][SQL Server]
Line 1:
Incorrect syntax near '.'. (170)You may have better success posting this in one of the numerous
microsoft.public.access newsgroups.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BrianMultilLanguage" <brian@.clilang.com> wrote in message
news:12k1s5d2cdmo10@.corp.supernews.com...
> Onl;y one user has the problem.
> I have given them a new copy of the switchboard but same startup error.
> IOSDSQL 664-964 ENTER SQLSetStmtOption
> HSTMT 0A0B1EC8
> UWORD 0 <SQL_QUERY_TIMEOUT>
> SQLPOINTER 0x0000003C
> IOSDSQL 664-964 EXIT SQLSetStmtOption with return code 0
> (SQL_SUCCESS)
> HSTMT 0A0B1EC8
> UWORD 0 <SQL_QUERY_TIMEOUT>
> SQLPOINTER 0x0000003C (BADMEM)
> IOSDSQL 664-964 ENTER SQLExecDirect
> HSTMT 0A0B1EC8
> UCHAR * 0x09FB18C0 [ -3] "SELECT
> "dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
> "dbo"."New_OnCall" ORDER BY "Lang" \ 0"
> SDWORD -3
> IOSDSQL 664-964 EXIT SQLExecDirect with return code -1
> (SQL_ERROR)
> HSTMT 0A0B1EC8
> UCHAR * 0x09FB18C0 [ -3] "SELECT
> "dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
> "dbo"."New_OnCall" ORDER BY "Lang" \ 0"
> SDWORD -3
> DIAG [37000] [Microsoft][ODBC SQL Server Driver][SQL Serv
er]Line 1:
> Incorrect syntax near '.'. (170)
>
I have given them a new copy of the switchboard but same startup error.
IOSDSQL 664-964 ENTER SQLSetStmtOption
HSTMT 0A0B1EC8
UWORD 0 <SQL_QUERY_TIMEOUT>
SQLPOINTER 0x0000003C
IOSDSQL 664-964 EXIT SQLSetStmtOption with return code 0
(SQL_SUCCESS)
HSTMT 0A0B1EC8
UWORD 0 <SQL_QUERY_TIMEOUT>
SQLPOINTER 0x0000003C (BADMEM)
IOSDSQL 664-964 ENTER SQLExecDirect
HSTMT 0A0B1EC8
UCHAR * 0x09FB18C0 [ -3] "SELECT
"dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
"dbo"."New_OnCall" ORDER BY "Lang" \ 0"
SDWORD -3
IOSDSQL 664-964 EXIT SQLExecDirect with return code -1 (SQL_ERROR)
HSTMT 0A0B1EC8
UCHAR * 0x09FB18C0 [ -3] "SELECT
"dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
"dbo"."New_OnCall" ORDER BY "Lang" \ 0"
SDWORD -3
DIAG [37000] [Microsoft][ODBC SQL Server Driver][SQL Server]
Line 1:
Incorrect syntax near '.'. (170)You may have better success posting this in one of the numerous
microsoft.public.access newsgroups.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BrianMultilLanguage" <brian@.clilang.com> wrote in message
news:12k1s5d2cdmo10@.corp.supernews.com...
> Onl;y one user has the problem.
> I have given them a new copy of the switchboard but same startup error.
> IOSDSQL 664-964 ENTER SQLSetStmtOption
> HSTMT 0A0B1EC8
> UWORD 0 <SQL_QUERY_TIMEOUT>
> SQLPOINTER 0x0000003C
> IOSDSQL 664-964 EXIT SQLSetStmtOption with return code 0
> (SQL_SUCCESS)
> HSTMT 0A0B1EC8
> UWORD 0 <SQL_QUERY_TIMEOUT>
> SQLPOINTER 0x0000003C (BADMEM)
> IOSDSQL 664-964 ENTER SQLExecDirect
> HSTMT 0A0B1EC8
> UCHAR * 0x09FB18C0 [ -3] "SELECT
> "dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
> "dbo"."New_OnCall" ORDER BY "Lang" \ 0"
> SDWORD -3
> IOSDSQL 664-964 EXIT SQLExecDirect with return code -1
> (SQL_ERROR)
> HSTMT 0A0B1EC8
> UCHAR * 0x09FB18C0 [ -3] "SELECT
> "dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
> "dbo"."New_OnCall" ORDER BY "Lang" \ 0"
> SDWORD -3
> DIAG [37000] [Microsoft][ODBC SQL Server Driver][SQL Serv
er]Line 1:
> Incorrect syntax near '.'. (170)
>
Subscribe to:
Posts (Atom)